The example uses JSON_COMPOSE to select items from tables and group the output, with a subquery that uses JSON_AGG
SELECT JSON_Compose(O.customer,
O.orderID,
O.price,
I.JA AS items)
FROM
(
SELECT customer AS customer,
orderId AS orderID,
price (FORMAT '$(9).9(2)') AS price
FROM order_table
) AS O,
(
SELECT orderID AS orderID,
JSON_Agg(itemID AS ID,
itemName AS name,
amount AS amt) AS JA
FROM item_table
GROUP BY orderID
) AS I
WHERE O.orderID = I.orderID;
Result:
JSON_Compose
------------
{
"customer" : "Teradata",
"orderID" : 1,
"price" : "$1000.00",
"items" :
[
{ "ID" : 1, "name" : "disk", "amt" : 100 },
{ "ID" : 2, "name" : "RAM", "amt" : 200 },
]
}
{
"customer" : "Teradata",
"orderID" : 2,
"price" : "$10000.00",
"items" :
[
{ "ID" : 1, "name" : "disk", "amt" : 10 },
{ "ID" : 2, "name" : "RAM", "amt" : 20 },
{ "ID" : 3, "name" : "monitor", "amt" : 30 },
{ "ID" : 4, "name" : "keyboard", "amt" : 40 }
]
}
{
"customer" : "Apple",
"orderID" : 3,
"price" : "$100000.00",
"items" :
[
{ "ID" : 1, "name" : "disk", "amt" : 10 },
{ "ID" : 2, "name" : "RAM", "amt" : 20 },
{ "ID" : 3, "name" : "monitor", "amt" : 30 },
{ "ID" : 4, "name" : "keyboard", "amt" : 40 },
{ "ID" : 5, "name" : "camera", "amt" : 50 },
{ "ID" : 6, "name" : "button", "amt" : 60 },
{ "ID" : 7, "name" : "mouse", "amt" : 70 },
{ "ID" : 8, "name" : "pen", "amt" : 80 }
]
}