Example: Use JSON_COMPOSE with JSON_AGG

Teradata Vantageā„¢ JSON Data Type

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1150-162K
The example uses JSON_AGG to aggregate employee ID and employee name, and uses JSON_COMPOSE to create a JSON object from the values extracted and aggregated.
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;
Result:
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" } ]
 }