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

Question

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.

wuliwong · Accepted Answer · 2017-07-26 21:43:22Z

13

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

answered Jul 26, 2017 at 21:43

wuliwong

4,4009 gold badges46 silver badges72 bronze badges

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

Comments

mcris_roberto · Accepted Answer · 2023-04-04 23:34:11Z

1

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

edited Apr 4, 2023 at 23:34

answered Apr 4, 2023 at 23:33

mcris_roberto

413 bronze badges

Comments

brcebn · Accepted Answer · 2024-10-08 08:40:10Z

0

With Postgresql > 11, this is also a possible answer

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

answered Oct 8, 2024 at 8:40

brcebn

1,7721 gold badge27 silver badges51 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related