You need to enable JavaScript to run this app. PostgreSQL使用row_to_json实现三表join结果按原表嵌套分组的问题
优惠活动
大模型
产品
解决方案
定价
更多

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

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.11.18 19:06:00