Using PostgreSQL, how do I find all records that have the jsonb column filled out?

Question

Let's say I have a jsonb column type called "data" for the table "recipes". I'm trying to find all records that have "data" filled out (i.e. "data" is not null or an empty bracket {}).

I know for hstore columns, using ruby, you can use a query like this to do so:

Recipe.where("data <> ''")

Is there an equivalent query for jsonb?

Answer 1

Updated after reading the question properly:

You can check for columns with NO data like this

SELECT * FROM table WHERE json_column IS NOT NULL

So this would then be

Recipe.where.not('data IS NULL')

And for columns with non-empty hashes like this:

SELECT * FROM table WHERE json_column <> '{}'

This translates to:

Recipe.where('data <> ?', '{}')
# or
Recipe.where("data <> '{}'")

A good way to verify your query is to run them in SQL first (so you avoid AR and its translation of Ruby to SQL). Then you can try and build the query in AR and use to_sql to see what AR makes of your queries.

Recipe.where('data <> ?', '{}').to_sql
#=> "SELECT \"recipies\".* FROM \"recipies\" WHERE (data <> '{}')"

vs.

Recipe.where('data <> ?', {}).to_sql
#=> "SELECT \"recipies\".* FROM \"recipies\" WHERE (data <> NULL)"