Query jsonb column in Postgres against multiple values

Question

I have a column 'carInfo' of type jsonbin my PostgreSQL database and I want to be able to query against multiple values. I want the query to return only those rows that match all criteria. For example, if I had multiple columns, I would write the query like this:

select * 
from car 
where name = 'BMW' 
AND 'year' = 2020

Since my column is of type jsonb I can make use of the containment operator (@>) like this:

select * 
from car 
where carInfo @> cast('{"name":"BMW"}') as jsonb 
  AND carInfo @> cast('{"year":"2020"}')

but I want the query to be dynamic, so that the user can query by any attributes they want.

So, they will send a list of search terms (in this case, the elements of that list would be {"name":"BMW"} and {"year":"2020"}.

Assuming, that I have the list as above, how would the query look like if I wanted to achieve the same result as when using the AND operator?

I tried it like this:

select * 
from car 
where carInfo @> any(array['{"name":"BMW"}', '{"year":"2020"}']::jsonb[])

but it acts the same way as when using the OR operator. I need to find those rows that contain BOTH the search terms

You could merge all those key/values into a single jsonb value and see if carInfo contains that, eg SELECT * FROM car WHERE carInfo @> '{"name": "BMW", "year": 2020}'. Your @> any(...) solution could be changed to @> all(...) to make it an AND, but I would highly recommend doing the single @> operation instead if you can. — Marth
– Marth, Commented Jul 17, 2020 at 15:45

Laurenz Albe · Accepted Answer · 2021-05-25 05:54:51Z

4

You could use @> ALL, but this should be better:

WHERE carInfo @> cast('{"name":"BMW", "year": "2020"}' as jsonb)

edited May 25, 2021 at 5:54

answered Jul 17, 2020 at 15:48

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

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

2 Comments

KiraAG Over a year ago

Thanks. For me this worked only when as jsonb is inside cast function

Laurenz Albe Over a year ago

@KiraAG Unsurprising, since my answer contains a typo. I have fixed it, thanks for the hint.

Collectives™ on Stack Overflow

Query jsonb column in Postgres against multiple values

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related