PostgreSQL如何在同一条UPDATE语句中更新jsonb字段的多个键？

你遇到的multiple assignments to same column "details"报错，是因为PostgreSQL的UPDATE语法不允许在SET子句中对同一列重复赋值，三次给details列赋值的写法不符合语法要求，同时你的WHERE条件里e.id = 12345后多写了一个冗余的AND，也需要修正。

方案1：嵌套调用jsonb_set #

jsonb_set的返回值是修改完成的jsonb对象，你可以把前一次修改的结果作为后一次jsonb_set的输入，单次完成赋值：

UPDATE events e
SET  
   details = jsonb_set(
               jsonb_set(
                 jsonb_set(
                   details, 
                   '{"data", "user_name"}', 
                   '"Resident"'
                 ),
                 '{"data", "is_by_support"}', 
                 '"false"'
               ),
               '{"data", "is_by_resident"}', 
               '"true"'
             ),
   updated_by = 1,
   updated_on = now()
FROM
    test_req tr
WHERE
    e.id = 12345
    AND e.data_reference_id = tr.id
    AND e.event_type_id = 4
    AND e.created_by = 2
    AND e.updated_by = 2
    AND e.details -> 'data' ->> 'user_name' = 'Test'
    AND e.details -> 'data' ->> 'is_by_support' = 'true'
    AND e.details -> 'data' ->> 'is_by_resident' = 'false';

方案2：使用jsonb合并操作符|| #

如果修改的键都集中在同一个子层级，可以直接构造需要替换的子json对象，通过||操作符和原有内容合并，写法更简洁易读：

UPDATE events e
SET  
   details = details || '{"data": {"user_name": "Resident", "is_by_support": "false", "is_by_resident": "true"}}'::jsonb,
   updated_by = 1,
   updated_on = now()
FROM
    test_req tr
WHERE
    e.id = 12345
    AND e.data_reference_id = tr.id
    AND e.event_type_id = 4
    AND e.created_by = 2
    AND e.updated_by = 2
    AND e.details -> 'data' ->> 'user_name' = 'Test'
    AND e.details -> 'data' ->> 'is_by_support' = 'true'
    AND e.details -> 'data' ->> 'is_by_resident' = 'false';

注意：如果details下的data节点还有其他键，||操作符只会覆盖你指定的三个键，不会丢失其他原有数据。

• 如果你需要修改的键分散在不同的json层级，选嵌套jsonb_set的方案更灵活
• 如果修改的键集中在同个层级，选||合并的方案代码可读性更高

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