JSON_VALUE

Syntax

JSON_VALUE (expr, JSON_basic_path_expression 
[JSON_passing_clause] [JSON_value_returning_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_VALUE function.

The argument is a SQL expression that returns an instance of a scalar SQL data type. 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_VALUE 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_value_on_error_clause to override this default behavior.

JSON_basic_path_expression

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

TimesTen provides SQL access to JSON data using SQL/JSON path expressions. Use this clause to specify a SQL/JSON path expression that targets a single scalar value. The JSON_VALUE function uses the path expression to evaluate expr and find a JSON scalar value that matches or satisfies the path expression. An error occurs if the JSON scalar value does not match or satisfy the path expression.

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_VALUE 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_VALUE 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_value_returning_clause

RETURNING JSON_value_return_type [ASCII]

Use this optional clause to specify the data type of the return value. If you do not specify a returning clause, the default return type is VARCHAR2(4000). For the supported return data types, see JSON_value_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), preserving or truncating time, allowing or not allowing boolean to number conversion, and ASCII. See the following sections for details.

JSON_value_return_type

JSON_value_return_type ::= VARCHAR2[(size[{BYTE|CHAR}]) [TRUNCATE]] | NVARCHAR2[(size) [TRUNCATE]]|
JSON_value_other_char_types|JSON_value_datetime_types|JSON_value_some_numeric_types|JSON_value_additional_types

JSON_value_other_char_types::= CHAR|NCHAR
JSON_value_datetime_types::= {DATE|TIMESTAMP|TT_DATE|TT_TIMESTAMP} [{TRUNCATE|PRESERVE} TIME]
JSON_value_some_numeric_types::= {NUMBER [precision [,scale])]|INTEGER|BINARY_FLOAT|BINARY_DOUBLE} [{ALLOW|DISALLOW} [BOOLEAN] TO NUMBER [CONVERSION]]
JSON_value_additional_types::= BINARY|VARBINARY|CLOB|NCLOB|BOOLEAN|TT_BIGINT|TT_INTEGER|TT_SMALLINT|TT_TINYINT

The JSON_value_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.

Let's review some of the supported return data types and the clauses and keywords that are specific to these data types.

VARCHAR2 or NVARCHAR2:

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

You can 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_VALUE returns SQL NULL by default. You can use the JSON_value_on_error_clause to override this default behavior.
JSON_value_datetime_types:

In your JSON data, you can use a string that represents an ISO 8601 date-with-time value. This indicates it can have a time component. By default, when you use the RETURNING clause with JSON_value_datetime_types, and there is a time component, JSON_VALUE truncates the time component. For the DATE, TIMESTAMP, TT_DATE, and TT_TIMESTAMP return data types, you can use the RETURNING clause with the PRESERVE TIME clause to override this default truncating behavior and preserve the time component. (Using RETURNING JSON_value_datetime_types TRUNCATE TIME has the same effect as RETURNING JSON_value_datetime_types.)
JSON_value_numeric_types: For the NUMBER, INTEGER, BINARY_FLOAT, and BINARY_DOUBLE return data types, you can optionally specify the {ALLOW|DISALLOW} [BOOLEAN] TO NUMBER [CONVERSION] clause. You can use this clause when the JSON_VALUE function evaluates a path expression to a JSON Boolean. The default is DISALLOW [BOOLEAN] TO NUMBER [CONVERSION].

For details, see Using JSON_VALUE with a Boolean JSON Value in the Oracle TimesTen In-Memory Database JSON Developer's Guide.

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_value_on_error_clause

{NULL | ERROR | DEFAULT literal} ON ERROR

You can specify one of the following clauses or take the default:

NULL ON ERROR: Returns SQL NULL when an error occurs. This is the default.
ERROR ON ERROR: Returns the appropriate error when an error occurs.
DEFAULT literal ON ERROR: Returns literal when an error occurs. The data type of literal must match the data type of the value returned by this function.

Error conditions include the following:

The first argument (expr) is not well-formed JSON data.
There is a non-scalar value found when the JSON data is evaluated using the SQL/JSON path expression.
There is no match when the JSON data is evaluated using the SQL/JSON path expression. (The path expression is the second argument to JSON_VALUE.) You can override the behavior for this type of error by specifying the JSON_value_on_empty_clause.
The data type of the return value is not large enough to hold the return value.

JSON_value_on_empty_clause

{NULL | ERROR | DEFAULT literal} 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_value_on_error_clause. For example, if there is a no match found error, and you specify both the JSON_value_on_empty_clause and the JSON_value_on_error_clause, JSON_VALUE uses the JSON_value_on_empty_clause. Otherwise, JSON_VALUE uses the JSON_value_on_error_clause.

You can specify one of the following clauses or take the default:

NULL ON EMPTY: Returns SQL NULL when there is a no match error. This is the default.
ERROR ON EMPTY: Returns the appropriate error when there is a no match error.
DEFAULT literal ON EMPTY: Returns literal when there is a no match error. The data type of literal must match the data type of the value returned by this function.

JSON_value_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_VALUE 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_value_on_error_clause. For example, if there is a mismatch error, and you specify both the JSON_value_on_mismatch_clause and the JSON_value_on_error_clause, JSON_VALUE uses the JSON_value_on_mismatch_clause. Otherwise, JSON_VALUE uses the JSON_value_on_error_clause.

TYPE {STRICT|LAX}

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