Массив запросов Postgresql объектов в поле JSONB

У меня есть таблица в базе данных postgresql 9.4 с полем jsonb, называемым приемниками. Некоторые строки примеров:

[{"id": "145119603", "name": "145119603", "type": 2}]
[{"id": "1884595530", "name": "1884595530", "type": 1}]
[{"id": "363058213", "name": "363058213", "type": 1}]
[{"id": "1427965764", "name": "1427965764", "type": 1}]
[{"id": "193623800", "name": "193623800", "type": 0}, {"id": "419955814", "name": "419955814", "type": 0}]
[{"id": "624635532", "name": "624635532", "type": 0}, {"id": "1884595530", "name": "1884595530", "type": 1}]
[{"id": "791712670", "name": "791712670", "type": 0}]
[{"id": "895207852", "name": "895207852", "type": 0}]
[{"id": "144695994", "name": "144695994", "type": 0}, {"id": "384217055", "name": "384217055", "type": 0}]
[{"id": "1079725696", "name": "1079725696", "type": 0}]

У меня есть список значений для id и вы хотите выбрать любую строку, содержащую объект с любым из значений из этого списка, в массиве в поле jsonb.

Это возможно? Есть ли индекс GIN, который я могу сделать, это ускорит это?

Ответ 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, но на самом деле это быстрее, чем это. Если производительность действительно важна для вас, вы должны проверить оба.

Ответ 2

Я нашел обходное решение:
where data::text similar to '%("id": "145119603"|"id": "1884595530")%'