Сопоставление с образцом по ключу / значению jsonb

Question

Я использую PostgreSQL 9.4. Мой стол имеет jsonb колонка:

CREATE TABLE "PreStage".transaction (
  transaction_id serial NOT NULL,
  transaction jsonb
  CONSTRAINT pk_transaction PRIMARY KEY (transaction_id)
);

CREATE INDEX idxgin ON "PreStage".transaction USING gin (transaction);

Я храню транзакции в терминах ключ / значение в столбце JSONB. Одним из требований является поиск имени клиента по значению ключа, поэтому я выполняю такой запрос:

SELECT transaction as data FROM "PreStage".transaction
WHERE  transaction->>('HCP_FST_NM') ilike ('%neer%');

То, что я делаю, кажется, что запросу не нравится индекс GIN. Как сделать так, чтобы запрос использовал индекс GIN с поиском по шаблону без учета регистра?

Я пытался изменить jsonb столбец к тексту, индексация его с помощью gin_trgm_ops затем поиск необходимого текста, а затем преобразование результата в json и затем поиск нужного ключа / значения. Этот подход, похоже, не работает.

Answer 1

Класс оператора индекса GIN по умолчанию jsonb_ops не допускает сопоставления полнотекстового шаблона со значением. Подробности:

• Каков правильный индекс для запроса структур в массивах в Postgres jsonb?

Лучшая стратегия индексации зависит от вашей полной ситуации. Есть много вариантов. Чтобы просто охватить один ключ, который вы представили, вы можете использовать функциональный индекс триграммы. Ты уже проверял gin_trgm_opsИтак, вы уже знакомы с дополнительным модулем pg_trgm. Для тех, кто не:

• Варианты производительности запросов PostgreSQL LIKE

После установки модуля:

CREATE INDEX idxgin ON "PreStage".transaction
USING gin ((transaction->>'HCP_FST_NM') gin_trgm_ops);

Тогда этот запрос поддерживается:

SELECT transaction AS data
FROM   "PreStage".transaction
WHERE  transaction->>'HCP_FST_NM' ILIKE '%neer%';

Я также удалил некоторые ненужные скобки.

В зависимости от неизвестных деталей, существуют различные варианты оптимизации охвата индекса.

Например, если во многих строках вообще отсутствует ключ 'HCP_FST_NM', сделайте это частичным индексом, чтобы исключить ненужные строки и сохранить индекс маленьким:

CREATE INDEX idxgin ON "PreStage".transaction
USING gin ((transaction->>'HCP_FST_NM') gin_trgm_ops)
WHERE transaction ? 'HCP_FST_NM';

? бытьjsonb оператор локализации.
И добавьте один и тот же предикат к каждому запросу, который должен использовать этот индекс:

SELECT transaction AS data
FROM   "PreStage".transaction
WHERE  transaction->>'HCP_FST_NM' ILIKE '%neer%'
AND    transaction ? 'HCP_FST_NM';  -- even if that seems redundant.