CODEWITHSUNDEEP
sqlpostgresql
Tamdim
Tamdim

Reputation: 1192

PostgresSQL: Select rows where a JSONB field contains all items in an array

Given the database table below where tags are arrays stored as JSONB, how can I write an SQL query to only select ids that contain all tags value's in an array?

e.g only records 1 and 2 would be returned for the array {"London", "Sydney"}


 id |             tags                 
----+---------------------------------------------------
  1 | [{"value": "Sydney"..}, {"value": "London"..}, {"value": "Munich"..}]
  2 | [{"value": "Sydney"..}, {"value": "London"..}]
  3 | [{"value": "London"..}]
  4 | [{"value": "Sydney"..}]

I managed to construct this query however it does not return an absolute match for ALL the items in the array which is what I'm after..

SELECT * 
FROM mytable
WHERE EXISTS (
    SELECT TRUE 
    FROM jsonb_array_elements(tags) tag 
    WHERE tag->>'value' IN ('London', 'Sydney')
)

Upvotes: 0

Views: 677

Answers (1)

user330315
user330315

Reputation:

You can use the contains operator @> with a JSON array:

select * 
from the_table
where tags @> '[{"value": "London"}, {"value": "Sydney"}]'

Or you can use a JSON path expression

select * 
from the_table
where jsonb_path_query_array(tags, '$[*].value') ?& array['London', 'Sydney']

Upvotes: 2

Related Questions