Example: Use JSON_COMPOSE with JSON_AGG - Advanced SQL Engine - Teradata Database

JSON Data Type

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
September 2020
Language
English (United States)
Last Update
2021-01-23
dita:mapPath
gzn1554761068186.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1150
lifecycle
previous
Product Category
Teradata Vantageā„¢
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" } ]
 }