postgresql - 如何以 JSON 层次结构返回数据？

Question

对于pg12中的以下数据表和函数

create table orders
(
  orderid integer, grandtotal numeric(10, 2)
)

create table odetails
(
  orderid integer, detailid integer, description text
)

create function jorder() returns json as
begin
 return query select od.orderid, od.grandtotal, ds.detailid, ds.description
 from orders od
 join odetails ds on od.orderid = ds.orderid;
end;

如何在如下 JSON 层次结构中获取返回数据？

[{
    "orderid": 1,
    "grandtotal": 100.00,
    "details": [{
            "detailid": 11,
            "description": "pen"
        },
        {
            "detailid": 12,
            "description": "orange"
        }
    ]
}, {
    "orderid": 2,
    "grandtotal": 200.00,
    "details": [{
            "detailid": 21,
            "description": "book"
        },
        {
            "detailid": 22,
            "description": "coffee"
        },
        {
            "detailid": 23,
            "description": "tea"
        }
    ]
}]

标签： postgresql

Answer 1

您应该查看json 函数。您需要json_build_object形成对象并将json_agg它们聚合到 json 数组中：

CREATE FUNCTION jorder() 
RETURNS json 
LANGUAGE sql
AS $$
  SELECT 
    json_agg(orders.order)
  FROM (
    SELECT 
      json_build_object(
        'orderid', od.orderid, 
        'grandtotal', od.grandtotal, 
        'details', array_agg(
          json_build_object(
            'detailid', ds.detailid,
            'description', ds.description
          )
        )
      ) as order
    FROM 
      orders od
      JOIN odetails ds on od.orderid = ds.orderid
    GROUP BY 
      od.orderid, od.grandtotal
  ) as orders 
$$;

db<>fiddle的示例