How to return data in JSON hierarchy?

Question

For the following data tables and function in 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;

How do I get return data in a JSON hierarchy like below?

[{
    "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"
        }
    ]
}]

You should fix your question: the code you provided has syntax errors. — Julius Tuskenis
– Julius Tuskenis, Commented Sep 14, 2021 at 6:31

Julius Tuskenis · Accepted Answer · 2021-09-14 06:38:00Z

1

You should look into json functions . You need json_build_object to form the objects and json_agg to aggregate them into json array:

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 
$$;

Example at db<>fiddle

edited Sep 14, 2021 at 6:38

answered Sep 14, 2021 at 6:30

Julius Tuskenis

1,63010 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to return data in JSON hierarchy?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related