The SQL/JSON generation functions take SQL values as input and return a JSON object or array. The input values are used to produce JSON object field–value pairs or JSON array elements. How the input values are used depends on their SQL data type.

The returned JSON object or array is of a SQL data type that supports JSON data: JSON, VARCHAR2, CLOB, or BLOB. The default return type is VARCHAR2(4000). In all cases, the return value is known by the database to contain well-formed JSON data.

Unless it is of JSON data type, an input can optionally be followed by keywords FORMAT JSON, which declares that the value is to be considered as already representing JSON data (you vouch for it), so it is interpreted (parsed) as JSON data.

For example, if the input is '{}' then you might want it to produce the empty JSON object, {}, and not the JSON string "{}". Example 25-1 illustrates the use of FORMAT JSON to cause an input VARCHAR2 string "{\"x\":5}" to produce the JSON object {"x":5}.

If the input data is of JSON type then it is used as is. This includes the case where the JSON type constructor is used. (Do not use FORMAT JSON in this case; otherwise, an error is raised.)

In some cases where an input is not of JSON type, and you do not use FORMAT JSON, Oracle nevertheless knows that the result is JSON data. In such cases using FORMAT JSON is not needed and is optional. This is the case, for example, if the input data is the result of using function json_query or one of the JSON generation functions.

If, one way or another, an input is known to be JSON data, then it is used essentially as is to construct the result — it need not be processed in any way. This applies regardless of whether the input represents a JSON scalar, object, or array.

If an input is not known to be JSON data, then it produces a JSON value as follows (any other SQL value raises an error):

• An instance of a user-defined SQL object type produces a JSON object whose field names are taken from the object attribute names and whose field values are taken from the object attribute values (to which JSON generation is applied recursively).

• An instance of a SQL collection type produces a JSON array whose element values are taken from the collection element values (to which JSON generation is applied recursively).

• A VARCHAR2, CLOB, or NVARCHAR value is wrapped in double quotation marks ("), and characters are escaped when necessary to conform to the JSON standard for a JSON string. For example, input SQL input '{}' produces the JSON string "{}".

• A numeric value produces a JSON numeric value.

  If database initialization parameter compatible is at least 20 then NUMBER input produces a JSON number value, BINARY_DOUBLE input produces a JSON double value, and BINARY_FLOAT input produces a JSON float value.

  If compatible is less than 20 then the value is a JSON number, regardless of the numeric input type (NUMBER, BINARY_DOUBLE, or BINARY_FLOAT).

  The numeric values of positive and negative infinity, and values that are the undefined result of a numeric operation ("not a number" or NaN), cannot be expressed as JSON numbers. They instead produce the JSON strings "Inf", "-Inf", and "Nan", respectively.

• A RAW or BLOB value produces a hexadecimal JSON string, with double quotation marks, (").

• A time-related value (DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, TIMESTAMP WITH LOCAL TIME ZONE, INTERVAL YEAR TO MONTH, or INTERVAL DAY TO SECOND) produces a supported ISO 8601 format, and the result is enclosed in double quotation marks (") as a JSON string.

• A BOOLEAN (SQL or PL/SQL) value of TRUE or FALSE produces JSON true or false, respectively.

• A SQL NULL value produces JSON null, regardless of the NULL data type.

• A SQL VECTOR value produces a JSON-language scalar value of type vector.

-- PL/SQL: Return a SQL string representing a JSON object
CREATE FUNCTION getX(n NUMBER) RETURN VARCHAR2 AS
BEGIN
  RETURN '{"x":'|| n ||'}';
END;

-- SQL: Generate JSON data from SQL
SELECT json_array(getX(5) FORMAT JSON) FROM DUAL;

SELECT json_array(getX(5) FORMAT JSON STRICT) FROM DUAL;