PostgreSQL中如何将jsonb整数数组转换为带id键的对象数组？

方案1：兼容所有支持jsonb的PostgreSQL版本（通用方案） #

步骤1：新增目标字段 #

ALTER TABLE 你的表名 ADD COLUMN list_ids jsonb;

步骤2：批量更新存量数据 #

执行以下语句完成全量转换，兼容原字段为空、空数组的场景：

UPDATE 你的表名
SET list_ids = COALESCE(
    (
        SELECT jsonb_agg(jsonb_build_object('id', elem))
        FROM jsonb_array_elements(list_integers) AS elem
    ),
    '[]'::jsonb
);

如果你的表数据量超过10万条，建议按主键范围分批执行更新，避免长时间锁表影响业务。

方案2：PostgreSQL 12及以上版本最优方案（自动维护，无需手动更新） #

使用存储生成列实现字段自动同步，后续原字段list_integers发生新增、修改操作时，list_ids会自动同步更新，无需额外业务代码处理：

ALTER TABLE 你的表名
ADD COLUMN list_ids jsonb
GENERATED ALWAYS AS (
    COALESCE(
        (
            SELECT jsonb_agg(jsonb_build_object('id', elem))
            FROM jsonb_array_elements(list_integers) AS elem
        ),
        '[]'::jsonb
    )
) STORED;

验证转换结果 #

执行以下查询确认转换效果符合预期：

SELECT list_integers, list_ids FROM 你的表名 LIMIT 10;

注意事项 #

• 执行转换前建议先抽样验证转换逻辑，确认原字段存储的内容均为整数数组，避免异常值导致转换报错
• 全量更新大表前建议在业务低峰期操作，避免锁表影响线上业务

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