TYPE Clause for SQL Functions and Conditions

18.7 TYPE Clause for SQL Functions and Conditions

SQL condition json_exists and functions json_transform, json_value, json_query, and json_table each accept an optional TYPE clause, which specifies whether JSON values are compared strictly with respect to JSON-language type, that is, as if the relevant "only" data-type conversion item methods were applied to the data being compared.

Keyword TYPE is followed, in parentheses, by keyword STRICT or LAX.

TYPE (LAX) specifies lax type-compatibility, the default behavior (same as no TYPE clause), which is that JSON values can be implicitly interpreted (essentially cast) as values of SQL data types for purposes of comparison. This type-casting is explained in Types in Filter-Condition Comparisons.

For example, a comparison such as '$.PONumber?(@ > 20) implicitly interprets a PONumber value of "314" as the number 314 (because it is compared with the number 20). That comparison is true, just as if the expression were '$.PONumber?(@.number() > 20)
TYPE (STRICT) specifies strict type-compatibility, which has the same effect as applying "only" item methods.

For example, '$.PONumber?(@ > 20) behaves as if it were '$.PONumber?(@.numberOnly() > 20). For a PONumber value of "314" the comparison is false, just as if the expression were '$.PONumber?(@.numberOnly() > 20).

Lax type-compatibility is the default behavior for json_transform, json_query, json_value, json_table, and json_exists, but you can change the default behavior for the current database session using initialization parameter JSON_BEHAVIOR, giving it any combination of the following options. Each option can be specified as TRUE (default value) or FALSE, meaning use lax or strict type-compatibility, respectively, as the default behavior for the session.

lax_json_value — Affects only function json_value.
lax_json_query — Affects only function json_value.
lax_json_table — Affects only function json_table. The specified default applies to all columns of the table, regardless of their particular semantics (json_value, json_query, json_exists).
lax_json_exists — Affects both condition json_exists and function json_transform.

By default, the type-compatibility check for each of the affected SQL operators is lax, which corresponds to using a value of TRUE for a given option. Lax type-compatibility means that if the targeted data is not of the required data type then an attempt is made to convert it to that type. Failure to convert causes the operator to follow the current ON ERROR behavior for the operator, which is also the strict type-compatibility behavior of a type mismatch.

For example, if the type to be returned by a given json_value invocation is NUMBER, and the targeted data is the string "42", then lax type-compatibility converts that value to the number 42. Strict compatibility does no such conversion, resulting in the ON ERROR behavior, which by default is FALSE ON ERROR for condition json_exists and NULL ON ERROR for the functions.

Parameter json_behavior specifies only the default type-compatibility behavior. In particular, any use of a TYPE clause determines the actual behavior for a given operator invocation.

Example 18-3 Using Parameter JSON_BEHAVIOR To Provide Lax/Strict Type Compatibility

We set the default type-compatibility for each SQL operator to strict, so no attempt is made to convert targeted data to the required return type. Strict type-compatibility behavior produces the default ON ERROR behavior if the targeted data doesn't match the return type.

ALTER SESSION SET JSON_BEHAVIOR=
  "lax_json_value:false;lax_json_query:false;lax_json_table:false;lax_json_exists:false";

With this json_value query, instead of converting the targeted string, "42", to the number 42 and returning that, NULL is returned.

SELECT json_value('{"a" : "42"}', '$.a' RETURNING NUMBER);

For json_table, the json_behavior-imposed strict default type-compatibility affects all columns. Field a is of the expected type; field b is not. No attempt is made to convert field b's string value "42" to type NUMBER; NULL is returned for column b.

SELECT jt.* FROM json_table('{"a" : 314, "b" : "42"}',
                  '$' COLUMNS (a NUMBER PATH '$.a',
                               b NUMBER PATH '$.b')) jt;

A    B
---- ----
 314

This json_query query looks for an element of array a that's equal to the number 42. With lax type-compatibility it would convert string element "42" to that number and thus return the array [42, 6, "alpha"]. But with strict type-compatibility it returns NULL (nothing — no match).

SELECT json_query('{"a" : [ 314, "42", "alpha" ]}',
                  '$.a?(@ == 42)');

This json_exists query looks for field b with a value that's the number 42 or 314. With lax type-compatibility it would convert string "42" to the number 42, to match 42 in the predicate, and would thus return TRUE, But with strict type-compatibility there's no attempt to convert the input data and it returns FALSE.

SELECT json_exists('{"a" : {"b" : "42"}}',
                   '$.a?(@.b in (42, 314))');

This json_transform query inserts field b into the targeted object, giving it the value of field a plus 1. With lax type-compatibility it would convert a's string value of "42" to the (positive) number 42, to produce b's value of 42+1=43, resulting in the object {"a":"42", "b":43}. But with strict type-compatibility there's no such conversion attempt, so the value of field b is JSON null, resulting in the object {"a":"42", "b":null}.

SELECT json_transform('{"a" : "42"}',
                      INSERT '$.b' = PATH '$?(@.a > 0).a + 1');

The use of TYPE(LAX) in an invocation of any of the SQL operators always overrides the default type-compatibility. So for example, if the default type-compatibility is strict then this query would successfully convert field a's string value to the number 42.

SELECT json_transform('{"a" : "42"}',
                      INSERT '$.b' = PATH '$?(@.a > 0).a + 1'
                      TYPE(LAX));

JSON_TRANSFORM(...)
-------------------
{"a":"42", "b":43}

Note:

Don't confuse these two different ways in which "strict" and "lax" are used in this documentation:

Strict or lax syntax checking. This refers to how textual JSON data is parsed, for example on input.

Strict syntax completely respects the JSON standards. Lax syntax accepts data that deviates in some ways from the standards, such as allowing unquoted field names.

Strict and lax syntax are covered in Strict and Lax JSON Syntax.
Strict or lax type-compatibility. This refers to whether JSON values are compared strictly with respect to their JSON-language type.

Strict type-compatibility requires a value to be of the same JSON-language type family as the expected type — for example, if the expected type is number then a compatible value must be numeric. Lax type-compatibility tries to convert a value to the expected type — for example, the string "42" is lax-compatible with type number.

Strict and lax type compatility are covered in TYPE Clause for SQL Functions and Conditions.