Search code examples
postgresqljsonb

In postgresql how can I select rows where a jsonb array contains objects?

My database table is something like this (data is a JSONB column):

 id |                 data                 
----+--------------------------------------
  1 | {"tags": [{"name": "tag1"}, {"name": "tag2"}]}
  2 | {"tags": [{"name": "tag2"}]}
  3 | {"tags": [{"name": "tag3"}]}
  4 | {"tags": [{"name": "tag4"}]}

I'd like to write a query that will return the rows where data contains tags tag2 or tag3. So rows 1, 2, and 3 should be returned. I've been looking at the postgresql JSONB documentation and it's not clear to me how to query a nested structure like this. How would I write the where clause?

Solution

  • Using where exists with a filter on the unnested json array will return the rows with id 1, 2 & 3

    SELECT * 
FROM mytable
WHERE EXISTS (
    SELECT TRUE 
    FROM jsonb_array_elements(data->'tags') x 
    WHERE x->>'name' IN ('tag2', 'tag3')
)