Clauses Used in SQL/JSON Functions and Conditions

TimesTen SQL/JSON functions and conditions accept optional clauses that control variable binding, return types and formats, array wrapping, error handling, missing-field handling, type-mismatch handling, and type compatibility. These clauses are shared by more than one SQL/JSON function or condition.

TimesTen also implicitly validates textual input when it is converted to the JSON data type. The JSON data type constructor is therefore the entry point for textual JSON validation; it is not an additional SQL/JSON clause.

Table 3-5 summarizes where you can use each clause. A JSON_TABLE column can have JSON_EXISTS, JSON_QUERY, or JSON_VALUE semantics. For JSON_TABLE, use only the clauses and placements that its row or column grammar explicitly permits.

Table 3-5 Applicability of Shared SQL/JSON Clauses

Clause	TimesTen SQL/JSON Functions and Conditions
`PASSING`	`JSON_EXISTS`, `JSON_QUERY`, and `JSON_VALUE`
`RETURNING`	`JSON_QUERY`, `JSON_SERIALIZE`, and `JSON_VALUE`; a `JSON_TABLE` column declares the corresponding SQL data type directly
`WRAPPER`	`JSON_QUERY` and `JSON_TABLE` columns with `JSON_QUERY` semantics
`ON ERROR`	`JSON_EQUAL`, `JSON_EXISTS`, `JSON_QUERY`, `JSON_SCALAR`, `JSON_SERIALIZE`, `JSON_TABLE`, and `JSON_VALUE`
`ON EMPTY`	`JSON_EXISTS`, `JSON_QUERY`, `JSON_TABLE`, and `JSON_VALUE`
`ON MISMATCH`	`JSON_QUERY`, `JSON_TABLE`, and `JSON_VALUE`
`ON NULL`	`JSON_SCALAR`
`TYPE`	`JSON_EXISTS`, `JSON_QUERY`, and `JSON_VALUE`; `JSON_TABLE` columns with `JSON_VALUE` semantics

See Functions and Search Conditions in Oracle TimesTen In-Memory Database SQL Reference for the function-specific syntax for each function or condition. The following topics explain the shared behavior and defaults.

Topics:

PASSING Clause

The JSON_EXISTS condition and the JSON_QUERY and JSON_VALUE functions accept an optional PASSING clause. The clause binds SQL values to SQL/JSON variables that you can use in a SQL/JSON path expression.

Use SQL/JSON variables when a statement is repeated and only the comparison values change. Keeping the path expression unchanged can avoid unnecessary statement recompilation.

The clause has this form:

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

Each binding has:

A SQL expression that TimesTen evaluates.
The keyword AS.
An identifier that names the SQL/JSON variable.

Reference the variable in a path expression with a dollar sign ($) followed immediately by the variable name. For example, the binding 1500 AS "min" defines the SQL/JSON variable $min.

TimesTen supports BINARY_DOUBLE, DATE, NUMBER, TIMESTAMP, and VARCHAR2 values in these bindings. The expression must be a constant or bind value. It cannot be a column reference.

A quoted identifier preserves its case. For example, AS "min" defines the variable min. An unquoted identifier is converted to uppercase. The quotes are not part of the SQL/JSON variable name: use $min, not $"min".

SQL/JSON variable names must:

Contain only ASCII letters, digits, or the underscore character (_).
Begin with a letter or an underscore, not a digit.

If you also specify TYPE (STRICT), TimesTen compares the targeted JSON value strictly with respect to the SQL data type of the bound variable. For example, a numeric variable is compared only with JSON numeric values; a JSON string that contains numeric characters is not converted for the comparison.

See also:

Example 3-5 Using PASSING with JSON_EXISTS

This example binds the SQL number 1500 to the SQL/JSON variable $min. The path expression finds PONumber because its value is greater than $min.

SELECT JSON_EXISTS(po_document, '$.PONumber?(@ > $min)'
                   PASSING 1500 AS "min")
  FROM j_purchaseorder;

The query returns this output, given the JSON data inserted into the j_purchaseorder table in Example 2-2.

< TRUE >
< TRUE >
2 rows found.

RETURNING Clause

The JSON_QUERY, JSON_SERIALIZE, and JSON_VALUE functions accept an optional RETURNING clause. The clause specifies the SQL data type and, where applicable, the format of the value returned by the function.

The supported return types and defaults depend on the function.

Table 3-6 SQL/JSON Return Types and Defaults

SQL/JSON Function	Return Types	Default
`JSON_VALUE`	`BINARY`, `BINARY_DOUBLE`, `BINARY_FLOAT`, `BOOLEAN`, `CHAR`, `CLOB`, `DATE`, `INTEGER`, `NCHAR`, `NCLOB`, `NUMBER`, `NVARCHAR2`, `TIMESTAMP`, `TT_BIGINT`, `TT_DATE`, `TT_INTEGER`, `TT_SMALLINT`, `TT_TIMESTAMP`, `TT_TINYINT`, `VARBINARY`, or `VARCHAR2`	`VARCHAR2(4000)`
`JSON_QUERY`	`BLOB`, `CLOB`, `JSON`, `NCLOB`, `NVARCHAR2`, or `VARCHAR2`	`JSON` when the input is `JSON`; otherwise `VARCHAR2(4000)`
`JSON_SERIALIZE`	`BLOB`, `CLOB`, `NCLOB`, `NVARCHAR2`, or `VARCHAR2`	`VARCHAR2(4000)`

For the complete function-specific list, see the JSON_VALUE, JSON_QUERY, or JSON_SERIALIZE in Oracle TimesTen In-Memory Database SQL Reference.

The following options refine the return behavior.

For JSON_VALUE and JSON_QUERY, you can specify TRUNCATE with a sized VARCHAR2 or NVARCHAR2 return type. For JSON_SERIALIZE, TRUNCATE shortens output that is too large for the return buffer. Truncation takes precedence over the error handler for an oversize result.
For a JSON_VALUE numeric return type, ALLOW BOOLEAN TO NUMBER CONVERSION maps JSON true to 1 and JSON false to 0. The default is to disallow this conversion.
For a JSON_VALUE return type of DATE, TIMESTAMP, TT_DATE, or TT_TIMESTAMP, the default is TRUNCATE TIME. Use PRESERVE TIME to retain the time component of an ISO 8601 date-with-time value.
For JSON_QUERY, ALLOW SCALARS permits a scalar JSON value at the top level and is the default. DISALLOW SCALARS permits only an object or array.
For JSON_QUERY, PRETTY inserts line breaks and indentation, and ASCII escapes non-ASCII Unicode characters. Both options require a return type of VARCHAR2, NVARCHAR2, or CLOB.
For JSON_SERIALIZE, PRETTY inserts line breaks and indentation, and ASCII escapes non-ASCII Unicode characters.
For JSON_VALUE, ASCII escapes non-ASCII Unicode characters and is supported only with VARCHAR2, NVARCHAR2, and CLOB.

The mapping of a JSON Boolean depends on the requested SQL return type:

Table 3-7 JSON VALUE Mapping for JSON Boolean Values

Return Type	Result for JSON true or false
Default `VARCHAR2(4000)`	Lowercase SQL character string `'true'` or `'false'`
`BOOLEAN`	SQL Boolean result; because TimesTen has no native SQL `BOOLEAN` data type, it maps the result to the `VARCHAR2(7)` value `TRUE` or `FALSE`
`NUMBER` with `ALLOW BOLEAN TO NUMBER CONVERSION`	`1` or `0`

JSON_SERIALIZE returns textual JSON regardless of the SQL return type. A BLOB result contains UTF-8 encoded JSON text.

See also:

Example 3-6 Returning a JSON Boolean as a NUMBER

This example returns a JSON Boolean value as a SQL NUMBER. The conversion option maps JSON true to 1.

SELECT JSON_VALUE(po_document, '$.AllowPartialShipment'
                  RETURNING NUMBER
                  ALLOW BOOLEAN TO NUMBER CONVERSION)
  FROM j_purchaseorder;

The query returns this output, given the JSON data inserted into the j_purchaseorder table in Example 2-2.

< <NULL> >
< 1 >
2 rows found.

WRAPPER Clause

The JSON_QUERY function and JSON_TABLE columns with JSON_QUERY semantics accept an optional wrapper clause. The clause controls whether TimesTen encloses values that match the path expression in a JSON array.

The clause has these forms:

WITHOUT [ARRAY] WRAPPER

WITH [UNCONDITIONAL] [ARRAY] WRAPPER

WITH CONDITIONAL [ARRAY] WRAPPER

WITHOUT WRAPPER is the default.

WITH WRAPPER always encloses the matched values in an array. WITH UNCONDITIONAL WRAPPER has the same meaning.
WITHOUT WRAPPER returns the matched value without adding an outer array. It produces an error if the path expression matches multiple values. It also produces an error for a single scalar value when the RETURNING clause specifies DISALLOW SCALARS.
WITH CONDITIONAL WRAPPER adds a wrapper for multiple values. For a single match, it omits the wrapper when the value can be returned without one and adds the wrapper when a scalar result is disallowed.
ARRAY is optional and does not change the behavior.

Table 3-8 summarizes wrapper-clause results.

Table 3-8 Wrapper-Clause Results

Values Matched by the Path Expression	`WITH WRAPPER`	`WITHOUT WRAPPER`	`WITH CONDITIONAL WRAPPER`
One object	Array containing the object	The object	The object
One array	Array containing the matched array	The matched array	The matched array
One scalar, scalars allowed	Array containing the scalar	The scalar	The scalar
One scalar, scalars disallowed	Array containing the scalar	Error	Array containing the scalar
Multiple values	Array containing all matched values	Error	Array containing all matched values
No values	Determined by `ON EMPTY`	Determined by `ON EMPTY`	Determined by `ON EMPTY`

The order of multiple values in a wrapper array is not guaranteed.

ON EMPTY takes precedence over the wrapper clause. For example, EMPTY ARRAY ON EMPTY returns [] when the path expression has no match.

Note:

You cannot combine an array wrapper with OMIT QUOTES.

See also:

Example 3-7 Wrapping Multiple Values

This example encloses the matched values in an array.

SELECT JSON_QUERY(po_document, '$.LineItems[*].Part.UPCCode'
                  WITH ARRAY WRAPPER)
  FROM j_purchaseorder;

The query returns this output, given the JSON data inserted into the j_purchaseorder table in Example 2-2.

< [794043523625,717951001931,13023025295] >
< [13131092899,85391628927] >
2 rows found.

ON ERROR Clause

TimesTen SQL/JSON functions and conditions accept an optional ON ERROR clause that controls how a runtime error is handled. The supported forms and the default depend on the function or condition.

Depending on the function or condition, ON ERROR handles malformed JSON input, conversion and size errors, cardinality or scalar-result errors, and other errors that occur at runtime. For a function or condition that uses a SQL/JSON path expression, ON ERROR also handles errors raised while a syntactically correct path expression is evaluated. It does not handle a compile-time syntax error in a path expression.

Table 3-9 ON ERROR Behavior and Defaults

Function or Condition	Supported Behavior	Default
`JSON_EQUAL`	`ERROR`, `TRUE`, `FALSE`, or `NULL ON ERROR`	`FALSE ON ERROR`
`JSON_EXISTS`	`ERROR`, `TRUE`, or `FALSE ON ERROR`	`FALSE ON ERROR`
`JSON_VALUE`	`ERROR`, `NULL`, or `DEFAULT literal ON ERROR`	`NULL ON ERROR`
`JSON_QUERY`	`ERROR`, `NULL`, `EMPTY ARRAY`, or `EMPTY OBJECT ON ERROR`	`NULL ON ERROR`
`JSON_TABLE`	`ERROR` or `NULL ON ERROR` at row level; a column can use the handler for its `JSON_EXISTS`, `JSON_QUERY`, or `JSON_VALUE` semantics	`NULL ON ERROR` at both levels
`JSON_SCALAR`	`ERROR` or `NULL ON ERROR`	`ERROR ON ERROR`
`JSON_SERIALIZE`	`ERROR`, `NULL`, `EMPTY ARRAY`, or `EMPTY OBJECT ON ERROR`	`ERROR ON ERROR`

The forms have these general effects:

ERROR ON ERROR raises the error.
NULL ON ERROR returns SQL NULL.
TRUE ON ERROR or FALSE ON ERROR returns the corresponding condition value.
EMPTY ARRAY ON ERROR returns []. EMPTY ON ERROR is an abbreviation for EMPTY ARRAY ON ERROR where it is supported.
EMPTY OBJECT ON ERROR returns {}.
DEFAULT literal ON ERROR returns a constant value that is compatible with the return data type.

JSON_TABLE has row-level and column-level error handling. A column-level handler overrides the row-level handler for that column.

An explicit ON EMPTY clause handles a missing target before ON ERROR. An explicit ON MISMATCH clause handles a conversion mismatch before ON ERROR. If the specialized clause is omitted, the applicable ON ERROR behavior handles the case.

See also:

Example 3-8 Returning a Default Value on Error

This example uses JSON_VALUE where the path expression matches values with greater precision than the one specified in the RETURNING clause. The matches produce an error and TimesTen returns the specified default.

SELECT JSON_VALUE(po_document, '$.PONumber'
                  RETURNING NUMBER(3)
                  DEFAULT 0 ON ERROR)
  FROM j_purchaseorder;

The query returns this output, given the JSON data inserted into the j_purchaseorder table in Example 2-2.

< 0 >
< 0 >
2 rows found.

ON EMPTY Clause

The JSON_EXISTS condition and the JSON_QUERY, JSON_TABLE, and JSON_VALUE functions accept an optional ON EMPTY clause. The clause controls the result when the field or value targeted by a path expression is absent.

Use ON EMPTY when a missing field should be handled differently from other runtime errors. If you omit ON EMPTY, the applicable ON ERROR behavior also handles the no-match case.

Table 3-10 ON EMPTY Behavior and Defaults

Function or Condition	Supported Behavior	Default When neither ON EMPTY nor ON ERROR Is Explicit
`JSON_EXISTS`	`ERROR`, `TRUE`, or `FALSE ON EMPTY`	`FALSE ON EMPTY`
`JSON_VALUE`	`ERROR`, `NULL`, or `DEFAULT literal ON EMPTY`	`NULL ON EMPTY`
`JSON_QUERY`	`ERROR`, `NULL`, `EMPTY ARRAY`, or `EMPTY OBJECT ON EMPTY`	`NULL ON EMPTY`
`JSON_TABLE`	`ERROR` or `NULL ON EMPTY` at row level; a column can use the handler for its semantics	`NULL ON EMPTY`

When both clauses are present, ON EMPTY takes precedence over ON ERROR for a no-match case. For JSON_VALUE, JSON_QUERY, and applicable JSON_TABLE column semantics, a common pattern is:

ERROR ON ERROR
NULL ON EMPTY

This pattern permits an optional field to be absent but raises an error for malformed input, multiple matches where one value is required, and other runtime evaluation errors.

For JSON_TABLE, a column-level handler overrides the row-level handler for that column.

Note:

A field that is present with the JSON value null is not empty. For example, JSON_EXISTS returns true for a present field whose value is JSON null. JSON_VALUE returns SQL NULL for a targeted JSON null.

See also:

Example 3-9 Returning a Default for a Missing Field

This example targets a field that is not present in all targeted JSON documents. ON EMPTY returns the specified text, while ERROR ON ERROR remains in effect for other errors.

SELECT JSON_VALUE(po_document, '$.AllowPartialShipment'
                  ERROR ON ERROR
                  DEFAULT 'missing' ON EMPTY)
  FROM j_purchaseorder;

The query returns this output, given the JSON data inserted into the j_purchaseorder table in Example 2-2.

< missing >
< true >
2 rows found.

Use NULL ON EMPTY for an Index on JSON_VALUE

For an index based on a JSON_VALUE expression, use ERROR ON ERROR to reject data that cannot produce the required single scalar index value. Add NULL ON EMPTY if rows that omit the indexed field should still be accepted.

CREATE UNIQUE INDEX po_ref_idx1 ON j_purchaseorder 
  (JSON_VALUE(po_document, '$.Reference' RETURNING VARCHAR2(200)
              ERROR ON ERROR
              NULL ON EMPTY));

ON MISMATCH Clause

The JSON_QUERY, JSON_TABLE, and JSON_VALUE functions accept an optional ON MISMATCH clause. The clause controls the result when a path expression matches JSON data but the matched data cannot be converted to the requested SQL return type.

For example, a mismatch occurs when the JSON string "cat" is returned as SQL NUMBER.

The clause has these forms:

NULL ON MISMATCH

ERROR ON MISMATCH

IGNORE ON MISMATCH

TimesTen SQL syntax also accepts IGNORE ON MISMATCH and lets you qualify a mismatch handler with a mismatch kind:

handler ON MISMATCH 
  (MISSING DATA | EXTRA DATA | TYPE ERROR)

NULL ON MISMATCH returns SQL NULL.
ERROR ON MISMATCH raises an error.
IGNORE ON MISMATCH is retained for compatibility. Prefer NULL ON MISMATCH when you want SQL NULL.

The mismatch qualifiers are MISSING DATA, EXTRA DATA, and TYPE ERROR. Their applicability depends on the requested SQL return type and mapping. For scalar projections, use an unqualified NULL ON MISMATCH or ERROR ON MISMATCH unless the individual function topic documents a qualified form for your mapping.

Each qualified ON MISMATCH clause names one mismatch kind. Repeat the clause to specify separate handlers for additional kinds.

When neither ON MISMATCH nor ON ERROR is explicit, the effective default is NULL ON MISMATCH. If you omit ON MISMATCH and specify ON ERROR, the ON ERROR behavior also handles a mismatch.

ON EMPTY and ON MISMATCH handle different situations. ON EMPTY handles a path that has no match. ON MISMATCH handles data that matched but cannot be returned in the requested type or shape.

For compatibility between SQL/JSON path-end item methods and SQL return types, see SQL/JSON Path Expressions.

See also:

Example 3-10 Returning NULL for a Type Mismatch

This example targets the value of field Reference and requests a SQL NUMBER. The explicit mismatch handler returns SQL NULL.

SELECT JSON_VALUE(po_document, '$.Reference'
                  RETURNING NUMBER
                  ERROR ON ERROR
                  NULL ON MISMATCH)
  FROM j_purchaseorder;

The query returns this output, given the JSON data inserted into the j_purchaseorder table in Example 2-2.

< <NULL> >
< <NULL> >
2 rows found.

TYPE Clause

The JSON_EXISTS condition and the JSON_QUERY and JSON_VALUE functions accept an optional TYPE clause. A JSON_TABLE column with JSON_VALUE semantics can also use the clause after its PATH clause. The clause controls whether TimesTen uses lax or strict type compatibility when it compares or returns JSON values.

The clause has these forms:

TYPE (LAX) 

TYPE (STRICT)

TYPE (LAX) is the default. TimesTen can implicitly convert a targeted JSON value to the required comparison or return type. For example, the JSON string "42" can be converted to the number 42 for a numeric comparison.

TYPE (STRICT) prevents conversion between JSON type families. A value must already belong to the required JSON type family. It has the same effect as applying the relevant “only” path-expression item method. For example, strict numeric comparison behaves as if numberOnly() were applied.

If strict compatibility rejects a value, the applicable ON MISMATCH or ON ERROR behavior determines the result. If you combine PASSING with TYPE (STRICT), the SQL data type of the bound variable determines the required type for the comparison.

Note:

Do not confuse type compatibility with JSON syntax checking. Strict or lax JSON syntax controls how textual JSON input is parsed. TYPE (STRICT) and TYPE (LAX) control whether values can be converted between JSON type families during a comparison or projection. See Differentiating Syntax from Type Compatibility.

See also:

Example 3-11 Comparing Lax and Strict Type Compatibility

The first query uses lax compatibility. TimesTen converts the JSON string "1600" to a number for comparison with 1500.

SELECT JSON_EXISTS('{"PONumber":"1600"}', '$.PONumber?(@ > 1500)'
                   TYPE (LAX));

The query returns:

< TRUE > 
1 row found.

The second query uses strict compatibility. The JSON string is not a JSON number, so it does not match the numeric comparison.

SELECT JSON_EXISTS('{"PONumber":"1600"}', '$.PONumber?(@ > 1500)' 
                   TYPE (STRICT));

The query returns:

< FALSE > 
1 row found.