例: 複数のサブクエリーとGROUP BYによるJSON_COMPOSEの使用

Teradata® Database JSONデータ型

brand
Software
prodname
Teradata Database
vrm_release
16.20
category
プログラミング リファレンス
featnum
B035-1150-162K-JPN
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 }  
      ]
    }
   ]
  }