PostgreSQL中在jsonb值中搜索字符串的问题

问题背景 #

现有PostgreSQL表的单条数据结构如下：

{
  "key": "z06khw1bwi886r18k1m7d66bi67yqlns",
  "reference_keys": {
    "KEY": "1x6t4y",
    "CODE": "IT137-521e9204-ABC-TESTE",
    "NAME": "A"
  }
}

需求是在reference_keys（一维JSONB类型字段）的任意键对应值中搜索指定字符串（比如'521e9204'），无需关注具体键名。

之前尝试用JOIN展开JSONB键值对的方法会导致重复返回行，而指定具体键的方案又不够灵活。

解决方案 #

方法1：使用EXISTS子查询（推荐） #

通过EXISTS判断是否存在匹配的键值对，既避免重复行，又无需指定具体键：

SELECT * 
FROM your_table
WHERE EXISTS (
    SELECT 1 
    FROM jsonb_each_text(reference_keys) AS j(k, value)
    WHERE j.value LIKE '%521e9204%'
);

原理：EXISTS仅检查子查询是否有结果返回，不会展开JOIN后的所有键值对，因此原表中符合条件的行只会返回一次，效率也较高——只要找到一个匹配值就会停止该行的检查。

方法2：拼接所有JSON值后匹配 #

将JSONB字段的所有值拼接成字符串，再进行模糊匹配：

SELECT * 
FROM your_table
WHERE string_agg(jsonb_object_values(reference_keys)::text, ',') LIKE '%521e9204%';

这种方法逻辑直观，但需要处理所有键值对后再匹配，在数据量大时效率不如EXISTS方案。

优化查询性能（可选） #

如果频繁需要执行这类模糊搜索，可以借助pg_trgm扩展创建 trigram 索引提升速度：

1. 先启用扩展：

CREATE EXTENSION IF NOT EXISTS pg_trgm;

2. 创建基于拼接值的索引：

CREATE INDEX idx_reference_keys_trgm ON your_table 
USING GIN (string_agg(jsonb_object_values(reference_keys)::text, ',') gin_trgm_ops);

内容的提问来源于stack exchange，提问作者Tomás Mendes