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" } ] }