Example: Use ARRAY_AGG to Aggregate Salaries as Input to ARRAY_TO_JSON - Teradata Vantage NewSQL Engine - 16.20

Teradata Vantage™ JSON Data Type

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1150-162K
Use ARRAY_AGG to aggregate the salaries of the job positions as input to ARRAY_TO_JSON, to obtain JSON objects as output.
The example uses the table(s) created earlier.
SELECT salary, (ARRAY_TO_JSON(ARRAY_AGG(pos ORDER BY empId, NEW varchararr5())) 
    RETURNS JSON(100) CHARACTER SET LATIN)
FROM employeeTable 
GROUP BY salary
ORDER BY salary;
Result:
salary 	 ARRAY_TO_JSON(ARRAY_AGG(…))
-----------------------------------
50000 	 ["engineer","engineer","salesman","salesman","manager"]
75000 	 ["engineer","salesman","manager"]
100000 	["engineer","salesman","manager"]
125000 	["manager","executive"]
150000 	["executive","executive"]
200000 	["executive"]
1000000   ["executive"]