Postgresql query array of objects in JSONB field

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   jsonbtestwhere  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  immutableas $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_jsonbfrom   jsonbtestwhere  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  immutableas $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_arrayfrom   jsonbtestwhere  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.

I find workaround:
where data::text similar to '%("id": "145119603"|"id": "1884595530")%'