Example: Use JSON_COMPOSE to Extract Values from a Table and Compose a JSON Object - Teradata Vantage

Use JSON_COMPOSE to create a JSON object from the values selected from a table.

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

Result:

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