如何在PostgreSQL中按JSON数组条件更新jsonb字段内的嵌套对象

正确的PostgreSQL jsonb数组元素更新语句 #

你原来的写法存在3个核心问题：

• 直接将myjson赋值为activity数组，丢失了外层的id等其他根字段
• 没有把activity数组拆分为单个元素遍历匹配，直接对数组访问user字段逻辑无效
• 更新路径错误：你需要修改的是匹配元素下user对象的name属性，而非message属性

最终更新语句 #

UPDATE mytable
SET myjson = jsonb_set(
    myjson,
    '{activity}',
    (
        SELECT jsonb_agg(
            CASE 
                WHEN elem->'user'->>'uid' = '101' 
                THEN jsonb_set(elem, '{user,name}', '"xxx"')
                ELSE elem 
            END
        )
        FROM jsonb_array_elements(myjson->'activity') elem
    )
)
-- 可选过滤条件：仅更新存在对应uid的行，避免全表扫描提升性能
WHERE myjson @> '{"activity": [{"user": {"uid": "101"}}]}';

结果验证 #

执行更新后可通过以下语句查看格式化后的结果：

SELECT jsonb_pretty(myjson) FROM mytable;

输出和你期望的结果完全一致：

{
  "id": "1",
  "activity": [
    {
      "message": "This is test",
      "user": {
        "uid": "101",
        "name": "xxx"
      }
    },
    {
      "message": "this is sample",
      "user": {
        "uid": "102",
        "name": "abc"
      }
    }
  ]
}

如果你需要同时把user下的uid键重命名为id，只需修改CASE内的逻辑即可：

CASE 
  WHEN elem->'user'->>'uid' = '101' 
  THEN jsonb_set(
         elem,
         '{user}',
         (elem->'user' - 'uid') || jsonb_build_object('id', elem->'user'->'uid', 'name', 'xxx')
       )
  ELSE elem 
END

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