Clauses Used in SQL Functions and Conditions for JSON

17 Clauses Used in SQL Functions and Conditions for JSON

Clauses PASSING, RETURNING, wrapper, error, empty-field, on-mismatch and TYPE are described for SQL functions that use JSON data. Each clause is used in one or more of the SQL functions and conditions is json, is not json, json_array, json_arrayagg, json_equal, json_exists, json_mergepatch, json_query, json_object, json_objectagg, json_serialize, json_table, json_transform, and json_value.

PASSING Clause for SQL Functions and Conditions
Oracle SQL function json_transform, SQL/JSON functions json_value and json_query, and SQL/JSON condition json_exists accept an optional PASSING clause, which binds SQL values to SQL/JSON variables for use in path expressions.
RETURNING Clause for SQL Functions
SQL functions json_array, json_arrayagg, json_mergepatch, json_object, json_objectagg, json_query, json_serialize, json_transform, and json_value accept an optional RETURNING clause, which specifies the data type of the value returned by the function. This clause and the default behavior (no RETURNING clause) are described here.
Wrapper Clause for SQL/JSON Query Functions JSON_QUERY and JSON_TABLE
SQL/JSON query functions json_query and json_table accept an optional wrapper clause, which specifies the form of the value returned by json_query or used for the data in a json_table column. This clause and the default behavior (no wrapper clause) are described here. Examples are provided.
Error Clause for SQL Functions and Conditions
Some SQL query functions and conditions for JSON data accept an optional error clause, which specifies handling for a runtime error that is raised by the function or condition. This clause and the default behavior (no error clause) are summarized here.
Empty-Field Clause for SQL/JSON Query Functions
SQL/JSON query functions json_value, json_query, and json_table accept an optional ON EMPTY clause, which specifies the handling to use when a targeted JSON field is absent from the data queried. This clause and the default behavior (no ON EMPTY clause) are described here.
ON MISMATCH Clause for SQL/JSON Query Functions
You can use an ON MISMATCH clause with SQL/JSON functions json_value, json_query, and json_table, to handle type-matching exceptions. It specifies handling to use when a targeted JSON value does not match the specified SQL return value. This clause and its default behavior (no ON MISMATCH clause) are described here.
TYPE Clause for SQL Functions and Conditions
Oracle SQL function json_transform, SQL/JSON functions json_query, json_value and json_table, and SQL/JSON condition json_exists accept optional TYPE clauses, which specify whether JSON values are compared strictly with respect to JSON-language type, that is, as if the relevant "only" data-type conversion item methods were applied to the data being compared.

Parent topic: Query JSON Data

17.1 PASSING Clause for SQL Functions and Conditions

Oracle SQL function json_transform, SQL/JSON functions json_value and json_query, and SQL/JSON condition json_exists accept an optional PASSING clause, which binds SQL values to SQL/JSON variables for use in path expressions.

Keyword PASSING is followed by one or more comma-separated SQL/JSON variable bindings, such as 42 AS "d".

Each binding is composed of (1) a SQL expression to be evaluated; (2) keyword AS; and (3) a SQL/JSON variable name.^Foot 1 The binding 42 AS "d" binds the value of expression 42 to the SQL/JSON variable named d, which can be used in a path-expression such as $.PONumber?(@ > $d).

If you use a PASSING clause together with a TYPE (STRICT) clause, then each value that's compared with a SQL/JSON variable in the path expression is compared strictly with respect to its JSON-language type, just as if the relevant "only" data-type conversion item method were applied to the value. The type used for comparison is that of the SQL/JSON variable.

For example, with TYPE (STRICT) specified, a comparison such as $.PONumber?(@ > $d) for a numeric value of variable $d is treated implicitly as if it were $.PONumber?(@.numberOnly() > $d). So these two queries behave the same: only PONumber fields whose value is numeric are considered, because the value of $d is numeric.

SELECT count(*) FROM j_purchaseorder
WHERE json_exists(po_document, '$.PONumber?(@.numberOnly() > $d)'
PASSING to_number(:1) AS "d");

SELECT count(*) FROM j_purchaseorder
WHERE json_exists(po_document, '$.PONumber?(@ > $d)'
PASSING to_number(:1) AS "d" TYPE(STRICT));

The expression to evaluate must be of data type BINARY_DOUBLE, BOOLEAN, DATE, JSON, NUMBER, TIMESTAMP, or TIMESTAMP WITH TIME ZONE, VARCHAR2, VECTOR; otherwise, an error is raised.

If the expression evaluates to a SQL NULL value, the effect depends on the SQL type of that NULL value, as follows:

Passing NULL of SQL type JSON raises an error.
Passing NULL of SQL type VARCHAR2 binds the variable to an empty JSON string, "".
Passing NULL of SQL type RAW binds the variable to a zero-length JSON binary value.
Passing NULL of any other SQL type binds the variable to a JSON null value.

Note:

A SQL/JSON variable name has the syntax of a SQL identifier, but with these restrictions:

A SQL/JSON variable name never includes quote characters, even when the SQL identifier used to define it includes them.

In a PASSING clause for JSON functions and conditions, the SQL identifier that follows keyword AS can be a quoted identifier or an unquoted identifier — for example, AS "d" or AS d. This defines a SQL/JSON variable named d in the first case (no quote characters in the name), and D in the second case (implicitly uppercase). (The SQL identifier in the first case is "d", not d, and in the second case it is D, not d.)
A SQL/JSON variable name must contain only ASCII alphanumeric characters or the ASCII underscore character (decimal code 95). In addition, the name must start with a letter or an underscore character, not a digit. For example, 42 AS "2d", 42 AS "d+", and 42 AS "dã" each raise an error, the first because it starts with a numeral, the second because it contains an ASCII character that's not alphanumeric (+), and the third because it contains a non-ASCII character (ã).

A SQL/JSON variable is $ followed by a SQL/JSON variable name — for example, $d is the variable with name D.

A SQL/JSON variable, not a SQL identifier, is used in a SQL/JSON path expression. In particular, this means that quote characters are never present — you just use the name directly. For example, $.PONumber?(@ > $"d") raises an error; $.PONumber?(@ > $d) has correct syntax.

Related Topics

See Also:

JSON_EXISTS Condition in Oracle Database SQL Language Reference for information about the PASSING clause

Parent topic: Clauses Used in SQL Functions and Conditions for JSON

17.2 RETURNING Clause for SQL Functions

SQL functions json_array, json_arrayagg, json_mergepatch, json_object, json_objectagg, json_query, json_serialize, json_transform, and json_value accept an optional RETURNING clause, which specifies the data type of the value returned by the function. This clause and the default behavior (no RETURNING clause) are described here.

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 2, 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.)

Note:

An instance of Oracle SQL data type DATE includes a time component. And in your JSON data you can use a string that represents an ISO 8601 date-with-time value, that is, it can have a time component.

By default, json_value with RETURNING DATE returns a SQL DATE value that has a zero time component (zero hours, minutes, and seconds). By default, a time component in the queried JSON scalar value is truncated in the returned SQL DATE instance. But before any time truncation is done, if the value represented by an ISO 8601 date-with-time string has a time-zone component then the value is first converted to UTC, to take any time-zone information into account.

You can use RETURNING DATE PRESERVE TIME to override this default truncating behavior and preserve the time component, when present, of the queried JSON scalar value. (Using RETURNING DATE TRUNCATE TIME has the same effect as just RETURNING DATE, the default behavior.)

(The same considerations apply to item methods date(), which corresponds to TRUNCATE TIME, and dateWithTime(), which corresponds to PRESERVE TIME.)

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 3

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(po_document, '$.PONumber.numberOnly()') FROM j_purchaseorder
  RETURNING NUMBER;

SELECT json_value(po_document, '$.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.

Related Topics

See Also:

Oracle Database SQL Language Reference for information about SQL data types DATE and TIMESTAMP
Oracle Database SQL Language Reference for information about SQL data type NUMBER
Value LOBs in Oracle Database SecureFiles and Large Objects Developer's Guide for information about value-based LOBs
Oracle Spatial Developer's Guide for information about using Oracle Spatial and Graph data
GeoJSON.org

Parent topic: Clauses Used in SQL Functions and Conditions for JSON

17.3 Wrapper Clause for SQL/JSON Query Functions JSON_QUERY and JSON_TABLE

SQL/JSON query functions json_query and json_table accept an optional wrapper clause, which specifies the form of the value returned by json_query or used for the data in a json_table column. This clause and the default behavior (no wrapper clause) are described here. Examples are provided.

The JSON data targeted by a path expression for json_query or a json_table column can be a single JSON value (scalar, object, or array value), or it can be multiple JSON values. With an optional wrapper clause you can wrap the targeted data in an array before returning it.

For example, if the targeted data is the set of values "A50" and {"a": 42} you can specify that those be wrapped to return the array [ "A50", {"a": 42} ] (or [ {"a": 42}, "A50" ] — you cannot control the element order). Or if the only targeted value is 42 then you can wrap that and return the array [42].

Prior to Oracle Database 21c only RFC 4627 was supported, not RFC 8259. A single scalar JSON value could not be returned in this context — wrapping it in an array was necessary, to avoid raising an error. This is still the case if database initialization parameter compatible is less than 20. And even when RFC 8259 is supported you might sometimes want to wrap the result in an array.

The behavior of a wrapper clause (or its absence, which is the same as using keywords WITHOUT WRAPPER) depends on (1) whether or not the targeted JSON data is a single scalar value and (2) whether returning a single scalar value is allowed for the particular invocation of the SQL/JSON function.

Without wrapping, returning a single scalar value or multiple values (scalar or not) raises an error if either of the following is true:

Database initialization parameter compatible is less than 20.
Keywords DISALLOW SCALARS are used in the RETURNING clause.

The ON EMPTY clause takes precedence over the wrapper clause. The default for the former is NULL ON EMPTY, which means that if no JSON values match the path expression then SQL NULL is returned. If you want an empty JSON array ([]) returned instead then specify EMPTY ARRAY ON EMPTY. If you want an error raised instead then specify ERROR ON EMPTY.

The wrapper clause for nonempty matches is as follows:

WITH WRAPPER – Use a JSON array that contains all of the JSON values that match the path expression. The order of the array elements is unspecified.
WITHOUT WRAPPER – Use the JSON value or values that match the path expression.

Raise an error if either of these conditions holds:
- The path expression matches multiple values.
- Returning a scalar value is not allowed, and the path expression matches a single scalar value (not an object or array).
WITH CONDITIONAL WRAPPER – Use a value that represents all of the JSON values that match the path expression.

If multiple JSON values match then this is the same as WITH WRAPPER.

If only one JSON value matches:
- If returning a scalar value is allowed, or if the single matching value is an object or an array, then this is the same as WITHOUT WRAPPER.
- Otherwise, this is the same as WITH WRAPPER.

The default behavior is WITHOUT WRAPPER.

You can use keyword UNCONDITIONAL if you find that it makes your code clearer: WITH WRAPPER and WITH UNCONDITIONAL WRAPPER mean the same thing.

You can add keyword ARRAY immediately before keyword WRAPPER, if you find it clearer: WRAPPER and ARRAY WRAPPER mean the same thing.

Note:

You cannot use an array wrapper with json_query if you use clause OMIT QUOTES; a compile-time error is raised if you do that.

Table 17-1 illustrates the wrapper-clause possibilities. The array wrapper is shown in bold italics.

Table 17-1 JSON_QUERY Wrapper Clause Examples

JSON Values Matching Path Expression	WITH WRAPPER	WITHOUT WRAPPER	WITH CONDITIONAL WRAPPER
`{"id": 38327}` (single object)	`[{"id": 38327}]`	`{"id": 38327}`	`{"id": 38327}` (same as `WITHOUT WRAPPER`)
`[42, "a", true]` (single array)	`[[42, "a", true]]`	`[42, "a", true]`	`[42, "a", true]` (same as `WITHOUT WRAPPER`)
`42`	`[42]`	`42`, if returning a single scalar value is allowed Error, if returning a single scalar value is not allowed	`42`, if returning a single scalar value is allowed (same as `WITHOUT WRAPPER`) `[42]`, if returning a single scalar value is not allowed (same as `WITH WRAPPER`)
`42`, `"a"`, `true` (multiple values)	`[42, "a", true]`	Error (multiple values)	`[42, "a", true]` (same as `WITH WRAPPER`)
none	Determined by the `ON EMPTY` clause. SQL `NULL` by default (`NULL ON EMPTY`) `[]` with clause `EMPTY ARRAY ON EMPTY`	Error (no values)	Same as `WITH WRAPPER`.

Consider, for example, a json_query query to retrieve a JSON object. What happens if the path expression matches multiple JSON values (of any kind)? You might want to retrieve the matched values instead of raising an error. For example, you might want to pick one of the values that is an object, for further processing. Using an array wrapper lets you do this.

A conditional wrapper can be convenient if the only reason you are using a wrapper is to avoid raising an error and you do not need to distinguish those error cases from non-error cases. If your application is looking for a single object or array and the data matched by a path expression is just that, then there is no need to wrap that expected value in a singleton array.

On the other hand, with an unconditional wrapper you know that the resulting array is always a wrapper — your application can count on that. If you use a conditional wrapper then your application might need extra processing to interpret a returned array. In Table 17-1, for instance, note that the same array ([42, "a", true]) is returned for the very different cases of a path expression matching that array and a path expression matching each of its elements.

Related Topics

Support for RFC 8259: JSON Scalars

Parent topic: Clauses Used in SQL Functions and Conditions for JSON

17.4 Error Clause for SQL Functions and Conditions

Some SQL query functions and conditions for JSON data accept an optional error clause, which specifies handling for a runtime error that is raised by the function or condition. This clause and the default behavior (no error clause) are summarized here.

By default, SQL functions and conditions for JSON avoid raising runtime errors. For example, when JSON data is syntactically invalid, json_exists and json_equal return false and json_value returns NULL.

But in some cases you can also specify an error clause, which overrides the default behavior. The error handling you can specify varies, but each SQL function and condition for JSON that lets you specify error handling supports at least the ERROR ON ERROR behavior of raising an error.

The optional error clause can take these forms:

ERROR ON ERROR – Raise the error (no special handling).
NULL ON ERROR – Return NULL instead of raising the error.

Not available for json_exists.
FALSE ON ERROR – Return false instead of raising the error.

Available only for json_exists and json_equal, for which it is the default.
TRUE ON ERROR – Return true instead of raising the error.

Available only for json_exists and json_equal.
EMPTY OBJECT ON ERROR – Return an empty object ({}) instead of raising the error.

Available only for json_query.
EMPTY ARRAY ON ERROR – Return an empty array ([]) instead of raising the error.

Available only for json_query.
EMPTY ON ERROR – Same as EMPTY ARRAY ON ERROR.
DEFAULT 'literal_return_value' ON ERROR – Return the specified value instead of raising the error. The value must be a constant at query compile time.

Not available:
- For json_exists, json_equal, json_serialize, json_scalar, json_mergepatch, or a json_table column value clause that has json_exists behavior
- For json_query or a json_table column value clause that has json_query behavior
- For row-level error-handing for json_table
- When SDO_GEOMETRY is specified either as the RETURNING clause data type for json_value or as a json_table column data type

The default behavior is NULL ON ERROR, except for conditions json_exists and json_equal.

You can, however, change the default behavior for a given database session, using parameter JSON_BEHAVIOR. It affects only the error handlers for SQL operators that have NULL ON ERROR as the default behavior. This means that it does not affect SQL conditions json_exists and json_equal, or SQL function json_table for columns that use keyword EXISTS (they have json_exists semantics). It affects only functions json_value, json_query, and json_table without EXISTS.

The value you give to JSON_BEHAVIOR specifies the default behavior to use for the current session, as follows:

ON_ERROR:ERROR — ERROR ON ERROR behavior is the session default.
ON_ERROR:NULL — NULL ON ERROR behavior is the session default.

A typical use case is to set the parameter to ON_ERROR:ERROR for debugging purposes, to raise an error if path-expression evaluation finds no matching value in the queried JSON data. This is illustrated in Example 17-1.

Note:

There are two levels of error handling for json_table, corresponding to its two levels of path expressions: row and column. When present, a column error handler overrides row-level error handling. The default error handler for both levels is NULL ON ERROR.

Note:

An ON EMPTY clause overrides the behavior specified by ON ERROR for the error of trying to match a missing field.

Note:

The ON ERROR clause takes effect only for runtime errors that arise when a syntactically correct SQL/JSON path expression is matched against JSON data. A path expression that is syntactically incorrect results in a compile-time syntax error; it is not handled by the ON ERROR clause.

Example 17-1 Using Parameter JSON_BEHAVIOR To Provide ERROR ON ERROR Behavior

By default, json_value returns NULL on error, which can make it hard to notice a query with errors. This query returns NULL, because the path expression, $.a, does not match a single scalar value — it matches the multiple values 1 and 2.

SELECT json_value('[{a:1},{a:2}]', '$.a');

This code alters the value of parameter JSON_BEHAVIOR for the current session, causing occurrence of the same error to actually raise an error, instead of returning NULL:

ALTER SESSION SET JSON_BEHAVIOR=ON_ERROR:ERROR

SELECT json_value('[{a:1},{a:2}]', '$.a');

ORA-40470: JSON query '$.a' evaluated to multiple values.

This code resets the parameter to its default value:

ALTER SESSION SET JSON_BEHAVIOR=ON_ERROR:NULL

Related Topics

See Also:

Oracle Database SQL Language Reference for detailed information about the error clause for SQL functions for JSON
Oracle Database SQL Language Reference for detailed information about the error clause for SQL conditions for JSON
JSON_BEHAVIOR in Oracle Database Reference

Parent topic: Clauses Used in SQL Functions and Conditions for JSON

17.5 Empty-Field Clause for SQL/JSON Query Functions

SQL/JSON query functions json_value, json_query, and json_table accept an optional ON EMPTY clause, which specifies the handling to use when a targeted JSON field is absent from the data queried. This clause and the default behavior (no ON EMPTY clause) are described here.

You generally handle errors for SQL/JSON functions and conditions using an error clause (ON ERROR). However, there is a special case where you might want different handling from this general error handling: when querying to match given JSON fields that are missing from the data. Sometimes you do not want to raise an error just because a field to be matched is absent. (A missing field is normally treated as an error.)

You typically use a NULL ON EMPTY clause in conjunction with an accompanying ON ERROR clause. This combination specifies that other errors are handled according to the ON ERROR clause, but the error of trying to match a missing field is handled by just returning NULL. If no ON EMPTY clause is present then an ON ERROR clause handles also the missing-field case.

In addition to NULL ON EMPTY there are ERROR ON EMPTY and DEFAULT ... ON EMPTY, which are analogous to the similarly named ON ERROR clauses.

If only an ON EMPTY clause is present (no ON ERROR clause) then missing-field behavior is specified by the ON EMPTY clause, and other errors are handled the same as if NULL ON ERROR were present (it is the ON ERROR default). If both clauses are absent then only NULL ON ERROR is used.

Note:

When SQL/JSON function json_value is used in PL/SQL code with a RETURNING type that is a record type or an index-table type, a NULL value cannot be returned, because values of these types cannot be atomically NULL.

For this reason, clauses NULL ON MISMATCH and NULL ON EMPTY cannot return a NULL value for these collection types. Instead of returning NULL, a compile-time error is raised. (There is no such exception for PL/SQL code with a RETURNING type for SQL objects, varrays, or nested tables, because values of these types can be atomically NULL.)

Use NULL ON EMPTY for an Index Created on JSON_VALUE

NULL ON EMPTY is especially useful for the case of a functional index created on a json_value expression. The clause has no effect on whether or when the index is picked up, but it is effective in allowing some data to be indexed that would otherwise not be because it is missing a field targeted by the json_value expression.

You generally want to use ERROR ON ERROR for the queries that populate the index, so that a query path expression that results in multiple values or complex values raises an error. But you sometimes do not want to raise an error just because the field targeted by a path expression is missing — you want that data to be indexed. Example 29-4 illustrates this use of NULL ON EMPTY when creating an index on a json_value expression.

17.6 ON MISMATCH Clause for SQL/JSON Query Functions

You can use an ON MISMATCH clause with SQL/JSON functions json_value, json_query, and json_table, to handle type-matching exceptions. It specifies handling to use when a targeted JSON value does not match the specified SQL return value. This clause and its default behavior (no ON MISMATCH clause) are described here.

Note:

Clause ON MISMATCH applies only when neither of the clauses ON EMPTY and ON ERROR applies. It applies when the targeted JSON data matches the path expression, in general, but the type of that targeted data does not match the specified return type. More precisely, ON MISMATCH applies when the targeted data cannot be converted to the return type. For example, targeted value "cat", a JSON string, cannot be converted to a SQL NUMBER value.

Clause ON EMPTY applies when the field targeted by a path expression does not exist in the queried data.

Clause ON ERROR applies when any error is raised while processing the query. This includes the cases of invalid query syntax and targeting of multiple values in a json_value query or a json_query query without an array wrapper.

When a query returns a SQL value that reflects the JSON data targeted by function json_value, json_query, or json_table, the types of the targeted data and the value to be returned must match, or else an error is raised.

If an ON ERROR handler is specified then its behavior applies as the default behavior for ON MISMATCH: it is the behavior for a type mismatch if no ON MISMATCH clause is given.

You can use one or more ON MISMATCH clauses to specify type mismatch behavior in the following ways.

IGNORE ON MISMATCH — Explicitly specify the default behavior: ignore the mismatch. The object or collection returned can contain one or more SQL NULL values because of mismatches against the targeted JSON data.

This value is available only if the query targets an instance of a user-defined object or collection type, which can be the case only when json_value (or a json_table column with json_value semantics) is used. An error is raised if data of another type is targeted.
NULL ON MISMATCH — Return SQL NULL as the value.

Note:

When SQL/JSON function json_value is used in PL/SQL code with a RETURNING type that is a record type or an index-table type, a NULL value cannot be returned, because values of these types cannot be atomically NULL.

For this reason, clauses NULL ON MISMATCH and NULL ON EMPTY cannot return a NULL value for these collection types. Instead of returning NULL, a compile-time error is raised. (There is no such exception for PL/SQL code with a RETURNING type for SQL objects, varrays, or nested tables, because values of these types can be atomically NULL.)
ERROR ON MISMATCH — Raise an error for the mismatch.

When function json_value (or a json_table column with json_value semantics) returns a user-defined object-type or collection-type instance, each of the ON MISMATCH clause types can be followed, in parentheses ((…)), by one or more clauses that each indicates a kind of mismatch to handle, separated by commas (,). These are the possible mismatch kinds:

MISSING DATA — Some JSON data was needed to match the object-type or collection-type data, but it was missing.
EXTRA DATA — One or more JSON fields have no corresponding object-type or collection-type data. For example, for JSON field address there is no object-type attribute with the same name (matching case-insensitively, by default).
TYPE ERROR — A JSON scalar value has a data type that is incompatible with the corresponding return SQL scalar data type. This can be because of general type incompatibility, as put forth in Table 17-2, or because the SQL data type is too constraining (e.g., VARCHAR(2) is too short for JSON string "hello").

If no such kind-of-mismatch clause (e.g. EXTRA DATA) is present for a given handler (e.g. NULL ON MISMATCH) then that handler applies to all kinds of mismatch.

You can have any number of ON MISMATCH clauses of different kinds, but if two or more such contradict each other then a query compile-time error is raised.

Note:

When an "only" item method is used (an item method with "only" in its name), only a value of the specified type input type is compatible. For example, if item method booleanOnly() is used, then only an input value of JSON-language type boolean is compatible with (can be converted to) the destination SQL types listed (BOOLEAN, VARCHAR2, and CLOB).

In this context, item method idOnly() is an exception, in that it requires its input to not just be of JSON-language type family binary but to also be suitable as an identifier. See also Comparison and Sorting of JSON Data Type Values.

Table 17-2 Compatible Scalar Data Types: Converting JSON to SQL

JSON Language Type (Source)	SQL Type (Destination)	Notes
`binary`	`RAW`	Supported only for JSON data stored as SQL type `JSON`.
`binary`	`BLOB`	Supported only for JSON data stored as SQL type `JSON`.
`binary`	`CLOB`	Supported only for JSON data stored as SQL type `JSON`.
`boolean`	`BOOLEAN`	The instance value is the SQL Boolean value `TRUE` or `FALSE`.
`boolean`	`VARCHAR2`	The instance value is the SQL string `"true"` or `"false"`.
`boolean`	`CLOB`	The instance value is the SQL string `"true"` or `"false"`.
`date`	`DATE`, with a (possibly zero) time component^Foot 4	Supported only for JSON data stored as SQL type `JSON`.
`date`	`TIMESTAMP`	Time component is padded with zeros. Supported only for JSON data stored as SQL type `JSON`.
`daysecondInterval`	`INTERVAL DAY TO SECOND`	Supported only for JSON data stored as SQL type `JSON`.
`double`	`BINARY_DOUBLE`	Supported only for JSON data stored as SQL type `JSON`.
`double`	`BINARY_FLOAT`	Supported only for JSON data stored as SQL type `JSON`.
`double`	`NUMBER`	Supported only for JSON data stored as SQL type `JSON`.
`double`	`VARCHAR2`	Supported only for JSON data stored as SQL type `JSON`.
`double`	`CLOB`	Supported only for JSON data stored as SQL type `JSON`.
`float`	`BINARY_FLOAT`	Supported only for JSON data stored as SQL type `JSON`.
`float`	`BINARY_DOUBLE`	Supported only for JSON data stored as SQL type `JSON`.
`float`	`NUMBER`	Supported only for JSON data stored as SQL type `JSON`.
`float`	`VARCHAR2`	Supported only for JSON data stored as SQL type `JSON`.
`float`	`CLOB`	Supported only for JSON data stored as SQL type `JSON`.
`null`	Any SQL data type.	The instance value is SQL `NULL`.
`number`	`NUMBER`	None.
`number`	`BINARY_DOUBLE`	None.
`number`	`BINARY_FLOAT`	None.
`number`	`VARCHAR2`	None.
`number`	`CLOB`	None.
`string`	`VARCHAR2`	None.
`string`	`CLOB`	None.
`string`	`NUMBER`	The JSON string must be numeric.
`string`	`BINARY_DOUBLE`	The JSON string must be numeric.
`string`	`BINARY_FLOAT`	The JSON string must be numeric.
`string`	`DATE`, with a (possibly zero) time component^Foot 4	The JSON string must have a supported ISO 8601 format.
`string`	`TIMESTAMP`	The JSON string must have a supported ISO 8601 format.
`string`	`INTERVAL YEAR TO MONTH`	The JSON string must have a supported ISO 8601 duration format.
`string`	`INTERVAL DAY TO SECOND`	The JSON string must have a supported ISO 8601 duration format.
`timestamp`	`TIMESTAMP`	Supported only for JSON data stored as SQL type `JSON`,
`timestamp`	`DATE`, with a (possibly zero) time component^Foot 4	Supported only for JSON data stored as SQL type `JSON`.
`yearmonthInterval`	`INTERVAL YEAR TO MONTH`	Supported only for JSON data stored as SQL type `JSON`.

^Footnote 4For example, a DATE instance with a zero time component is returned by a json_value RETURNING DATE clause that does not specify preservation of the time component.

Example 17-2 Using ON MISMATCH Clauses

This example uses the following object-relational data with various queries. The queries are the same except for the type-mismatch behavior. Each query targets a non-existent JSON field middle.

CREATE TYPE person_T AS OBJECT (
  first     VARCHAR2(30),
  last      VARCHAR2(30),
  birthyear NUMBER);

This query returns the object person_t('Grace', 'Hopper', 1906). Field middle is ignored, because the default error handler is NULL ON ERROR.

SELECT json_value('{"first":     "Grace",
                    "middle":    "Brewster",
                    "last":      "Hopper",
                    "birthyear": "1906"}',
                  '$'
                  RETURNING person_t)
FROM DUAL;

This query raises an error because of the extra-data mismatch: field middle is extra.

SELECT json_value('{"first":     "Grace",
                    "middle":    "Brewster",
                    "last":      "Hopper",
                    "birthyear": "1906"}',
                  '$'
                  RETURNING person_t
                  ERROR ON MISMATCH (EXTRA DATA))
FROM DUAL;
ORA-40602: extra data for object type conversion

This query uses three ON MISMATCH clauses. It returns the object person_t('Grace', 'Hopper', NULL). The clause ERROR ON MISMATCH (EXTRA DATA) would, by itself, raise an error, but the IGNORE ON MISMATCH (TYPE ERROR) causes that error to be ignored.

SELECT json_value('{"first":     "Grace",
                    "middle":    "Brewster",
                    "last":      "Hopper",
                    "birthyear": "1906"}',
                  '$'
                  RETURNING person_t
                  ERROR ON MISMATCH (EXTRA DATA)
                  ERROR ON MISMATCH (MISSING DATA)
                  IGNORE ON MISMATCH (TYPE ERROR))
FROM DUAL;

Related Topics

Using JSON_VALUE To Instantiate a User-Defined Object-Type or Collection-Type Instance

Parent topic: Clauses Used in SQL Functions and Conditions for JSON

17.7 TYPE Clause for SQL Functions and Conditions

Oracle SQL function json_transform, SQL/JSON functions json_query, json_value and json_table, and SQL/JSON condition json_exists accept optional TYPE clauses, which specify whether JSON values are compared strictly with respect to JSON-language type, that is, as if the relevant "only" data-type conversion item methods were applied to the data being compared.

Keyword TYPE is followed, in parentheses, by keyword STRICT or LAX.

TYPE (LAX) specifies the default behavior (same as no TYPE clause), which is that JSON values can be implicitly interpreted (essentially cast) as values of SQL data types for purposes of comparison. This type-casting is explained in Types in Filter-Condition Comparisons.

For example, a comparison such as '$.PONumber?(@ > 20) implicitly interprets a PONumber value of "314" as the number 314 (because it is compared with the number 20). That comparison is true, just as if the expression were '$.PONumber?(@.number() > 20)
TYPE (STRICT) has the same effect as applying "only" item methods.

For example, '$.PONumber?(@ > 20) behaves as if it were '$.PONumber?(@.numberOnly() > 20). For a PONumber value of "314" the comparison is false, just as if the expression were '$.PONumber?(@.numberOnly() > 20).

See Also:

JSON_QUERY in Oracle Database SQL Language Reference

Parent topic: Clauses Used in SQL Functions and Conditions for JSON

Footnote Legend

Footnote 1: Wrapping a SQL/JSON variable name in double-quote (") characters in a PASSING clause is necessary only if you want a case-sensitive name.
Footnote 2: You can use RAW as the return type only when the input data is of JSON data type.
Footnote 3: JSON data type is available only if database initialization parameter compatible is 20 or greater.