jsonb 键/值上的模式匹配 [英] Pattern matching on jsonb key/value

问题描述

我使用的是 PostgreSQL 9.4.我的表有一个 jsonb 列:

I am using PostgreSQL 9.4. My table has a jsonb column:

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 列中的键/值存储交易.要求之一是从键值中搜索客户名称，因此我正在运行如下查询:

I store transactions in terms of key / value in the JSONB column. One of the requirements is to search customer name from the key value, hence I am running a query like:

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

我所做的似乎查询不喜欢 GIN 索引.如何让查询使用 GIN 索引和不区分大小写的模式搜索?

What ever I do seems the query doesn't like the GIN index. How can I make the query use a GIN index with case insensitive pattern search?

我尝试将 jsonb 列更改为文本，使用 gin_trgm_ops 对其进行索引，然后搜索所需的文本，然后将结果转换为 json，然后搜索所需的键/值.这种方法似乎行不通.

I tried changing jsonb column to text, indexing it using gin_trgm_ops then search for required text, then converting the result to json and then searching in the required key/value. This approach doesn't seem to work.

推荐答案

默认的 GIN 索引运算符类 jsonb_ops 不允许对值进行全文模式匹配.详情:

The default GIN index operator class jsonb_ops does not allow full-text pattern matching on a value. Details:

• 什么是在 Postgres jsonb 中查询数组结构的正确索引?

最佳索引策略取决于您的整体情况.有很多选择.为了仅涵盖您提供的一个键，您可以使用功能三元组索引.您已经测试了 gin_trgm_ops，因此您已经熟悉附加模块 pg_trgm.对于那些不是:

The best indexing strategy depends on your complete situation. There are many options. To just cover the one key you presented, you could use a functional trigram index. You already tested gin_trgm_ops, so you are already familiar with the additional module pg_trgm. For those who are not:

• 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%';

我还删除了一些不必要的括号.

I also removed some unnecessary parentheses.

根据未知的细节，有多种优化索引覆盖的选项.

Depending on unknown details, there are various options to optimize index coverage.

例如，如果许多行根本没有键'HCP_FST_NM'，则将其设为部分索引 排除不相关的行并保持索引较小:

For instance, if many rows don't have a key 'HCP_FST_NM' at all, make that a partial index to exclude irrelevant rows and keep the index small:

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

? 是 jsonb 包含操作符.
并将相同的谓词添加到应该使用此索引的每个查询中:

? being the jsonb containment operator.
And add the same predicate to every query that's supposed to use this index:

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

这篇关于jsonb 键/值上的模式匹配的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！