更新PostgreSQL jsonb数组后出现重复值问题排查

错误原因 #

你的SQL出现大量重复元素的核心问题是：

• 子查询未限定仅处理目标行（id=1），而是遍历了表中所有行的seats元素；
• 子查询与主表产生笛卡尔积关联，导致所有行的seats元素被无差别聚合，最终重复填充到目标行中。

具体来说：表中共有2行数据，每行seats拆分为2个元素，子查询会把这4个元素全部聚合为一个数组，再和主表的id=1行做关联，最终因笛卡尔积逻辑导致数组被重复多次（出现44个元素是执行计划中重复计算的结果）。

另外还有一个细节错误：'"PICKED"'会让status的值变成带嵌套双引号的字符串（如"\"PICKED\""），不符合JSON格式规范。

修正后的SQL写法 #

写法一：限定子查询范围并分组聚合

UPDATE driver_orders
SET seats = s.new_seats
FROM (
    SELECT 
        jsonb_agg(
            jsonb_build_object(
                'id', elem -> 'id',
                'price', elem -> 'price',
                'user_id', elem -> 'user_id',
                'status', CASE 
                             WHEN elem ->> 'id' = '#2' THEN 
                                'PICKED'::jsonb  -- 直接转为JSONB类型，避免嵌套引号
                             ELSE 
                                elem -> 'status' END
            )
        ) as new_seats
    FROM driver_orders,
         jsonb_array_elements(seats) as elem
    WHERE driver_orders.id = 1  -- 仅处理目标行
    GROUP BY driver_orders.id   -- 按行分组，确保只聚合当前行的元素
) s
WHERE id = 1

写法二：用jsonb_set精准更新指定元素（更高效）

这种方式无需全量聚合数组，直接定位到目标元素修改：

UPDATE driver_orders
SET seats = jsonb_set(
    seats,
    array[(index - 1)::text],  -- JSONB数组索引从0开始，需转换为文本数组
    jsonb_set(elem, '{status}', '"PICKED"'::jsonb)
)
FROM (
    SELECT 
        id,
        elem,
        row_number() OVER (PARTITION BY id ORDER BY elem ->> 'id') as index
    FROM driver_orders,
         jsonb_array_elements(seats) as elem
    WHERE id = 1 AND elem ->> 'id' = '#2'
) sub
WHERE driver_orders.id = sub.id;

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