PostgreSQL14 PL/pgSQL下更新带嵌套数组的jsonb对象数组指定元素

针对内存中jsonb数组的指定元素更新需求，不需要使用复杂的路径查询或动态SQL，直接通过数组展开、条件修改、重新聚合的逻辑即可实现，同时可以规避原有动态拼接字符串导致的转义错误、注入风险问题。

完整实现代码 #

DO $$
DECLARE
    -- 你的原始jsonb变量
    res jsonb := '[ {"county": "x", "dpa": ["a", "b", "c"]}, {"county": "y", "dpa": ["d", "e", "f"]}, {"county": "z", "dpa": ["h", "i", "j"]}]'::jsonb;
    -- 对应你循环中的变量
    cty record;
    r1 record;
BEGIN
    -- 此处替换为你原有的循环查询逻辑
    FOR cty IN SELECT 'y' AS cty_name LOOP
        FOR r1 IN SELECT 'm' AS name LOOP
            -- 核心更新逻辑：替代你原有步骤1-3的所有代码
            SELECT jsonb_agg(
                CASE
                    -- 匹配county值的元素做dpa字段更新
                    WHEN elem->>'county' = cty.cty_name
                    THEN elem || jsonb_build_object('dpa', (elem->'dpa') || jsonb_build_array(r1.name))
                    -- 不匹配的元素原样保留
                    ELSE elem
                END
            ) INTO res
            FROM jsonb_array_elements(res) AS elem;
        END LOOP;
    END LOOP;

    -- 步骤4：新建表存储最终jsonb
    CREATE TABLE IF NOT EXISTS county_dpa_result (
        result_data jsonb NOT NULL
    );
    INSERT INTO county_dpa_result VALUES (res);
END $$;

说明 #

• 该方案对数百个元素的jsonb数组处理性能足够，不需要依赖任何表存储的jsonb数据
• 完全规避了原有动态SQL拼接jsonb字符串可能产生的单引号转义错误、SQL注入风险
• 执行后你可以直接查询county_dpa_result表验证更新后的结果

内容的提问来源于stack exchange，提问作者Morik-san