例: 複数のサブクエリーとGROUP BYによるJSON_COMPOSEの使用 - Teradata Database - Teradata Vantage NewSQL Engine - この例では、作成への入力としてJSON_COMPOSEを複数のサブクエリーおよびJSON_Aggと共に使用します。

Teradata Vantage™ JSONデータ型

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
2019年3月
Language
日本語
Last Update
2019-10-29
dita:mapPath
ja-JP/bgi1512081870828.ditamap
dita:ditavalPath
ja-JP/bgi1512081870828.ditaval
dita:id
evi1472243742653
Product Category
Software
Teradata Vantage
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 }  
      ]
    }
   ]
  }