PostgreSQL jsonb层级调整：将validate节点上移并移除原节点

你的问题出在：当UPDATE语句的FROM子句返回多个对应同一id的行时，PostgreSQL只会选取其中一行执行更新（通常是第一行），导致只有索引0的元素被修改。正确的做法是先为每个id重新构建完整的properties数组，再一次性替换原字段，而不是逐个元素调用jsonb_set。

正确的SQL语句 #

UPDATE test_table e
SET data = jsonb_set(e.data, '{properties}', sub.new_properties)
FROM (
    SELECT
        id,
        jsonb_agg(
            -- 处理单个properties元素：提取validate到同级，移除opts内的validate
            CASE
                WHEN item -> 'opts' -> 'validate' IS NOT NULL THEN
                    (item - 'opts') || jsonb_build_object(
                        'opts', item -> 'opts' - 'validate',
                        'validate', item -> 'opts' -> 'validate'
                    )
                ELSE item  -- 保留没有validate的元素原样
            END ORDER BY index  -- 保持原数组顺序
        ) AS new_properties
    FROM test_table,
         jsonb_array_elements(data -> 'properties') WITH ORDINALITY arr(item, index)
    GROUP BY id
) sub
WHERE e.id = sub.id;

语句说明 #

1. 拆分与处理元素：

   • 使用jsonb_array_elements拆分properties数组，WITH ORDINALITY保留元素原索引以维持顺序。
   • 对每个元素，通过jsonb_build_object和运算符-（删除键）完成结构调整：
     • item - 'opts'：先移除原元素中的opts字段
     • 重新构建opts（删除内部的validate键）和新增同级的validate字段
     • 用||合并所有字段，保留原元素的其他属性（如name）

2. 聚合与替换：

   • 用jsonb_agg按原索引顺序聚合处理后的元素，生成完整的新properties数组。
   • 最后用jsonb_set一次性替换原data字段中的properties路径，确保所有元素都被更新。

针对你原语句的问题分析 #

你原语句中，CTE返回每个id对应的多个行（每个properties元素一行），但PostgreSQL的UPDATE在匹配多个源行时，只会选择其中一行应用更新，因此只有第一个元素（索引0）被修改。通过先聚合生成完整数组再替换的方式，能避免这个问题。

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