Эффективный поиск по всему вложенному JSONB 1 уровня в Postgres

Question

Допустим, нам нужно проверить, содержит ли столбец jsonb конкретное значение, совпадающее с подстрокой в ​​любом значении (не вложенное, только первый уровень).

Как эффективно оптимизировать запрос для поиска по всему JSONB столбец (это означает каждый ключ) для значения?

Есть ли хорошая альтернатива ILIKE %val% на jsonb тип данных приведен к тексту?

jsonb_each_text(jsonb_column) ILIKE '%val%'

В качестве примера рассмотрим эти данные:

SELECT 
  '{
   "col1": "somevalue", 
   "col2": 5.5, 
   "col3": 2016-01-01, 
   "col4": "othervalue", 
   "col5": "yet_another_value"
  }'::JSONB

Как бы вы пошли на оптимизацию запроса, когда нужно искать шаблон? %val% в записях, содержащих разные ключи конфигурации для разных строк в столбце jsonb?

Я знаю, что поиск с предшествующим и последующим % знак неэффективен, поэтому ищет лучший путь, но с трудом находит его. Кроме того, индексирование всех полей в столбце json явным образом не вариант, поскольку они различаются для каждого типа записи и создают огромный набор индексов (не каждая строка имеет одинаковый набор ключей).

Вопрос

Есть ли лучшая альтернатива извлечению каждой пары ключ-значение в текст и выполнению поиска ILIKE/POSIX?

Answer 1

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

Это слишком простой, но понятный пример:

create table foo as SELECT '{"col1": "somevalue", "col2": 5.5, "col3": "2016-01-01", "col4": "othervalue", "col5": "yet_another_value"}'::JSONB as bar;

create index pickfoo1 on foo ((bar #>> '{col1}'));
create index pickfoo2 on foo ((bar #>> '{col2}'));

Это основная идея, даже если она бесполезна для запросов, но вы можете делать больше вещей (в зависимости от ваших потребностей).

Например: если вам нужно только сопоставление без учета регистра, этого будет достаточно:

-- Create index over lowered value:
create index pickfoo1 on foo (lower(bar #>> '{col1}'));
create index pickfoo2 on foo (lower(bar #>> '{col2}'));

-- Check that it matches:
select * from foo where lower(bar #>> '{col1}') = lower('soMEvaLUe');

ПРИМЕЧАНИЕ. Это только пример: если вы выполните объяснение по сравнению с предыдущим выбором, вы увидите, что postgres фактически выполняет последовательное сканирование вместо использования индекса. Но это потому, что мы тестируем таблицу с одной строкой, что не является обычным. Но я уверен, что вы могли бы проверить это с большим столом;-)

Несмотря на огромные таблицы, даже подобные запросы должны выигрывать от индекса, если первая карта не появляется в начале строки (но это не вопрос jsonb, а вопрос самих индексов btree).

Если вам нужно оптимизировать запросы, такие как:

select * from foo where bar #>> '{col1}' ilike '%MEvaL%';

... тогда вам следует рассмотреть возможность использования индексов GIN или GIST.