Oracle started adding JSON support to Oracle Database with version 12.1.0.2. The earliest support was targeted at storing, indexing, and querying JSON data. Version 12.2 rounded out that support by adding features for generating, exploring, and processing JSON data. See the JSON Developer’s Guide for a comprehensive overview of what’s now available. In this post, I’ll leverage the new SQL operators for JSON generation to convert the relational data to meet the goal.

Please Note: This post is part of a series on generating JSON from relational data in Oracle Database. See that post for details on the solution implemented below as well as other options that can be used to achieve that goal.

Solution

The 12.2+ SQL functions available for JSON generation are:

• JSON_OBJECT – single-row function, creates an object for each row.
• JSON_ARRAY – single-row function, creates an array for each row.
• JSON_OBJECTAGG – aggregate function, creates an object based on groups of rows.
• JSON_ARRAYAGG – aggregate function, creates an array based on groups of rows.

The following solution uses JSON_OBJECT and JSON_ARRAYAGG multiple times, nesting them as needed to produce the desired output.

select json_object(
  'id' is department_id,
  'name' is department_name,
  'location' is (
    select json_object(
      'id' is location_id,
      'streetAddress' is street_address,
      'postalCode' is postal_code,
      'country' is (
        select json_object(
          'id' is country_id,
          'name' is country_name,
          'regionId' is region_id 
        )
        from countries
        where country_id = loc.country_id
      )
    )
    from locations loc
    where location_id = dept.location_id
  ),
  'manager' is (
    select json_object(
      'id' is employee_id,
      'name' is first_name || ' ' || last_name,
      'salary' is salary,
      'job' is (
        select json_object(
          'id' is job_id,
          'title' is job_title,
          'minSalary' is min_salary,
          'maxSalary' is max_salary
        )
        from jobs
        where job_id = man.job_id
      )
    )
    from employees man
    where employee_id = dept.manager_id
  ),
  'employees' is (
    select json_arrayagg(
      json_object(
        'id' is employee_id,
        'name' is first_name || ' ' || last_name,
        'isSenior' is 
          case when emp.hire_date < to_date('01-01-2005', 'dd-mm-yyyy')
            then 'true'
            else 'false'
          end format json,
        'commissionPct' is commission_pct,
        'jobHistory' is (
          select json_arrayagg(
            json_object(
              'id' is job_id,
              'departmentId' is department_id,
              'startDate' is to_char(start_date, 'DD-MON-YYYY'),
              'endDate' is to_char(end_date, 'DD-MON-YYYY')
            )
          )
          from job_history
          where employee_id = emp.employee_id
        )
      )
    )
    from employees emp
    where department_id = dept.department_id
  )
) as department
from departments dept
where department_id = :department_id

As was the case with the SQL query used for ORDS, this is a fairly large query. But I love the control the new SQL operators provide! As an example, I’ve highlighted line 50, which uses the FORMAT JSON keywords to declare that the value is to be considered as JSON data. This allowed me to add Boolean values to the JSON output despite the fact that Oracle’s SQL engine doesn’t support Boolean. There are other optional keywords to modify the behavior of the JSON generating functions.

Output

I’m happy to report that the solution above generates JSON that meets the goal 100%!

{
  "id": 10,
  "name": "Administration",
  "location": {
    "id": 1700,
    "streetAddress": "2004 Charade Rd",
    "postalCode": "98199",
    "country": {
      "id": "US",
      "name": "United States of America",
      "regionId": 2
    }
  },
  "manager": {
    "id": 200,
    "name": "Jennifer Whalen",
    "salary": 4400,
    "job": {
      "id": "AD_ASST",
      "title": "Administration Assistant",
      "minSalary": 3000,
      "maxSalary": 6000
    }
  },
  "employees": [
    {
      "id": 200,
      "name": "Jennifer Whalen",
      "isSenior": true,
      "commissionPct": null,
      "jobHistory": [
        {
          "id": "AD_ASST",
          "departmentId": 90,
          "startDate": "17-SEP-1987",
          "endDate": "17-JUN-1993"
        },
        {
          "id": "AC_ACCOUNT",
          "departmentId": 90,
          "startDate": "01-JUL-1994",
          "endDate": "31-DEC-1998"
        }
      ]
    }
  ]
}

However, when I ran this query on department 60 (which as the most employees) I received this error: ORA-40459: output value too large (actual: 4071, maximum: 4000). This is because each of the JSON generation functions has a default output of varchar2(4000). This is fine for many use cases, but it’s easily exceeded with the aggregate functions and deeply nested structures.

The solution is to leverage the RETURNING clause to specify a different data type or size. See this gist to get an idea of how the solution above could be modified to use the RETURNING clause. In 12.2, there were some restrictions on which functions could work with CLOBs, but they’ve been lifted in 18c.

Summary

This is my favorite solution of the series – by far! The JSON generation functions are very powerful and because they’re in the database it’s possible to leverage them from just about anywhere, including Node.js and ORDS.