如何将PostgreSQL的JSONB对象列数据包装为数组形式？

• 验证转换效果：先通过查询确认转换后的格式符合预期，假设你的表是your_table，JSONB列是target_column，执行以下语句查看前几条数据的转换结果：

  SELECT jsonb_build_array(target_column) FROM your_table LIMIT 5;
  

  这条语句会把每行的JSONB对象直接包装成单元素数组，和你想要的[{ key1: {...}, key2: {...}}]格式完全一致。

• 批量更新数据：确认效果无误后，执行UPDATE语句完成全表数据迁移：

  UPDATE your_table SET target_column = jsonb_build_array(target_column);
  

  如果表数据量极大，一次性更新可能导致锁表时间过长，建议分批执行，比如按主键或其他字段分段：

  -- 分批更新示例，每次处理1000条
  UPDATE your_table SET target_column = jsonb_build_array(target_column) WHERE id BETWEEN 1 AND 1000;
  UPDATE your_table SET target_column = jsonb_build_array(target_column) WHERE id BETWEEN 1001 AND 2000;
  -- 重复执行直到覆盖全表
  

• 关键注意事项：

  • 操作前务必备份表数据，比如用pg_dump导出表，避免操作失误导致数据丢失。
  • 建议在事务中执行更新，若出现异常可以直接回滚：

    BEGIN;
    UPDATE your_table SET target_column = jsonb_build_array(target_column);
    -- 检查数据无误后提交
    COMMIT;
    -- 出错则回滚
    -- ROLLBACK;
    

  • 如果该JSONB列有索引，更新过程会自动维护索引，但大数据量场景下可能影响性能，可考虑先删除索引，更新完成后重新创建索引。

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