Postgres jsonb_agg函数输出被额外JSON字段包裹的原因及解决方法

原因分析 #

你遇到的问题是因为子查询中的jsonb_set结果没有指定列别名，Postgres会默认将这个列命名为jsonb_set。当你使用jsonb_agg(t)时，是把整个行（包含默认列名的键值对）作为JSON对象聚合，所以最终结果里会多出一层{"jsonb_set": ...}的结构。

解决方法 #

有两种简单的方式可以得到预期结果：

方法一：给子查询的结果指定别名，聚合该别名列 #

修改SQL语句，给jsonb_set的结果起一个别名（比如val），然后聚合这个具体的列，而不是整个行：

SELECT jsonb_agg(t.val) AS json_array
FROM (SELECT jsonb_set('[{"f1": "1","f2": null},  2,  null,  3]', '{0,f1}', '"DELETEME"',true) AS val) t

方法二：直接聚合子查询中的匿名列 #

如果不想额外加别名，也可以直接引用子查询中的默认列名，或者去掉子查询直接聚合函数结果：

-- 引用默认列名
SELECT jsonb_agg(t.jsonb_set) AS json_array
FROM (SELECT jsonb_set('[{"f1": "1","f2": null},  2,  null,  3]', '{0,f1}', '"DELETEME"',true)) t

-- 去掉子查询，直接聚合函数结果
SELECT jsonb_agg(jsonb_set('[{"f1": "1","f2": null},  2,  null,  3]', '{0,f1}', '"DELETEME"',true)) AS json_array

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