PostgreSQL使用row_to_json实现三表join结果按原表嵌套分组的问题
问题根源
直接使用g.* as all_g的写法无法生成嵌套结构,是因为PostgreSQL的语法解析规则会把g.*展开为表的所有独立列,别名只会作为展开后的列名前缀,并不会把整行封装为独立的嵌套记录对象,最终序列化JSON时会得到所有列平铺的扁平结构,和预期的嵌套对象完全不符。
实现方法
PostgreSQL中可以直接使用不带.*的表别名代表整行的复合记录类型,只要在SELECT列表中把表别名指定为对应名称,就会自动将整张表的行封装为嵌套结构,不需要手动拼装每个字段。
修正后的核心SQL片段如下:
select g as all_g, -- 直接用表别名代表整行,不需要加.* o as all_o, d as all_d from schema.table_1 g join schema.table_2 o on g.eid = o.ev_id join schema.table_3 d on g.eid = d.id where g.eid = any(%1$L)
这种写法下,后续执行row_to_json(s)序列化时,会自动生成你期望的JSON结构:每个外层对象的all_g/all_o/all_d键对应的是完整的子对象,包含对应表的所有字段。
完整修正后的函数逻辑
select format($q$ select results from ( select json_agg(row_to_json(s)) as results from ( -- 替换原来的展开列写法为整行取别名的写法 select g as all_g, o as all_o, d as all_d from schema.table_1 g join schema.table_2 o on g.eid = o.ev_id join schema.table_3 d on g.eid = d.id where g.eid = any(%1$L) ) s ) m; $q$, evs ) into q; return query execute q;
扩展说明
- 不需要取表的全部字段时,可以用
row()函数手动指定需要的字段集,比如row(g.eid, g.name) as all_g,同样会生成合规的嵌套结构 - 需要完全手动控制嵌套对象的字段时,可以用
json_build_object手动组装,比如json_build_object('eid', g.eid, 'name', g.name) as all_g,生成的JSON结构和预期完全一致 - 禁止使用
g.* as all_g这类写法,PostgreSQL会将表列直接平铺展开,无法生成嵌套对象结构
内容的提问来源于stack exchange,提问作者nimgwfc
相关产品推荐
相关产品推荐

