PostgreSQL中JSON多字段动态多条件通配符匹配（逻辑与）查询的实现方案咨询

哥们，你这个需求我之前做项目的时候刚好碰到过，动态多字段的AND型通配符匹配在PostgreSQL处理JSON数据时确实需要点技巧，下面给你几个实用的解决方案：

方案一：利用jsonb_each_text+聚合函数实现动态匹配 #

这个方案最灵活，完全适配你“运行时才知道搜索参数”的场景，核心思路是把原JSON字段和搜索条件都拆成键值对，逐个验证匹配，最后确保所有条件都命中。

假设你的表叫test_table，存储JSON的字段是data（强烈建议用jsonb类型，比json性能高很多），搜索条件是一个jsonb格式的参数（比如'{"field1":"abc","field3":"mno"}'::jsonb），可以这么写：

-- 直接写死搜索条件的示例
SELECT t.*
FROM test_table t
JOIN LATERAL (
    -- 统计匹配成功的条件数量
    SELECT COUNT(*) AS match_count
    FROM jsonb_each_text(t.data) d(key, value)
    JOIN jsonb_each_text('{"field1":"abc","field3":"mno"}'::jsonb) s(key, value)
      ON d.key = s.key  -- 匹配对应字段
     AND d.value LIKE '%' || s.value || '%'  -- 通配符匹配（包含关系）
) matches 
-- 只有当匹配数量等于搜索条件的字段总数时，才说明所有条件都满足
ON matches.match_count = jsonb_object_length('{"field1":"abc","field3":"mno"}'::jsonb);

如果是从应用层传入动态参数（比如用占位符$1），代码更简洁：

SELECT t.*
FROM test_table t
JOIN LATERAL (
    SELECT COUNT(*) AS match_count
    FROM jsonb_each_text(t.data) d(key, value)
    JOIN jsonb_each_text($1) s(key, value)
      ON d.key = s.key
     AND d.value LIKE '%' || s.value || '%'
) matches 
ON matches.match_count = jsonb_object_length($1);

这个方案的优点是完全不需要提前知道字段名，不管搜索条件有多少个字段都能处理，逻辑清晰且性能也不错。

方案二：动态生成JSONPath表达式 #

如果你更倾向于用你一开始想到的@?操作符（JSONPath匹配），那可以通过动态拼接JSONPath表达式来实现多条件AND匹配。

首先创建一个辅助函数，用来把搜索条件转换成合法的JSONPath字符串：

CREATE OR REPLACE FUNCTION build_jsonpath_search(search_json jsonb)
RETURNS jsonpath AS $$
DECLARE
    jsonpath_str text := '';
    rec record;
BEGIN
    -- 遍历搜索条件的每个键值对
    FOR rec IN SELECT key, value FROM jsonb_each_text(search_json) LOOP
        -- 多个条件用&&（逻辑与）连接
        IF jsonpath_str <> '' THEN
            jsonpath_str := jsonpath_str || ' && ';
        END IF;
        -- 格式化JSONPath片段，同时转义特殊字符避免语法错误
        jsonpath_str := jsonpath_str || format(
            '$.%I ? (@ like_regex ".*%s.*")', 
            rec.key, 
            regexp_replace(rec.value, '["\\]', '\\\0', 'g')
        );
    END LOOP;
    RETURN jsonpath_str::jsonpath;
END;
$$ LANGUAGE plpgsql;

然后使用这个函数查询：

SELECT * FROM test_table
WHERE data @? build_jsonpath_search('{"field1":"abc","field3":"mno"}'::jsonb);

如果需要大小写不敏感的匹配，只需要给like_regex加上flag "i"，修改函数里的格式化语句即可：

jsonpath_str := jsonpath_str || format(
    '$.%I ? (@ like_regex ".*%s.*" flag "i")', 
    rec.key, 
    regexp_replace(rec.value, '["\\]', '\\\0', 'g')
);

额外注意事项 #

1. 优先用jsonb类型：jsonb比json支持更多操作，且查询性能更优，尤其是涉及到索引的时候。
2. 加索引优化性能：如果你的表数据量较大，建议给data字段创建GIN索引：

   CREATE INDEX idx_test_data_gin ON test_table USING GIN (data);
   

3. 通配符的选择：如果是前缀匹配（比如搜索"abc"匹配"abcdef"），用LIKE 'abc%'比LIKE '%abc%'性能更好，因为可以利用部分索引；如果是任意位置匹配，就只能用%abc%了。

备注：内容来源于stack exchange，提问作者SQL RV