PostgreSQL中如何对jsonb数组列进行不区分大小写的模糊匹配

针对你用json_agg(column_3)::jsonb生成的JSONB字符串数组列item_ids，要实现类似ANY(item_ids) ILIKE '%item_1%'的模糊匹配逻辑，以下是几种可行的PostgreSQL实现方案：

方案1：通过展开数组元素匹配（通用兼容） #

利用jsonb_array_elements将JSONB数组拆分为单个元素，转换为文本后用ILIKE匹配，通过EXISTS判断是否存在符合条件的元素：

SELECT *
FROM your_table
WHERE EXISTS (
    SELECT 1
    FROM jsonb_array_elements(item_ids) AS elem
    WHERE elem::text ILIKE '%item_1%'
);

方案2：使用JSON路径查询（PostgreSQL 12+） #

PostgreSQL 12及以上版本支持JSON路径表达式，通过jsonb_path_query_exists结合正则匹配实现不区分大小写的模糊查询：

SELECT *
FROM your_table
WHERE jsonb_path_query_exists(
    item_ids,
    '$[*] ? (@ like_regex "item_1" flag "i")'
);

• $[*]：遍历数组中的所有元素
• @：指代当前遍历到的元素
• flag "i"：开启不区分大小写模式

方案3：直接提取文本数组（PostgreSQL 15+） #

PostgreSQL 15新增了更便捷的jsonb_array_elements_text函数，可直接将JSONB数组转为文本数组，结合ANY操作符简化查询：

SELECT *
FROM your_table
WHERE '%item_1%' ILIKE ANY (ARRAY(SELECT jsonb_array_elements_text(item_ids)));

或者用EXISTS写法更清晰：

SELECT *
FROM your_table
WHERE EXISTS (
    SELECT 1
    FROM jsonb_array_elements_text(item_ids) AS elem
    WHERE elem ILIKE '%item_1%'
);

性能优化建议 #

如果你的表数据量较大，频繁执行这类查询可能会有性能瓶颈，可以创建基于文本数组的GIN索引（需先安装pg_trgm扩展）：

-- 先安装pg_trgm扩展（如果未安装）
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- 创建表达式索引
CREATE INDEX idx_item_ids_trgm ON your_table USING GIN (
    (array(SELECT jsonb_array_elements_text(item_ids))) gin_trgm_ops
);

创建索引后，上述方案3的ANY查询可以利用索引加速模糊匹配。

内容的提问来源于stack exchange，提问作者Wai Yan Hein