Operator Expressions

Operators perform a specific operation on the input values or expressions.
Arithmetic Operators
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
﻿
String Operators
Performs various 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')
byte_length(str)
Returns the length of str in bytes. Strings in Ditto are encoded in UTF-8, so a character can take up to 4 bytes.
WHERE byte_length(field_name) > 4
char_length(str) character_length(str)
Returns the number of characters in str.
WHERE char_length(field_name) > 4
﻿
Scalar Type Operators
Indicates which scalar type to interact with: 
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
null
boolean
number
binary
string
array
object
object
object
﻿
Array Operators
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
﻿
Collection Operators
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
Array and object literals are currently not supported and cannot be used inline.
The following syntax is invalid:
SELECT * FROM your_collection_name WHERE field1 = [0,1]
Instead, use arguments to pass in your array or object.
DQL
SELECT * FROM your_collection_name WHERE field1 = :your_array
SELECT * FROM your_collection_name WHERE field1 = :your_array
﻿
﻿
Comparison Operators
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
Comparison Operations with NULL
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
﻿
Conditional Operators
Conditional Operators allow you to more easily express conditional logic within your DQL queries.
Operator
Purpose
Example
coalesce(v1, v2, ...)
Returns the first non-null value passed to the function
WHERE coalesce(field_name_1, field_name_2, field_name_3) = 'foo'
decode(input, comp1, res1, [comp2, res2...] [, default value])
Compares the input against each compparameter, and if they match type and value, returns the correspondingres value. 
If no matches are found, the default value is returned (which defaults to NULL).
WHERE decode(field_name, 'foo', 'I found foo', 'bar', 'I found bar', 'I found nothing') = 'foo'
nvl(input, default)
Returns the input if input evaluates to NOT NULL, otherwise returns default.
WHERE nvl(field_name, 0) > 10
Logical Operators
 Logical operators perform logical NOT, AND, and OR operations over Boolean values (TRUE and FALSE), plus NULL and MISSING. 
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
NOT Truth Table
Value
Result
True
False
False
True
NULL
NULL
AND Truth Table
﻿
True
NULL
False
True
True
NULL
False
NULL
NULL
NULL
False
False
False
False
False
OR Truth Table
﻿
True
NULL
False
True
True
True
True
NULL
True
NULL
NULL
False
True
NULL
False
﻿

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

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')
byte_length(str)	Returns the length of str in bytes. Strings in Ditto are encoded in UTF-8, so a character can take up to 4 bytes.	WHERE byte_length(field_name) > 4
char_length(str) character_length(str)	Returns the number of characters in str.	WHERE char_length(field_name) > 4

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'

REGISTER	MAP	ATTACHMENT
null boolean number binary string array object	object	object

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

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')

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

Equation	Result
NULL = NULL	NULL
NULL <> NULL	NULL
1 = NULL	NULL
1 > NULL	NULL

Operator	Purpose	Example
coalesce(v1, v2, ...)	Returns the first non-null value passed to the function	WHERE coalesce(field_name_1, field_name_2, field_name_3) = 'foo'
decode(input, comp1, res1, [comp2, res2...] [, default value])	Compares the input against each compparameter, and if they match type and value, returns the correspondingres value. If no matches are found, the default value is returned (which defaults to NULL).	WHERE decode(field_name, 'foo', 'I found foo', 'bar', 'I found bar', 'I found nothing') = 'foo'
nvl(input, default)	Returns the input if input evaluates to NOT NULL, otherwise returns default.	WHERE nvl(field_name, 0) > 10

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

Updated 17 Sep 2024

Did this page help you?

IDs, Paths, Strings, and Keywords

DQL Roadmap Functionality