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

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
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" : "Teradata",
  "employees" : [ 
     { "id" : 1, "name" : "Cameron", "age" : 24 },
     { "id" : 2, "name" : "Justin", "age" : 34 }
  ]
}
{
  "company" : "Apple",
  "employees" : [ 
     { "id" : 3, "name" : "Someone", "age" : 24 } 
  ]
}