如何更新Postgres JSON字段对象数组中特定值的所有匹配项

解决方案 #

你原来的写法失效的原因是当同一个ID对应多条更新路径时，PostgreSQL的UPDATE语句仅会执行一次匹配更新，所以只有第一个匹配的用户名会被修改。推荐使用重构整个user数组的方案实现全量匹配更新，兼容PostgreSQL 10及以上所有版本：

UPDATE user_table
SET json_field = jsonb_set(
    json_field,
    '{user}'::text[],
    (
        SELECT jsonb_agg(
            CASE 
                WHEN elem->>'user_name' = 'Devang' THEN elem || '{"user_name": "Dev"}'::jsonb
                ELSE elem
            END
        )
        FROM jsonb_array_elements(json_field->'user') AS elem
    ),
    false
)
-- 可选过滤条件，仅更新存在目标用户名的行，提升更新性能
WHERE json_field @> '{"user": [{"user_name": "Devang"}]}'::jsonb;

逻辑说明

1. 内层查询通过jsonb_array_elements将user数组拆分为单个元素行
2. 遍历每个元素判断用户名，匹配到Devang则用jsonb合并操作符覆盖user_name字段值，未匹配则保留原元素
3. 用jsonb_agg将处理完的元素重新聚合为新的user数组
4. 外层jsonb_set将原JSON中的user数组替换为处理后的新数组
5. WHERE条件用@>包含操作符过滤，避免无差别扫描全表

如果你使用的是PostgreSQL 12及以上版本，可以用JSONPath语法更简洁地实现批量更新：

UPDATE user_table
SET json_field = jsonb_set_path(
    json_field,
    '$.user[*] ? (@.user_name == "Devang").user_name'::jsonpath,
    '"Dev"'::jsonb,
    true
)
WHERE json_field @> '{"user": [{"user_name": "Devang"}]}'::jsonb;

内容的提问来源于stack exchange，提问作者l.b.vasoya