There is no single operation, which can help you, but you have a few options:
1. If you have a small (and fixed) number of ids to query, you can use multiple containment operators @>
combined with or
; f.ex.:
where data @> '[{"id": "1884595530"}]' or data @> '[{"id": "791712670"}]'
A simple gin
index can help you on your data column here.
2. If you have variable number of ids (or you have a lot of them), you can use json[b]_array_elements()
to extract each element of the array, build up an id list and then query it with the any-containment operator ?|
:
select *
from jsonbtest
where to_json(array(select jsonb_array_elements(data) ->> 'id'))::jsonb ?|
array['1884595530', '791712670'];
Unfortunately, you cannot index an expression, which has a sub-query in it. If you want to index it, you need to create a function for it:
create function idlist_jsonb(jsonbtest)
returns jsonb
language sql
strict
immutable
as $func$
select to_json(array(select jsonb_array_elements($1.data) ->> 'id'))::jsonb
$func$;
create index on jsonbtest using gin (idlist_jsonb(jsonbtest));
After this, you can query ids like this:
select *, jsonbtest.idlist_jsonb
from jsonbtest
where jsonbtest.idlist_jsonb ?| array['193623800', '895207852'];
Note: I used dot notation / computed field here, but you don't have to.
3. But at this point, you don't have to stick with json[b]: you have a simple text array, which is supported by PostgreSQL too.
create function idlist_array(jsonbtest)
returns text[]
language sql
strict
immutable
as $func$
select array(select jsonb_array_elements($1.data) ->> 'id')
$func$;
create index on jsonbtest using gin (idlist_array(jsonbtest));
And query this computed field with the overlap array operator &&
:
select *, jsonbtest.idlist_array
from jsonbtest
where jsonbtest.idlist_array && array['193623800', '895207852'];
Note: From my internal testing, this latter solution is calculated with a higher cost than the jsonb variant, but in fact it is faster than that, a little. If performance really matters to you, you should test both.