JSON_QUERY

Syntax

JSON_QUERY (expr, JSON_basic_path_expression 
[JSON_passing_clause] [JSON_query_returning_clause] [JSON_query_wrapper_clause]
[JSON_query_quotes_clause] [JSON_query_on_error_clause] [JSON_query_on_empty_clause] 
[JSON_query_on_mismatch_clause][TYPE {STRICT|LAX}])

expr

expr::= CHAR|NCHAR|VARCHAR2|NVARCHAR2|BLOB|CLOB|NCLOB|JSON

This is the first of two required arguments to the JSON_QUERY function.

The argument is a SQL expression that returns an instance of a SQL data type that contains JSON data. The SQL expression accepts as input data types that include the following: CHAR, NCHAR, VARCHAR2, NVARCHAR2, BLOB, CLOB, NCLOB, and JSON.

If expr is null, then JSON_QUERY returns SQL NULL. If expr is not a text literal of well-formed JSON data using strict or lax syntax, then the function returns SQL NULL by default. You can use the JSON_query_on_error_clause to override this default behavior.

JSON_basic_path_expression

This is the second of two required arguments to the JSON_QUERY function.

TimesTen provides SQL access to JSON data using SQL/JSON path expressions. Use this clause to specify a SQL/JSON path expression. The JSON_QUERY function uses the path expression to evaluate expr and find one or more JSON values that match or satisfy the path expression. The path expression must be a text literal.

You can use one or more filter expressions in the path expression to select documents based on their content. If the path expression contains a filter, then the data that matches must also satisfy the filter in order for JSON_QUERY to return the value. The filter expression may refer to SQL/JSON variables, whose values are passed from SQL by binding them with the JSON_passing_clause.

For specific syntax, see JSON_EXISTS Condition in this book. For complete details about syntax and semantics, see SQL/JSON Path Expressions and JSON_QUERY Function in the Oracle TimesTen In-Memory Database JSON Developer's Guide.

JSON_passing_clause

PASSING expr AS identifier [,expr AS identifier]...

Use this optional clause to pass values to the path expression. The result of evaluating expr is bound to the corresponding identifier in JSON_basic_path_expression.

JSON_query_returning_clause

RETURNING JSON_query_return_type [{ALLOW | DISALLOW} SCALARS][PRETTY] [ASCII]

Use this optional clause to specify the data type and format of the character string returned by this function. If you do not specify a returning clause, the default return type depends on the input data type. For example, if the input type is JSON, JSON is the default return type. Otherwise VARCHAR2(4000) is the default return type.

There are optional clauses and keywords to the RETURNING clause, such as allowing or disallowing scalars, TRUNCATE (if the return type is VARCHAR2 or NVARCHAR2), PRETTY, and ASCII. See the following sections for details.

JSON_query_return_type

JSON_query_return_type::= VARCHAR2[(size[{BYTE|CHAR}]) [TRUNCATE]]|NVARCHAR2[(size) [TRUNCATE]]|JSON|CLOB|BLOB|NCLOB

The JSON_query_return_type clause specifies the supported return types for the RETURNING clause. For example, if you want the return type to be CLOB, specify CLOB.

When specifying a VARCHAR2 or NVARCHAR2 data type elsewhere in SQL, you must specify a size. However, for JSON_QUERY, you can omit the size. In this case, JSON_QUERY returns a character string of type VARCHAR2(4000) or NVARCHAR2(4000) respectively.

Specify the optional TRUNCATE keyword immediately after the VARCHAR2(size) or NVARCHAR2(size) data type to truncate the return value to size characters. For example, if you specify VARCHAR2(10) TRUNCATE and the return value is greater than 10 characters, JSON_VALUE truncates the value to 10 characters.

If the data type is not large enough to hold the return character string, JSON_QUERY returns SQL NULL by default. You can use the JSON_query_on_error_clause to override this default behavior.

{ALLOW | DISALLOW} SCALARS

Use this optional clause as part of the JSON_query_returning_clause clause to allow or disallow scalar JSON values. The default is ALLOW. Use DISALLOW SCALARS if you want to match the JSON standards before IETF RFC8259. These JSON standards allowed only JSON objects and arrays at the top level.

PRETTY

Use this optional keyword as part of the JSON_query_returning_clause clause to format the output, making it more human-readable. For example, the PRETTY keyword pretty-prints the return character string by inserting newline characters and by indenting.

You can use PRETTY only with VARCHAR2, NVARCHAR2, and CLOB return data types.

ASCII

Use this optional keyword to automatically escape any non-ASCII Unicode characters in the return character string by using standard ASCII Unicode escape sequences.

You can use ASCII only with VARCHAR2, NVARCHAR2, and CLOB return data types.

JSON_query_wrapper_clause

WITHOUT [ARRAY] WRAPPER | WITH [{UNCONDITIONAL | CONDITIONAL}] [ARRAY] WRAPPER

Use this optional clause to enclose the values matched by the path expression in an array wrapper (enclosed in square brackets []).

Supported clauses include the following:

WITHOUT WRAPPER: Omits the array wrapper. You can specify this clause only if the path expression matches a single JSON object or JSON array. This is the default.
WITH WRAPPER: Includes the array wrapper.
WITH UNCONDITIONAL WRAPPER: Equivalent to specifying the WITH WRAPPER clause. The UNCONDITIONAL keyword is provided for semantic clarity.
WITH CONDITIONAL WRAPPER: Includes the array wrapper only if the path expression matches a single scalar value or multiple values of any type. If the path expression matches a single JSON object or JSON array, then the array wrapper is omitted.
ARRAY: Optional keyword provided for semantic clarity.

If the function returns a single scalar value, or multiple values of any type, and you do not specify WITH [UNCONDITIONAL | CONDITIONAL] WRAPPER, the function returns SQL NULL by default. You can use the JSON_query_on_error_clause to override this default behavior.

You cannot use an array wrapper with the OMIT QUOTES clause.

JSON_query_quotes_clause

{KEEP|OMIT} QUOTES [ON SCALAR STRING]

Use this optional clause to return a scalar string result with or without quotes. The default is KEEP QUOTES.

For example, if the JSON value returned by JSON_QUERY is a string and the return data type is textual (not a JSON type), JSON_QUERY includes the JSON string-delimiting double-quotation marks in the return value. To omit double-quotation marks, use the OMIT QUOTES clause.

Here is an example using KEEP QUOTES:

SELECT JSON_QUERY('{a:"hello", b:"hi", c:300}', '$.a' KEEP QUOTES ON SCALAR STRING) AS value;

The output is the following:

< "hello" >
1 row found.

Here is an example using OMIT QUOTES:

SELECT JSON_QUERY('{a:"hello", b:"hi", c:300}', '$.a' OMIT QUOTES ON SCALAR STRING) AS value;

The output is the following:

< hello > 
1 row found.

JSON_query_on_error_clause

{EMPTY[ARRAY|OBJECT]|ERROR|NULL} ON ERROR

Use this optional clause to specify the return value in case of an error.

You can specify the following clauses:

EMPTY ON ERROR: Returns an empty JSON array ([]) when an error occurs. Specifying this clause is equivalent to specifying EMPTY ARRAY ON ERROR.
EMPTY ARRAY ON ERROR: Returns an empty JSON array ([]) when an error occurs.
EMPTY OBJECT ON ERROR: Returns an empty JSON object ({}) when an error occurs.
ERROR ON ERROR: Returns the appropriate error when an error occurs.
NULL ON ERROR: Returns SQL NULL when an error occurs. This is the default.

Error conditions include the following:

The first argument (expr) is not well-formed JSON data.
There is no match in the JSON data for the evaluated path expression. (The path expression is the second argument to JSON_QUERY.) You can override the behavior for this type of error by specifying the JSON_query_on_empty_clause.
The data type of the return value is not large enough to hold the return string.
The return value is a single scalar value or multiple match values of any type and you specified DISALLOW SCALARS and you did not specify the WRAPPER clause.

JSON_query_on_empty_clause

{EMPTY[ARRAY|OBJECT]|ERROR|NULL} ON EMPTY

Use this clause to specify the value returned by this function if no match is found when the JSON data is evaluated using the SQL/JSON path expression. This clause allows you to specify a different outcome for this type of error than the outcome specified with the JSON_query_on_error_clause. For example, if there is a no match found error, and you specify both the JSON_query_on_empty_clause and the JSON_query_on_error_clause, JSON_QUERY uses the JSON_query_on_empty_clause. Otherwise, JSON_QUERY uses the JSON_query_on_error_clause.

You can specify the following clauses:

EMPTY ON EMPTY: Returns an empty JSON array ([]) when there is a no match error. Specifying this clause is equivalent to specifying EMPTY ARRAY ON EMPTY.
EMPTY ARRAY ON EMPTY: Returns an empty JSON array ([]) when there is a no match error.
EMPTY OBJECT ON EMPTY: Returns an empty JSON object ({}) when there is a no match error.
ERROR ON EMPTY: Returns the appropriate error when there is a no match error.
NULL ON EMPTY: Returns SQL NULL when there is a no match error.

If you omit this clause, then the JSON_query_on_error_clause determines the value returned when there is a no match error.

JSON_query_on_mismatch_clause

({IGNORE|ERROR|NULL} ON MISMATCH [((MISSING DATA)|(EXTRA DATA)|(TYPE ERROR))])...

Use this optional clause to control mismatch errors. For complete details and usage, see JSON_QUERY Function in the Oracle TimesTen In-Memory Database JSON Developer's Guide.

Here is a summary:

Mismatch errors can be of three types:

MISSING DATA: There is no result for the path expression.
EXTRA DATA: There is more data than expected.
TYPE ERROR: There is a data type mismatch.

For all three types of mismatch, you can specify the following:

IGNORE: Behaves the same as NULL ON MISMATCH. Use the NULL ON MISMATCH clause instead.
ERROR: Returns the appropriate error when a mismatch occurs.
NULL: Returns SQL NULL when a mismatch occurs.

If you specify ERROR ON MISMATCH or NULL ON MISMATCH, the same error handling applies to all three types of mismatch. The default is NULL ON MISMATCH.

This clause allows you to specify a different outcome for this type of error than the outcome specified with the JSON_query_on_error_clause. For example, if there is a mismatch error, and you specify both the JSON_query_on_mismatch_clause and the JSON_query_on_error_clause, JSON_QUERY uses the JSON_query_on_mismatch_clause. Otherwise, JSON_QUERY uses the JSON_query_on_error_clause.

TYPE {STRICT|LAX}

Use this optional clause to determine if JSON syntax should be STRICT or LAX.