Rails: How to check if jsonb column is empty Postgres

Question

I am having trouble checking when the jsonb column of my table is empty.

My column directions when empty has value "{}"

Tried the following

Model.where("directions != '{}'") <- brings all
Model.where("directions <@ '{}'") <- brings all

is there any other way that i am not aware of? Using postgresql 9.6

Model.where("directions != '{}'") should work, but Model.where.not(directions: '{}') feels more railsy (but is equivalent). If these queries return all of your data, then are you sure you have any instances with actual directions data in them? — Frost
– Frost, Commented Nov 4, 2016 at 12:23
@Frost the only syntax that worked is Model.where.not(directions: '{}') . To answer your questions yes i had both-case records. — Petros Kyriakou
– Petros Kyriakou, Commented Nov 4, 2016 at 13:42

Fran Martinez · Accepted Answer · 2018-08-31 11:21:14Z

4

Model.where.not("directions::text = ?", "{}")

answered Aug 31, 2018 at 11:21

Fran Martinez

3,0822 gold badges29 silver badges38 bronze badges

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

Comments

Deepak Mahakale · Accepted Answer · 2016-11-04 12:20:42Z

3

Model.where.not(directions: '{}')

answered Nov 4, 2016 at 12:20

Deepak Mahakale

23.8k10 gold badges73 silver badges94 bronze badges

4 Comments

phil pirozhkov Over a year ago

ERROR: operator does not exist: json <> unknown

Deepak Mahakale Over a year ago

@philpirozhkov gotta check if something changed during these 4-5 years :)

BenjaminGolder Over a year ago

This didn't work for me unless I removed the quotes: where.not(directions: {}) (I'm using Rails 6.1.7 and Postgres 16)

Deepak Mahakale Over a year ago

Good to know. I will check if anything is changed in last 8 years :+1

yeasayer · Accepted Answer · 2016-11-04 12:18:34Z

-3

Try to negate query with .not:

Model.where.not("directions = '{}'")

answered Nov 4, 2016 at 12:18

yeasayer

8782 gold badges12 silver badges22 bronze badges

1 Comment

user478798 Over a year ago

I agree with Petros, this is invalid syntax as of 2017 and Rails 4.

Collectives™ on Stack Overflow

Rails: How to check if jsonb column is empty Postgres

3 Answers 3

Comments

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related