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:
- What's the proper index for querying structures in arrays in 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 query performance variations
安装模块后:
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屋!