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

Допустим, нам нужно проверить, содержит ли столбец 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?


postgresql lookup jsonb postgresql-9.5
person Kamil Gosciminski    schedule 27.04.2016    source источник
comment
Это может быть лучше для dba.stackexchange.com, я просто хотел получить широкую аудиторию по этому вопросу.   -  person Kamil Gosciminski    schedule 27.04.2016
comment
pg_trgm может быть лучшим вариантом (тип ilike / posix) для этого поскольку вы все еще используете тип критериев сопоставления с образцом в столбце jsonb   -  person Dmitry Savinkov    schedule 27.04.2016
comment
@DmitrySavinkov не могли бы вы уточнить? Я считаю, что мне все равно нужно будет распаковать данные json в отдельные строки.   -  person Kamil Gosciminski    schedule 27.04.2016
comment
да, вам нужно распаковать значение, чтобы можно было применить класс оператора gin_trgm_ops, вы также можете проверить этот ответ   -  person Dmitry Savinkov    schedule 27.04.2016
comment
Такие фильтры, как somethink LIKE '%<somevalue>%', по умолчанию неэффективны, поскольку всегда вызывают полное сканирование данных. Так что предложение @DmitrySavinkov - почти лучшее решение. ИМО, это должен быть ответ с кратким объяснением.   -  person Abelisto    schedule 27.04.2016
comment
Мой вопрос, кажется, не разъясняет, что я ищу. Мне известно о pg_trgm, неэффективном, как с предыдущим и последующим поиском % и т. Д. В основном я прошу способ эффективно искать каждый ключ jsonb по его значению (без его явной распаковки в текст)   -  person Kamil Gosciminski    schedule 28.04.2016

Ответы (1)


arrow_upward
1
arrow_downward

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

Это слишком простой, но самоочевидный пример:

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}'));

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

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

-- 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 фактически выполняет последовательное сканирование вместо использования индекса. Но это потому, что мы тестируем таблицу с одной строкой, что необычно. Но я уверен, что вы могли бы проверить это на более крупной таблице ;-)

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

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

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

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

person bitifet    schedule 26.08.2016
comment
Спасибо за Ваш ответ. К сожалению, я боюсь, что вы неправильно поняли суть этого вопроса. Я прошу более эффективный способ поиска по ВСЕМ jsonb на первом уровне, чем его распаковка. Под целым я подразумеваю каждый ключ. Это для поисковой системы. Я не могу сказать, какие ключи есть даже у конкретного столбца. - person Kamil Gosciminski; 26.08.2016
comment
Вот почему я начал говорить: «Если ты это знаешь…». Если вам нужно иметь возможность выполнять поиск по любому (не заранее заданному) ключу, оптимизировать будет намного сложнее. - person bitifet; 27.08.2016
comment
Кроме того, я осмелюсь сказать, что попытка более эффективного синтаксического анализа JSON не решит вашу проблему. Прежде всего потому, что на самом деле jsonb не хранится внутри как json ТОЧНО для более эффективного доступа (я не думаю, что вы могли бы достичь этого быстрее, чем собственные функции доступа). - person bitifet; 27.08.2016
comment
... но САМОЕ ВАЖНО, потому что пока вы что-либо ищете в базе данных, независимо от типа данных, которые вы ищете, и особенно когда объем данных становится огромным, смысл не в том, чтобы быстрее анализировать или сравнивать их, а в том, чтобы НЕ ДЕЛАТЬ ЭТО ВООБЩЕ . Это цель индексов. Оптимизация синтаксического анализа и / или сравнения в лучшем случае немного задержит реальную проблему (предположим, что ваша база данных растет со временем, как и большинство). - person bitifet; 27.08.2016
comment
… Так что я настаиваю. Без сомнения, даже мой простой пример вам не подходит: подходить нужно с помощью индексов. Конечно, это будет нетривиально. И я осмелюсь сказать, что это было бы невозможно с (по умолчанию) btree. Но я предлагаю вам изучить (на самом деле довольно глубоко) индексы GIN и GIST. Я не слишком опытен, чтобы легко написать возможный пример. Но я уверен, что вы могли бы реализовать один, чтобы, по крайней мере, отбрасывать (БЕЗ СКАНИРОВАНИЯ) строки, не запрошенные ключи (и, конечно, фильтровать те, у которых есть некоторые фрагменты (например, триграммы), что делает окончательное последовательное сканирование намного короче. - person bitifet; 27.08.2016
comment
ПРИМЕЧАНИЕ: Не как реальное решение, а как разумный патч тем временем: помните, что postgres может индексировать не только одно (или несколько) значений столбца, но и сложные выражения, ВКЛЮЧАЯ ВЫЗОВЫ ФУНКЦИЙ (если функция хотя бы стабильна!), Так что вы можете реализовать, например, функцию перенастройки отсортированного списка ключей json. Опять же, этого будет недостаточно с простым btree, но с tgrm (не говоря уже о реализации более специфичного gin / gist one) можно было бы фильтровать первые строки с ключами, которые мы хотим искать. - person bitifet; 27.08.2016