首页 文章

jsonb键/值上的模式匹配

提问于
浏览 次
4

我正在使用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 ，然后搜索所需的键/值 . 这种方法似乎不起作用 .

json postgresql indexing gin jsonb

1 回答

  • 8

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

    最佳索引策略取决于您的完整情况 . 有很多选择 . 要覆盖您提供的一个键，您可以使用 functional trigram index . 您已经测试了 gin_trgm_ops ，因此您已经熟悉了附加模块pg_trgm . 对于那些不是：

    安装模块后：

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

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

    根据未知的详细信息，有多种选项可以优化索引覆盖率 .

    For instance ，如果很多行根本没有键'HCP_FST_NM'，那么使 partial index 排除不相关的行并保持索引较小：

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

    ? being the jsonb containment operator.
    并为每个应该使用此索引的查询添加相同的谓词：

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

相关问题