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

Question

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')
)

What if you have a row with [{"value": "Sydney"}, {"value": "London"}, {"value": "Munich"}] should that also be returned? — user330315
– user330315, Commented Nov 28, 2022 at 8:04
Yes, that can also be returned! just edited the question to clarify. — Tamdim
– Tamdim, Commented Nov 28, 2022 at 8:07

user330315 · Accepted Answer · 2022-11-28 08:08:55Z

2

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']

answered Nov 28, 2022 at 8:08

user330315

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related