Ответ 1
Нет единственной операции, которая может вам помочь, но у вас есть несколько вариантов:
1. Если у вас есть небольшое (и фиксированное) количество идентификаторов для запроса, вы можете использовать несколько операторов сдерживания @> в сочетании с or; f.ex:.
where data @> '[{"id": "1884595530"}]' or data @> '[{"id": "791712670"}]'
Простой gin индекс может помочь вам в столбце данных здесь.
2. Если у вас есть переменное количество идентификаторов (или их много), вы можете использовать json[b]_array_elements() для извлечения каждого элемента массива, создания списка идентификаторов, а затем запросите его с помощью оператора с ограничениями ?|:
select *
from jsonbtest
where to_json(array(select jsonb_array_elements(data) ->> 'id'))::jsonb ?|
array['1884595530', '791712670'];
К сожалению, вы не можете индексировать выражение, в котором есть подзапрос. Если вы хотите проиндексировать его, вам нужно создать для него функцию:
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));
После этого вы можете запросить идентификаторы следующим образом:
select *, jsonbtest.idlist_jsonb
from jsonbtest
where jsonbtest.idlist_jsonb ?| array['193623800', '895207852'];
Примечание. Здесь я использовал точечное обозначение/вычисленное поле, но вам не нужно.
3. Но на данный момент вам не нужно придерживаться json [b]: у вас есть простой текстовый массив, который также поддерживается PostgreSQL.
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));
И запросите это вычисленное поле с помощью оператора массива перекрытий &&:
select *, jsonbtest.idlist_array
from jsonbtest
where jsonbtest.idlist_array && array['193623800', '895207852'];
Примечание. Из моего внутреннего тестирования это последнее решение вычисляется с более высокой стоимостью, чем вариант jsonb, но на самом деле это быстрее, чем это. Если производительность действительно важна для вас, вы должны проверить оба.