Operators
The following operators can be used in SELECT and WHERE clauses.
AND operator
Returns a Boolean result. Performs a logical AND operation. Returns
true if left and right operands are true. Otherwise, returns false.
Boolean operands or case insensitive "true" or "false" string
operands are required.
Syntax:
.expression AND
expression
| Left operand | Right operand | Output |
|---|---|---|
Boolean |
Boolean |
Boolean. True if both operands are true. Otherwise,
false. |
String/Boolean |
String/Boolean |
If all strings are "true" or "false" (case insensitive), they are
converted to Boolean and processed normally as
|
| Other value | Other value | Undefined. |
OR operator
Returns a Boolean result. Performs a logical OR operation. Returns
true if either the left or the right operands are true. Otherwise, returns false.
Boolean operands or case insensitive "true" or "false" string
operands are required.
Syntax:
.expression OR
expression
| Left operand | Right operand | Output |
|---|---|---|
Boolean |
Boolean |
Boolean. True if either operand is true. Otherwise,
false. |
String/Boolean |
String/Boolean |
If all strings are "true" or "false" (case insensitive), they are
converted to Booleans and processed normally as
|
| Other value | Other value | Undefined. |
NOT operator
Returns a Boolean result. Performs a logical NOT operation. Returns
true if the operand is false. Otherwise, returns true. A Boolean
operand or case insensitive "true" or "false" string operand is required.
Syntax:
NOT .expression
| Operand | Output |
|---|---|
Boolean |
Boolean. True if operand is false. Otherwise,
true. |
String |
If string is "true" or "false" (case insensitive), it is converted to the corresponding Boolean value, and the opposite value is returned. |
| Other value | Undefined. |
IN operator
Returns a Boolean result. You can use the IN operator in a WHERE
clause to check if a value matches any value in an array. It returns true if the
match is found, and false otherwise.
Syntax:
.expression IN
expression
| Left operand | Right operand | Output |
|---|---|---|
Int/Decimal/String/Array/Object |
Array |
True if the Integer/Decimal/String/Array/Object element is found
in the array. Otherwise, false. |
Example:
SQL: "select * from 'a/b' where 3 in arr" JSON: {"arr":[1, 2, 3, "three", 5.7, null]}
In this example, the condition clause where 3 in arr will evaluate to
true because 3 is present in the array named arr. Hence in the SQL
statement, select * from 'a/b' will execute. This example also shows
that the array can be heterogeneous.
EXISTS operator
Returns a Boolean result. You can use the EXISTS operator in a
conditional clause to test for the existence of elements in a subquery. It returns
true if the subquery returns one or more elements and false if the subquery returns
no elements.
Syntax:
.expression
Example:
SQL: "select * from 'a/b' where exists (select * from arr as a where a = 3)" JSON: {"arr":[1, 2, 3]}
In this example, the condition clause where exists (select * from arr as a
where a = 3) will evaluate to true because 3 is present in the array
named arr. Hence in the SQL statement, select * from 'a/b'
will execute.
Example:
SQL: select * from 'a/b' where exists (select * from e as e where foo = 2) JSON: {"foo":4,"bar":5,"e":[{"foo":1},{"foo":2}]}
In this example, the condition clause where exists (select * from e as e
where foo = 2) will evaluate to true because the array e
within the JSON object contains the object {"foo":2}. Hence in the SQL
statement, select * from 'a/b' will execute.
> operator
Returns a Boolean result. Returns true if the left operand is greater
than the right operand. Both operands are converted to a Decimal, and
then compared.
Syntax:
expression >
expression
| Left operand | Right operand | Output |
|---|---|---|
Int/ |