Use of Operators with JSON Data
The use of arithmetic, Boolean, and comparison operators against JSON values may differ from that of standard SQL.
Arithmetic Expressions
Unary + and Unary –
• Value must be numeric or an error is returned (in both lax and strict modes)
• The operator applies to each element of a sequence
Binary +, -, *, /, %
Requires singleton numeric operands. Any other operand, including sequences, generate errors (in both lax and strict modes).
Booleans and Boolean Operators
Unlike SQL, JSON has three Boolean states: TRUE, FALSE, and JSON UNKNOWN. JSON UNKNOWN is generally a result of an error (for example, invalid data type comparison).
Note: You cannot simply test for a true or false condition, which is the natural inclination for Boolean. For example, to compare @.age for all rows against the number 18, you cannot simply test for ‘@.age <= 18’ and ‘@.age > 18’; you must account for rows where ‘(@.age > 18)’ is unknown.
TRUE and FALSE Boolean conditions are tested using ==. Unlike SQL, Booleans cannot be tested “standalone.”
Example: Assume @.skilled is a Boolean value.
? (@.skilled == TRUE) is a valid statement.
? (@.skilled) is an invalid statement.
UNKNOWN Boolean conditions are testing using the IS UNKNOWN operator.
Examples
: ? (@.skilled IS UNKNOWN) is a valid statement.
? (@.skilled == unknown) is an invalid statement.
&&, ||, and ! are the AND, OR, and NOT operators and work against Boolean operands.
If the initial tested operand is enough to return a result, the other operand does not need to be evaluated. (For example, if operand 1 is FALSE and the operator is &&, then operand 2 can be ignored.)
&& truth table:
Note: Operators can be checked in any order, so FALSE && error may still return an error.
|| truth table:
! truth table:
!TRUE is FALSE
!FALSE is TRUE
!UNKNOWN is UNKNOWN
Comparison Operators
Note the following when using comparison operators:
• Two JSON items are comparable if one of them is a JSON null and the other is a JSON scalar, or if both are an identical scalar type (string, numeric, or Boolean). Arrays and JSON objects cannot be directly compared, although lax mode unwinds arrays and turns them into sequences.
• Equality operators have the same precedence as inequality operators.
• Comparisons require identical data types (no automatic casting).
• JSON NULLS are not greater than or less than anything.
• JSON NULLS are not equal to SQL nulls when compared directly using PASSING clause variables. The JSON NULL is considered a unique data type and is therefore not equal to null values of other data types (a null integer, for example).
• Comparisons allow sequences as operands. Every item in each sequence is compared against every item in the other sequence. When comparing sequences, the following rules apply:
– If a comparison results in a JSON UNKNOWN (for example, incomparable operands), then the comparison immediately returns JSON UNKNOWN as the final result.
– In lax mode, if a pair of operands returns a TRUE comparison, then the result is TRUE. This occurs even if there is an invalid “UNKNOWN” comparison that has not been detected yet. If all pairs of operands are compared and return FALSE, then the result is FALSE.
– In strict mode, every combination of operands is compared to check for JSON UNKNOWNs. If any comparison returns JSON UNKNOWN, then the result is JSON UNKNOWN. If all the comparisons return FALSE, then the result is FALSE. If all the comparisons return TRUE or FALSE and one or more return TRUE, then the result is TRUE.
• Operators can be checked in any order, so FALSE && error may still return an error.
Last modified date: 11/28/2023