例: JSON_AGGによりJSON_COMPOSEを使用 - Advanced SQL Engine - Teradata Database

この例では、JSON_AGGを使用して従業員IDと従業員名を集約し、JSON_COMPOSEを使用して、抽出され集約された値からJSONオブジェクトを作成します。

SELECT JSON_Compose(T.company, T.empAge AS age, T.employees)
FROM
 (
  SELECT company, empAge, 
     JSON_agg(empID AS id, 
              empName AS name) AS employees 
  FROM emp_table
  GROUP BY company, empAge
 ) AS T;

結果:

JSON_Compose
------------
  {
    "company" : "Teradata",
    "age" : 24,
    "employees" : [ 
    { "id" : 1, "name" : "Cameron" }
    ]
 }
 {
   "company" : "Teradata",
   "age" : 34,
   "employees" : [ 
   { "id" : 2, "name" : "Justin" }
   ]
 }
 {
   "company" : "Apple",
   "age" : 24,
   "employees" : [ { "id" : 3, "name" : "Someone" } ]
 }