get array matches with json

Question

I have field in database with type json:

[{"id": "1"}, {"id": "2"}, {"id": "3"}] 

and I need get matches in json with array ["1", "2"] for at least one element.

Answer 1

Something like this:

select t.*
from the_table t
where exists (select *
              from jsonb_array_elements(t.the_column) as x(item)
                join jsonb_array_elements_text('["1", "2"]') as e(id)
                  on x.item ->> 'id' = e.id);

Online example