ON MISMATCH Clause for SQL/JSON Query Functions

18.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 18-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 18-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 1	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 1	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 1	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 1For 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 18-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