CODEWITHSUNDEEP
sqljsonpostgresqljsonb
Chris W.
Chris W.

Reputation: 39179

How to write postgres select queries for arrays in JSONB columns?

I'm having some trouble wrapping my head around querying JSON arrays in postgres. For example:

Given a Postgres table foobar with the JSONB column data like...

db=# select * from foobar;
 id |             data              
----+--------------------------------------------
  1 | [[true, true], [true, false]] 
  2 | [[true, true], [true, true]]  
  3 | [[true, true], [true, true], [true, true]]  
(3 rows)

How would I write a select query that would select only the row which contains only true values in each array in data and whose array is of length 2. (i.e., row id = 2 in the example above)?

Upvotes: 1

Views: 37

Answers (1)

user330315
user330315

Reputation:

Just compare it with a reference:

select *
from foobar
where data = '[[true, true], [true, true]]'

Upvotes: 1

Related Questions