How do I query for empty jsonb arrays in Postgres, Activerecord, Rails 5

I have a Post model with the column defined by the following migration:

add_column :posts, :comments, :jsonb, default: []

add_index :posts, :comments, using: :gin

I would like to know the query to run so that I have a count of all the Posts which have the default, empty array as comments.

Upvotes: 5

Answers (3)

brcebn

Reputation: 1722

With Postgresql > 11, this is also a possible answer

Post.where('jsonb_array_length(comments) > 0')

Upvotes: 0

mcris_roberto

Reputation: 31

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 non empty comment

Post.where("length(comments::text) > 2").count

Upvotes: 1

wuliwong

Reputation: 4368

Post.where("comments = '[]'").count

Upvotes: 13

How do I query for empty jsonb arrays in Postgres, Activerecord, Rails 5

Answers (3)

Related Questions