PostgreSQL中jsonb列对象数组内如何实现ILIKE模糊匹配查询

该需求完全可以实现，你可以通过展开jsonb数组元素后做模糊匹配来完成需求，两种常用方案如下：

方案一：直接查询（适合偶发查询场景） #

通过jsonb_array_elements函数将SubscriptionInfo.Value对应的jsonb数组展开为多行记录，再对目标字段做ILIKE模糊匹配，用EXISTS关键字避免返回重复行，示例语句如下：

SELECT * 
FROM your_table_name  -- 替换为你实际的表名
WHERE EXISTS (
    SELECT 1 
    FROM jsonb_array_elements(data -> 'SubscriptionInfo' -> 'Value') AS subs
    -- 匹配firstname字段包含pa（不区分大小写）
    WHERE subs ->> 'firstname' ILIKE '%pa%'
    -- 如果需要同时匹配lastname等其他字段，追加OR条件即可
    -- OR subs ->> 'lastname' ILIKE '%pa%'
);

说明：->>运算符用于将jsonb类型的值转换为普通文本字符串，ILIKE为PostgreSQL原生提供的不区分大小写的模糊匹配运算符，符合你的需求。

方案二：加索引优化查询性能（适合高频查询场景） #

如果这类模糊查询的频率很高，可以安装pg_trgm扩展创建三元组索引，大幅提升模糊匹配的性能：

1. 先安装pg_trgm扩展：

CREATE EXTENSION IF NOT EXISTS pg_trgm;

2. 创建函数索引：

CREATE INDEX idx_subs_fuzzy_search ON your_table_name
USING GIN (
    ARRAY(
        SELECT jsonb_array_elements_text(data -> 'SubscriptionInfo' -> 'Value' -> 'firstname')
    ) gin_trgm_ops
);

如果需要同时覆盖多个字段的模糊查询，可以把其他字段也加入到索引的数组中即可。

内容的提问来源于stack exchange，提问作者ciolo