PostgreSQL中json_agg返回列顺序与临时表不一致如何解决

架构老林

2026-10-12

问题原因

PostgreSQL的json/jsonb类型默认不保证保留对象键的顺序，其中jsonb为了查询优化会对键做排序存储，顺序会和输入完全不一致。
直接将行记录传入json_agg做转换时，PostgreSQL不会严格按照表的列定义顺序生成json键，尤其是你通过ALTER TABLE动态追加列后，默认转换的顺序更容易出现错乱。
该问题和临时表本身无关，普通表执行相同的行转json逻辑也会出现同样的问题。

修复方案

最稳定的解决方案是手动按表列定义顺序拼接json对象，配合动态SQL适配你不定数量的动态追加列，具体实现逻辑如下：

步骤1：查询临时表的列顺序

PostgreSQL的系统表pg_attribute中存储了表的列定义信息，其中attnum字段的升序就是表的列创建顺序，我们可以先拿到按顺序排列的列参数，用于构造json_build_object：

-- 该语句会生成类似 'id',id,'batch_id',batch_id,'class_id',class_id,... 格式的字符串
SELECT string_agg(quote_literal(attname) || ', ' || quote_ident(attname), ', ' ORDER BY attnum)
INTO l_json_col_params
FROM pg_attribute
WHERE attrelid = 'tbl_temp_class'::regclass -- 你的临时表名
  AND attnum > 0 -- 排除系统内置列
  AND NOT attisdropped; -- 排除已删除的列

步骤2：动态构造按指定顺序生成json的查询语句

用上面拿到的列参数，调用json_build_object手动构造顺序正确的json对象，再用json_agg聚合成数组：

l_final_sql := format(
  'SELECT json_agg(json_build_object(%s)) FROM (SELECT * FROM tbl_temp_class ORDER BY id) t',
  l_json_col_params
);
-- 执行查询拿到结果
EXECUTE l_final_sql INTO l_final_json_result;