Relational to JSON with SQL

By October 12, 2018 Uncategorized

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.

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.

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%!

However, when I ran this query on department 50 (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.

Leave a Reply