Example: Use JSON_COMPOSE with Subqueries and GROUP BY

Teradata Vantageā„¢ JSON Data Type

brand
Software
Teradata Vantage
prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1150-162K
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 }  
  ]
}