16 Clauses Used in SQL Functions and Conditions for JSON
Clauses RETURNING
, wrapper, error, and empty-field are described for SQL functions that use JSON data. Each clause is used in one or more of the SQL functions and conditions json_value
, json_query
, json_table
, json_serialize
, json_mergepatch
, is json
, is not json
, json_exists
, and json_equal
.
- RETURNING Clause for SQL Query Functions
SQL functionsjson_value
,json_query
,json_serialize
, andjson_mergepatch
accept an optionalRETURNING
clause, which specifies the data type of the value returned by the function. This clause and the default behavior (noRETURNING
clause) are described here. - Wrapper Clause for SQL/JSON Query Functions JSON_QUERY and JSON_TABLE
SQL/JSON query functionsjson_query
andjson_table
accept an optional wrapper clause, which specifies the form of the value returned byjson_query
or used for the data in ajson_table
column. This clause and the default behavior (no wrapper clause) are described here. Examples are provided. - Error Clause for SQL Query Functions and Conditions
Some SQL query functions and conditions 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 functionsjson_value
,json_query
, andjson_table
accept an optionalON 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 (noON EMPTY
clause) are described here. - ON MISMATCH Clause for SQL/JSON Query Functions
You can use anON MISMATCH
clause with SQL/JSON functionsjson_value
,json_query
, andjson_table
, to handle type-matching exceptions. It specifies handling to use when a targeted JSON does not match the specified SQL return value. This clause and its default behavior (noON MISMATCH
clause) are described here.
Parent topic: Query JSON Data
16.1 RETURNING Clause for SQL Query Functions
SQL functions json_value
, json_query
,
json_serialize
, and json_mergepatch
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: VARCHAR2
,
NUMBER
, BINARY_DOUBLE
,
BINARY_FLOAT
, DATE
(with optional keywords
PRESERVE TIME
or TRUNCATE TIME
),
TIMESTAMP
, TIMESTAMP WITH TIME ZONE
,
INTERVAL YEAR TO MONTH
, INTERVAL DAY TO
SECOND
, SDO_GEOMETRY
, and CLOB
. 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_query
, json_serialize
, and
json_mergepatch
you can use VARCHAR2
,
CLOB
, BLOB
, or JSON
.Foot 1
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 alsoJSON
.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. Thejson_query
invocation then returns only non-scalar JSON values (which provides the same behavior as if RFC 8259 were not supported).
The RETURNING
clause also accepts two 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.
Tip:
You can pretty-print the entire context item by using only
$
as the path expression.
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 SQLNULL
. -
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 smallN
forVARCHAR2(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.
Related Topics
See Also:
-
Oracle Database SQL Language Reference for information about SQL data types
DATE
andTIMESTAMP
-
Oracle Database SQL Language Reference for information about SQL data type
NUMBER
-
Oracle Spatial Developer's Guide for information about using Oracle Spatial and Graph data
Parent topic: Clauses Used in SQL Functions and Conditions for JSON
16.2 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 than20
. -
Keywords
DISALLOW SCALARS
are used in theRETURNING
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.
Table 16-1 illustrates the wrapper-clause possibilities. The
array wrapper is shown in bold
italics
.
Table 16-1 JSON_QUERY Wrapper Clause Examples
JSON Values Matching Path Expression | WITH WRAPPER | WITHOUT WRAPPER | WITH CONDITIONAL WRAPPER |
---|---|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Error (multiple values) |
|
none |
Determined by the
|
Error (no values) |
Same as |
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 16-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
Parent topic: Clauses Used in SQL Functions and Conditions for JSON
16.3 Error Clause for SQL Query Functions and Conditions
Some SQL query functions and conditions 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
– ReturnNULL
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
andjson_equal
, for which it is the default. -
TRUE ON ERROR
– Return true instead of raising the error.Available only for
json_exists
andjson_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 asEMPTY 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_mergepatch
, or ajson_table
column value clause that hasjson_exists
behavior -
For
json_query
or ajson_table
column value clause that hasjson_query
behavior -
For row-level error-handing for
json_table
-
When
SDO_GEOMETRY
is specified either as theRETURNING
clause data type forjson_value
or as ajson_table
column data type
-
The default behavior is NULL ON ERROR
, except for conditions json_exists
and json_equal
.
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.
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
Parent topic: Clauses Used in SQL Functions and Conditions for JSON
16.4 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.
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 28-4 illustrates this use of NULL ON EMPTY
when creating an index on a json_value
expression.
16.5 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 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 SQLNULL
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 ajson_table
column withjson_value
semantics) is used. An error is raised if data of another type is targeted. -
NULL ON MISMATCH
— Return SQLNULL
as the value. -
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 fieldaddress
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 16-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.
Table 16-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 |
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
componentFoot 2
|
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
componentFoot 2 |
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 componentFoot 2 |
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 2 For 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 16-1 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;
Parent topic: Clauses Used in SQL Functions and Conditions for JSON
Footnote Legend
Footnote 1:JSON
data type is available only if database
initialization parameter compatible
is 20
or
greater.