Operator Expressions
Operators perform a specific operation on the input values or expressions.
Arithmetic operators are used to perform operations on numeric values.
Operator | Purpose | Example |
- | A unary operator that denotes a negative value | WHERE field_name = -1 |
+, - | As binary operators, they add or subtract | WHERE field_name + 1 > 2 |
* | Multiply | WHERE field_name * 2 > 2 |
/ | Divide | WHERE field_name / 2 > 1 |
% | Modulo | WHERE field_name % 2 > 1 |
abs(x) | Absolute value | WHERE abs(field_name) = 1 |
ceil(x) | Next whole number | WHERE ceil(field_name) > 1 |
floor(x) | Previous whole number | WHERE floor(field_name) > 1 |
Used to perform operations on string scalar types.
Operator | Purpose | Example |
|| | String concatenation | WHERE field_name || 'world' = 'hello world' |
concat(str, ...) | String concatenation | WHERE concat(field_name, 'world') = 'hello world' |
contains(str, substr) | Returns true if substr is contained in str, otherwise returns false | WHERE contains(field_name, 'hello') |
starts_with(str, substr) | Returns true if str starts with substr, otherwise returns false | WHERE starts_with(field_name, 'hello') |
ends_with(str, substr) | Returns true if str ends with substr, otherwise returns false | WHERE ends_with(field_name, 'world') |
Used to interact with scalar types.
Operator | Purpose | Example |
is_boolean(x) | Boolean type test | WHERE is_boolean(field_name) |
is_number(x) | Float, Int, or UInt type test | WHERE is_number(field_name) |
is_string(x) | String type test | WHERE is_string(field_name) |
type(x) | Returns a string name representing the type | WHERE type(field_name) = 'string' |
Following is the mapping between data types and scalar types; all of which are case-sensitive:
REGISTER | MAP | ATTACHMENT |
| object | object |
The following table provides an overview of interactions with array. Arrays can be either scalar values in a REGISTER or an input via an argument.
Operator | Purpose | Example |
array_contains(array, value) | Returns true if the array contains the value, otherwise returns false | WHERE array_contains(field_name, 'blue') WHERE array_contains(:your_array, field_name) |
array_contains_null(array) | Returns true if the array contains a NULL value, otherwise returns false | WHERE array_contains_null(field_name) |
array_length(array) | Returns the length of the array. | WHERE array_length(field_name) > 0 |
The following table provides the collection operators for comparing if a given value is equal to any of the values in a list:
Operator | Purpose | Example |
IN (x, y, ...) | Membership test | WHERE department IN ('HR', 'Sales') |
NOT IN (x, y, ...) | Non-membership test | WHERE department NOT IN ('HR', 'Sales') |
Array and object literals are currently not supported and cannot be used inline.
The following syntax is invalid:
Instead, use arguments to pass in your array or object.
The comparison operators fall into one of two sub-categories:
- Missing value comparisons
- Regular value comparisons
DQL has two ways of representing missing information in an object:
- The presence of the field with a NULL for its value (as in SQL)
- The absence of the field (which JSON permits)
The following table provides operators for comparing if a given value is equal to any of the values in a list:
NULL and UNKNOWN are synonym keywords and provide the same behavior.
Operator | Purpose | Example |
IS NULL | Returns true if the value is NULL, otherwise returns false | WHERE field_name IS NULL |
IS NOT NULL | Returns true if the value is not NULL, otherwise returns false | WHERE field_name IS NOT NULL |
IS MISSING | Returns true if the field is missing in a given document, otherwise returns false | WHERE field_name IS MISSING |
IS NOT MISSING | Returns true if the field is not missing in a given document, otherwise returns false | WHERE field_name IS NOT MISSING |
IS UNKNOWN | Returns true if the value is NULL, otherwise returns false | WHERE field_name IS UNKNOWN |
IS NOT UNKNOWN | Returns true if the value is not NULL, otherwise returns false | WHERE field_name IS NOT UNKNOWN |
= | Equality test | WHERE field_name = 100 |
== | Equality test | WHERE field_name == 100 |
!= | Inequality test | WHERE field_name != 100 |
<> | Inequality test | WHERE field_name <> 100 |
< | Less than | WHERE field_name < 100 |
> | Greater than | WHERE field_name > 100 |
<= | Less than or equal to | WHERE field_name <= 100 |
>= | Greater than or equal to | WHERE field_name >= 100 |
In DQL, NULL represents missing or unknown data. It's not a value in the way that 1 or 'text' are values. Any comparison operation that includes NULL will result in NULL.
Equation | Result |
NULL = NULL | NULL |
NULL <> NULL | NULL |
1 = NULL | NULL |
1 > NULL | NULL |
Logical operators perform logical NOT, AND, and OR operations over Boolean values (TRUE and FALSE) plus NULL and MISSING. MISSING is treated as NULL when performing a logical operation.
Operator | Purpose | Example |
NOT | Returns true if the following condition is false, otherwise returns false. | WHERE NOT field_name = true |
AND | Returns true if both branches are true, otherwise returns false | WHERE field_1 = true AND field_2 = false |
OR | Returns true if one branch is true; otherwise, returns false | WHERE field_1 = true OR field_2 = false |
Value | Result |
True | False |
False | True |
NULL | NULL |
| True | NULL | False |
True | True | NULL | False |
NULL | NULL | NULL | False |
False | False | False | False |
| True | NULL | False |
True | True | True | True |
NULL | True | NULL | NULL |
False | True | NULL | False |