PostgreSQL跨表更新jsonb字段替换数组内属性值问题

涉及两张数据库表结构如下：

• orders表：包含jsonb类型的eventlog字段，字段存储内容为JSON数组，数组内的每个元素都携带userId属性
• ids_tmp表：包含old_id、new_id两个字段，存储新旧ID的映射关系
  需求为：将eventlog数组中所有userId值匹配old_id的内容，替换为对应的new_id。原有更新脚本无法将处理后的JSON结果正确匹配更新到orders表的对应行。

原脚本的子查询没有关联orders表的唯一主键，会将全表所有行的eventlog元素打散后统一聚合，最终全表更新为同一个聚合结果，无法实现逐行对应更新，同时重复子查询判断逻辑执行效率低，也没有保留原JSON数组的元素顺序。

UPDATE orders g
SET eventlog = s.updated_log
FROM (
    SELECT 
        o.order_id, -- 替换为orders表实际的主键字段名，常见为id/order_id
        jsonb_agg(
            CASE 
                WHEN h.new_id IS NOT NULL THEN jsonb_set(elems, '{userId}', to_jsonb(h.new_id))
                ELSE elems 
            END
            ORDER BY elems_idx
        ) AS updated_log
    FROM orders o
    CROSS JOIN jsonb_array_elements(o.eventlog) WITH ORDINALITY arr(elems, elems_idx)
    LEFT JOIN ids_tmp h 
        ON h.old_id::varchar = elems->>'userId'
    GROUP BY o.order_id
) s
WHERE g.order_id = s.order_id;

• 必须通过orders表的主键做子查询和更新目标表的关联，才能保证处理后的JSON数组更新到对应的原行
• 用LEFT JOIN替换原脚本中重复的子查询判断、取值逻辑，减少表扫描次数，执行效率更高
• 拆分JSON数组时加WITH ORDINALITY获取元素原始位置，聚合时按位置排序，可保证替换后数组元素顺序和原数据完全一致
• 用to_jsonb()做类型转换，可自动适配new_id的字段类型（数字、字符串均可正确转为对应jsonb值），避免多层类型转换的冗余写法

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