For json_value, you can use any of these SQL data types in a RETURNING clause: BINARY_DOUBLE, BINARY_FLOAT, BOOLEAN, CHAR, CLOB, DATE (with optional keywords PRESERVE TIME or TRUNCATE TIME), DOUBLE PRECISION, FLOAT, INTEGER, NUMBER, INTERVAL YEAR TO MONTH, INTERVAL DAY TO SECOND, NCHAR, NCLOB, NVARCHAR2, RAW^Foot 1, REAL, SDO_GEOMETRY, TIMESTAMP, TIMESTAMP WITH TIME ZONE, and VARCHAR2. You can also use a user-defined object type or a collection type.

(See Using SQL/JSON Function JSON_VALUE With a Boolean JSON Value for information about return types when a JSON Boolean value is targeted.)

For json_array, json_arrayagg, json_mergepatch, json_object, json_objectagg, json_query, json_serialize, and json_transform you can use VARCHAR2, CLOB, BLOB, or JSON.^Foot 2

A BLOB result is in the AL32UTF8 character set. Whatever the data type returned by json_serialize, the returned data represents textual JSON data.

You can optionally specify a length for VARCHAR2 (default: 4000) and a precision and scale for NUMBER.

Data type SDO_GEOMETRY is for Oracle Spatial and Graph data. In particular, this means that you can use json_value with GeoJSON data, which is a format for encoding geographic data in JSON.

For json_query (only), if database initialization parameter compatible is 20 or greater, and if the input data is of data type JSON:

• The default return type (no RETURNING clause) is also JSON.

  Otherwise, the default return type is VARCHAR2(4000).

• Regardless of the return data type, by default the data returned can be a scalar JSON value.

  You can override this behavior by including keywords DISALLOW SCALARS just after the return data type. The json_query invocation then returns only nonscalar JSON values (which provides the same behavior as if RFC 8259 were not supported).

The RETURNING clause also accepts optional keywords, PRETTY and ASCII, unless the return data type is JSON. If both are present then PRETTY must come before ASCII. Keyword PRETTY is not allowed for json_value.

The effect of keyword PRETTY is to pretty-print the returned data, by inserting newline characters and indenting. The default behavior is not to pretty-print.

The effect of keyword ASCII is to automatically escape all non-ASCII Unicode characters in the returned data, using standard ASCII Unicode escape sequences. The default behavior is not to escape non-ASCII Unicode characters.

If VARCHAR2 is specified in a RETURNING clause then scalars in the value are represented as follows:

• Boolean values are represented by the lowercase strings "true" and "false".

• The null value is represented by SQL NULL.

• A JSON number is represented in a canonical form. It can thus appear differently in the output string from its representation in textual input data. When represented in canonical form:

  • It can be subject to the precision and range limitations for a SQL NUMBER.

  • When it is not subject to the SQL NUMBER limitations:

    • The precision is limited to forty (40) digits.

    • The optional exponent is limited to nine (9) digits plus a sign (+ or -).

    • The entire text, including possible signs (-, +), decimal point (.), and exponential indicator (E), is limited to 48 characters.

  The canonical form of a JSON number:

  • Is a JSON number. (It can be parsed in JSON data as a number.)

  • Does not have a leading plus (+) sign.

  • Has a decimal point (.) only when necessary.

  • Has a single zero (0) before the decimal point if the number is a fraction (between zero and one).

  • Uses exponential notation (E) only when necessary. In particular, this can be the case if the number of output characters is too limited (by a small N for VARCHAR2(N)).

Oracle extends the SQL/JSON standard in the case when the returning data type is VARCHAR2(N), by allowing optional keyword TRUNCATE immediately after the data type. When TRUNCATE is present and the value to return is wider than N, the value is truncated — only the first N characters are returned. If TRUNCATE is absent then this case is treated as an error, handled as usual by an error clause or the default error-handling behavior.

If the value returned would undergo an automatic type conversion because of lax handling you can prevent this by using keywords TYPE (STRICT).

For example, this query returns the number 1 because the default behavior (TYPE (LAX)) automatically converts the string "1" to a JSON number:

SELECT json_value('{"a" : "1"}', '$.a' RETURNING NUMBER;

Using TYPE (STRICT) prevents type conversion — this query returns no value:

SELECT json_value('{"a" : "1"}', '$.a' RETURNING NUMBER TYPE (STRICT);

Using TYPE (STRICT) is equivalent to applying the relevant "only" data-type conversion item method. For example, these two queries are equivalent. Only PONumber fields whose values are numeric are considered (projected).

SELECT json_value(data, '$.PONumber.numberOnly()') FROM j_purchaseorder
  RETURNING NUMBER;

SELECT json_value(data, '$.PONumber') FROM j_purchaseorder
  RETURNING NUMBER TYPE (STRICT);

For any of the SQL functions for JSON that can return a LOB, by default the LOB is returned by reference. You can instead have it return a value-based LOB by following the return type (CLOB, BLOB, or NCLOB, depending on the function) with the keyword VALUE. For example:

SELECT json_value(...) FROM ... RETURNING CLOB VALUE;

Value-based LOBs are generally more efficient because they cannot accumulate on the database server if you forget to free them.