Rails Postgres how to query a jsonb column for empty objects?

Question

How do I get all the empty records for a jsonb column with Active Record ?

Are you looking for all rows where the jsonb column is null? Or, are you looking for all rows where the jsonb column is an empty object '{}'? Some example code/sql would help here. — MrWillihog
– MrWillihog, Commented Jun 8, 2016 at 14:24
I am looking for all the rows where the jsonb column is an empty object '{}' . I actually tried Active Record query interface Model.where.not(my_column: {}) — Shani
– Shani, Commented Jun 9, 2016 at 9:56

MrWillihog · Accepted Answer · 2016-06-09 11:04:23Z

18

You can query for empty objects in a JSONB column using the following syntax:

Model.where("column = '{}'")

answered Jun 9, 2016 at 11:04

MrWillihog

2,67620 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

mcris_roberto · Accepted Answer · 2023-04-05 01:16:26Z

0

You can do this by converting the json to string, and count the length. Empty json will return 2. This works for both {} and []. E.g. To return empty comment

Model.where("length(column::text) <= 2").count

answered Apr 5, 2023 at 1:16

mcris_roberto

413 bronze badges

Collectives™ on Stack Overflow

Rails Postgres how to query a jsonb column for empty objects?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related