PG12 如何将关联查询结果返回为嵌套层级结构的JSON数据？

DevOps 小满

2026-10-4

PostgreSQL 12 层级JSON返回调整方案

原来的函数直接关联两张表返回扁平化结果，没有做层级聚合，你可以通过PostgreSQL内置的JSON构造+聚合函数调整，以下是可直接使用的修改后函数：

方案1：子查询关联写法（逻辑更直观）

create or replace function jorder() returns json as $$
select json_agg(
    json_build_object(
        'orderid', od.orderid,
        'grandtotal', od.grandtotal,
        'details', (
            -- 聚合当前订单对应的所有明细为JSON数组
            select json_agg(
                json_build_object(
                    'detailid', ds.detailid,
                    'description', ds.description
                )
            ) from odetails ds where ds.orderid = od.orderid
        )
    )
) from orders od;
$$ language sql stable;

方案2：分组关联写法（大数据量下性能更优）

create or replace function jorder() returns json as $$
select json_agg(
    json_build_object(
        'orderid', od.orderid,
        'grandtotal', od.grandtotal,
        'details', ds.detail_list
    )
) from orders od
-- 先聚合所有明细按订单分组
join (
    select 
        orderid, 
        json_agg(json_build_object('detailid', detailid, 'description', description)) as detail_list
    from odetails
    group by orderid
) ds on od.orderid = ds.orderid;
$$ language sql stable;