Overview of JSON Generation

25.1 Overview of JSON Generation

An overview covers best practices for JSON generation, the SQL/JSON generation functions, a simple JSON constructor syntax, handling of input SQL values, and resulting generated data.

The best way to generate JSON data from non-JSON database data is to use SQL. The standard SQL/JSON functions, json_object, json_array, json_objectagg, and json_arrayagg are designed specifically for this. If the generated data is to be of JSON type then a handy alternative is to use the JSON data type constructor function, JSON.

SQL/JSON function json_scalar produces a JSON scalar value of JSON type. See SQL/JSON Function JSON_SCALAR.

Both the JSON constructor and the generation functions make it easy to construct JSON data directly from a SQL query. They allow non-JSON data to be represented as JSON objects and JSON arrays. You can generate complex, hierarchical JSON documents by nesting calls to the generation functions or constructor JSON. Nested subqueries can generate JSON data that represents one-to-many relationships.^Foot 1

The Best Way to Construct JSON Data from Non-JSON Data

Alternatives to using the SQL/JSON generation functions are generally error prone or inefficient.

Using string concatenation to generate JSON documents is error prone. In particular, there are a number of complex rules that must be respected concerning when and how to escape special characters, such as double quotation marks ("). It is easy to overlook or misunderstand these rules, which can result in generating incorrect JSON data.
Reading non-JSON result sets from the database and using client-side application code to generate JSON data is typically quite inefficient, particularly due to network overhead. When representing one-to-many relationships as JSON data, multiple SELECT operations are often required, to collect all of the non-JSON data needed. If the documents to be generated represent multiple levels of one-to-many relationships then this technique can be quite costly.

The SQL/JSON generation functions and constructor JSON do not suffer from such problems; they are designed for the job of constructing JSON data from non-JSON database data.

They always construct well-formed JSON documents.
By using SQL subqueries with the functions, you can generate an entire set of JSON documents using a single SQL statement, which allows the generation operation to be optimized.
Because only the generated documents are returned to a client, network overhead is minimized: there is at most one round trip per document generated.

The SQL/JSON Generation Functions

Functions json_object and json_array construct a JSON object or array, respectively. In the simplest case, json_object takes SQL name–value pairs as arguments, and json_array takes SQL values as arguments.
Functions json_objectagg, and json_arrayagg are aggregate SQL functions. They transform information that is contained in the rows of a grouped SQL query into JSON objects and arrays, respectively. Evaluation of the arguments determines the number of object members and array elements, respectively; that is, the size of the result reflects the current queried data.

For json_objectagg and json_arrayagg, the order of object members and array elements, respectively, is unspecified. For json_arrayagg, you can use an ORDER BY clause within the json_arrayagg invocation to control the array element order.

Result Returned by SQL/JSON Generation Functions

By default, the generated JSON data is returned from a generation function as a SQL VARCHAR2(4000) value. You can use the optional RETURNING clause to specify a different VARCHAR2 size or to specify a JSON, CLOB or BLOB return value instead. When BLOB is the return type, the character set is AL32UTF8.

Unless the return type is JSON, the JSON values produced from the input SQL values are serialized to textual JSON. This serialization has the same effect as SQL/JSON function json_serialize.

Note:

SQL/JSON function json_serialize consistently serializes values of Oracle JSON-language scalar types, such as float and date, using standard formats.

For example, it serializes a JSON date value using the ISO 8601 date format YYYY-MM-DD.

If you want to generate a textual JSON object or array that contains a JSON string value in some other format, then provide a string in that format as input to the generation function. (String input to json_serialize is simply output as is.)

For example, if you want json_object to produce an object with a string field that has a different ISO 8601 date format, then use SQL conversion function such as to_char to provide that string and pass it to json_object.

Handling of Input Values For SQL/JSON Generation Functions

The SQL/JSON generation functions take SQL values as input and, from them, produce JSON values inside the JSON object or array that is returned. How the input values produce the JSON values used in the output depends on their SQL data type. See Handling of Input Values For SQL/JSON Generation Functions.

Optional Behavior For SQL/JSON Generation Functions

You can optionally specify a SQL NULL-handling clause, a RETURNING clause, and keywords STRICT and WITH UNIQUE KEYS.

NULL-handling clause — Determines how a SQL NULL value resulting from input evaluation is handled.
- NULL ON NULL — An input SQL NULL value is converted to JSON null for inclusion in the output JSON object or array. This is the default behavior for json_object and json_objectagg.
- ABSENT ON NULL — An input SQL NULL value results in no corresponding output. This is the default behavior for json_array and json_arrayagg.
- EMPTY STRING ON NULL An input SQL NULL value is converted to an empty JSON string, "" for inclusion in the output JSON object or array.
RETURNING clause — The SQL data type used for the function return value. The return type can be any of the SQL types that support JSON data: JSON, VARCHAR2, CLOB, or BLOB. The default return type (no RETURNING clause) is VARCHAR2(4000).
STRICT keyword — If present, the returned JSON data is checked to be sure it is well-formed. If STRICT is present and the returned data is not well-formed then an error is raised.

Note:

In general, you need not specify STRICT when generating data of JSON data type, and doing so can introduce a small performance penalty.

When an input and the returned data are both of JSON type, if you do not specify STRICT then that input is used as is in the returned data; it is not checked for strict well-formedness.

You might want to use STRICT when returning JSON type data if (1) the input data is also of JSON type and (2) you suspect that it is not completely strict. That could be the case, for example, if a client application created the input data and it did not ensure that each JSON string is represented by a valid UTF-8 sequence of bytes.
WITH UNIQUE KEYS keywords, when generating textual JSON data — If present, the returned JSON object is checked to be sure there are no duplicate field names. If there are duplicates, an error is raised. These keywords are available only for json_object and json_objectagg.

When generating textual JSON data, if WITH UNIQUE KEYS is absent (or if WITHOUT UNIQUE KEYS is present) then no check for unique fields is performed. In that case all fields are used, including any duplicates.

WITH[OUT] UNIQUE KEYS has no effect when producing JSON-type data. An error is always raised when there are duplicate keys if the return type is JSON.

JSON Data Type Constructor

You can use constructor JSON with a special syntax as an alternative to using json_object and json_array when generating data of data type JSON. (You can use constructor JSON and JSON type only if database initialization parameter compatible is at least 20. Otherwise an error is raised.)

The only difference in behavior is that the return data type when you use the constructor is always JSON (there is no RETURNING clause for the constructor).

When employed as an alternative syntax for json_object or json_array, you follow constructor JSON directly with braces ({}) and brackets ([]), respectively, for object and array generation, instead of the usual parentheses (()).

JSON { … } has the same effect as JSON(json_object( … )), which has the same effect as json_object( … RETURNING JSON).
JSON [ … ] has the same effect as JSON(json_array( … )), which has the same effect as json_array( … RETURNING JSON).

All of the behavior and syntax possibilities that json_object and json_array offer when they are used with RETURNING JSON are also available when you use constructor JSON with the special syntax. See Example 25-2, Example 25-3, Example 25-4, Example 25-5, Example 25-6, Example 25-8, and Example 25-9

JSON {…} and JSON […] provide alternative syntax only for json_object and json_array, not for the aggregate generation functions, json_objectagg and json_arrayagg. But you can pass a SQL query expression as argument to json_array, and thus also use it as the (single) argument to JSON […]. For example, these two queries are equivalent:

SELECT json_arrayagg(department_name)FROM departments;

SELECT json_array(SELECT department_name FROM departments);

And you can of course use constructor JSON (without the special syntax) on the result of an explicit call to json_objectagg or json_arrayagg. For example, these two queries are equivalent:

SELECT JSON(json_objectagg(department_name VALUE department_id))
  FROM departments;


SELECT json_objectagg(department_name VALUE department_id
                      RETURNING JSON)
  FROM departments;