PostgreSQL遍历jsonb数组并修改数值的实现方案

要批量处理JSONB数组中的每个元素，完成数值转换和字段删除，可使用PostgreSQL的JSONB函数组合实现，以下是完整的更新语句：

UPDATE table_name
SET details = jsonb_set(
    details,
    '{ranges}',
    (
        SELECT jsonb_agg(
            CASE
                -- 保留areaRange为null的原元素
                WHEN elem -> 'areaRange' IS NULL THEN elem
                ELSE
                    jsonb_set(
                        -- 删除areaRange下的unit键
                        elem - '{areaRange,unit}',
                        '{areaRange,range}',
                        CASE
                            -- 保留range为null的原值
                            WHEN elem -> 'areaRange' -> 'range' IS NULL THEN elem -> 'areaRange' -> 'range'
                            ELSE
                                jsonb_build_object(
                                    'to', 
                                    CASE 
                                        WHEN elem -> 'areaRange' -> 'range' ->> 'to' IS NOT NULL 
                                        THEN (round((elem -> 'areaRange' -> 'range' ->> 'to')::numeric, 2) * 100)::int 
                                        ELSE NULL 
                                    END,
                                    'from', 
                                    CASE 
                                        WHEN elem -> 'areaRange' -> 'range' ->> 'from' IS NOT NULL 
                                        THEN (round((elem -> 'areaRange' -> 'range' ->> 'from')::numeric, 2) * 100)::int 
                                        ELSE NULL 
                                    END
                                )
                        END
                    )
            END
        )
        FROM jsonb_array_elements(details -> 'ranges') AS elem
    )
)
WHERE details -> 'ranges' IS NOT NULL; -- 仅处理包含ranges字段的行

关键逻辑说明 #

• 数组展开与聚合：
  用jsonb_array_elements将ranges数组拆分为单个元素逐个处理，再通过jsonb_agg将处理后的元素重新组合为数组，替换原字段。

• null值处理：
  针对areaRange为null、range为null、to/from为null的三种场景，均通过CASE分支保留原null值，避免转换报错。

• 字段删除：
  使用JSONB的-操作符elem - '{areaRange,unit}'直接删除areaRange下的unit键，语法简洁高效。

• 数值转换：

  1. 用->>获取to/from的文本值，避免JSONB数值类型直接转换的精度问题；
  2. 转换为numeric类型后执行round(...,2)保留两位小数，再乘以100转为整数；
  3. 用CASE判断非null值才执行转换，保证null值原样保留。

验证结果 #

执行更新后，可通过以下语句查看处理后的ranges数据：

SELECT details -> 'ranges'
FROM table_name
WHERE details -> 'ranges' IS NOT NULL;

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