PostgreSQL更新jsonb字段时如何不覆盖已有键实现值合并

你可以通过自定义PL/pgSQL函数实现指定的合并规则，不需要提前知晓待更新的键名，函数会自动遍历新JSON对象的所有键值对做处理：

步骤1：创建自定义合并函数 #

CREATE OR REPLACE FUNCTION jsonb_merge_keep_duplicates(old_json jsonb, new_json jsonb)
RETURNS jsonb AS $$
DECLARE
    key text;
    new_val jsonb;
    old_val jsonb;
BEGIN
    -- 遍历新JSON对象的所有键值对
    FOR key, new_val IN SELECT * FROM jsonb_each(new_json) LOOP
        old_val := old_json -> key;
        -- 原JSON不存在对应键，直接插入
        IF old_val IS NULL THEN
            old_json := jsonb_set(old_json, ARRAY[key], new_val);
        ELSE
            -- 原键已存在，将旧值转为数组后追加新值
            IF jsonb_typeof(old_val) != 'array' THEN
                old_val := jsonb_build_array(old_val);
            END IF;
            old_json := jsonb_set(old_json, ARRAY[key], old_val || new_val);
        END IF;
    END LOOP;
    RETURN old_json;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

步骤2：使用函数执行更新 #

替换原来的||运算符为自定义函数即可：

UPDATE tmp_1 
   SET jb_value = jsonb_merge_keep_duplicates(jb_value, '{"foo_bar":4, "foo_foo_bar":4}'::jsonb)
 WHERE id = 1;

结果验证 #

执行查询确认结果符合预期：

SELECT jb_value FROM tmp_1 WHERE id = 1;
-- 输出：{"foo": 1, "bar": 2, "foo_bar": [3, 4], "foo_foo_bar": 4}

补充说明 #

• 上述函数仅合并顶层键，如果你需要递归处理嵌套JSON对象，可在判断旧值类型为object时递归调用该函数
• 函数标记为IMMUTABLE，输入固定时输出结果固定，支持PostgreSQL的查询优化，也可安全用于索引表达式

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