Example 25-11 Using JSON_ARRAYAGG to Construct a JSON Array

This example constructs a JSON object for each employee of table hr.employees (from standard database schema HR) who is a manager in charge of at least six employees. The objects have fields for the manager id number, manager name, number of employees reporting to the manager, and id numbers of those employees.

The order of the employee id numbers in the array is determined by the ORDER BY clause for json_arrayagg. The default direction for ORDER BY is ASC (ascending). The array elements, which are numeric, are in ascending numerical order.

SELECT json_object('id'         VALUE mgr.employee_id, 
                   'manager'    VALUE (mgr.first_name || ' '|| mgr.last_name),
                   'numReports' VALUE count(rpt.employee_id),
                   'reports'    VALUE json_arrayagg(rpt.employee_id
                                                    ORDER BY rpt.employee_id))
  FROM  employees mgr, employees rpt
  WHERE mgr.employee_id = rpt.manager_id
  GROUP BY mgr.employee_id, mgr.last_name, mgr.first_name
  HAVING count(rpt.employee_id) > 6;

-- The returned object is pretty-printed here for clarity.

JSON_OBJECT('ID'ISMGR.EMPLOYEE_ID,'MANAGER'VALUE(MGR.FIRST_NAME||''||MGR.LAST_NAME)
-----------------------------------------------------------------------------------
{"id":         100,
 "manager":    "Steven King",
 "numReports": 14,
 "reports":    [101,102,114,120,121,122,123,124,145,146,147,148,149,201]}

{"id":         120,
 "manager":    "Matthew Weiss",
 "numReports": 8,
 "reports":    [125,126,127,128,180,181,182,183]}

{"id":         121,
 "manager":    "Adam Fripp",
 "numReports": 8,
 "reports":    [129,130,131,132,184,185,186,187]}

{"id":         122,
 "manager":    "Payam Kaufling",
 "numReports": 8,
 "reports":    [133,134,135,136,188,189,190,191]}

{"id":         123,
 "manager":    "Shanta Vollman",
 "numReports": 8,
 "reports":    [137,138,139,140,192,193,194,195]}

{"id":         124,
 "manager":    "Kevin Mourgos",
 "numReports": 8,
 "reports":    [141,142,143,144,196,197,198,199]}

Example 25-12 Generating JSON Objects with Nested Arrays Using a SQL Subquery

This example shows a SQL left outer join between two tables: countries and regions. Table countries has a foreign key, region_id, which joins with the primary key of table regions, also named region_id.

The query returns a JSON object for each row in table regions. Each of these region objects has a countries field whose value is an array of country objects — the countries in that region.

SELECT json_object(
         'region'    : region_name,
         'countries' :
         (SELECT json_arrayagg(json_object('id'   : country_id,
                                           'name' : country_name))
            FROM countries c
            WHERE c.region_id = r.region_id))
  FROM regions r;

The query results in objects such as the following:

{"region"    : "Europe",
 "countries" : [ {"id"   : "BE",
                  "name" : "Belgium"},
                 {"id"   : "CH",
                  "name" : "Switzerland"},
                 {"id"   : "DE",
                  "name" : "Germany"},
                 {"id"   : "DK",
                  "name" : "Denmark"},
                 {"id"   : "FR",
                  "name" : "France"},
                 {"id"   : "IT",
                  "name" : "Italy"},
                 {"id"   : "NL",
                  "name" : "Netherlands"},
                 {"id"   : "UK",
                  "name" : "United Kingdom"} ]}