15.10 - Example: Use JSON_COMPOSE with JSON_AGG - Teradata Database

Teradata Database Teradata JSON

Product
Teradata Database
Release Number
15.10
Published
December 2015
Language
English (United States)
Last Update
2018-06-05
dita:mapPath
TeradataJSON_15_10.ditamap
dita:ditavalPath
ft:empty
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" } ]
 }