customerとorderIDによってグループ化され各orderIDと関連付けられたすべての項目を持つJSONオブジェクトを作成します。
SELECT JSON_Compose(T.customer,
T.JA AS orders)
FROM
(
SELECT O.customer AS customer,
JSON_Agg(O.orderID, O.price, I.JA AS items) AS JA
FROM
(
SELECT customer AS customer,
orderId AS orderID,
price 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
GROUP BY O.customer
) AS T;
結果:
> JSON_Compose
------------
{
"customer" : "Teradata",
"orders" :
[
{
"orderID" : 1,
"price" : 1000,
"items" :
[
{ "ID" : 1, "name" : "disk", "amt" : 100 },
{ "ID" : 2, "name" : "RAM", "amt" : 200 }
]
},
{
"orderID" : 2,
"price" : 10000,
"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",
"orders" :
[
{
"orderID" : 3,
"price" : 100000,
"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 }
]
}
]
}