SQL filter expression reference

SQL expression reference

The SQL filter evaluates a boolean expression in the context of a datapoint.

Data Types

Similar data types map internally to the same json type. For example CASTing a value to INT has the same effect as CASTing the same value to Float.
The following data types are supported:

SQL	Validio type
Varchar	String
Text	String
Integer	Number
Int	Number
Float	Number
Double	Number
Boolean	Boolean
Bool	Boolean
Timestamptz	Timestamp
Timestamp	Timestamp

Arithmetic Functions

SQL Function	Description
+number	Returns number
-number	Returns negative number
numbar1 + number2	Returns number1 plus number2
number1 - number2	Returns number1 minus number2
number1 * number2	Returns number1 multiplied by number2
number1 / number2	Returns number1 divided by number2
number1 % number2	Returns the remainder (modulus) of number1 divided by number2
POWER(base, exponent)	Raises base to the power of exponent
SQRT(number)	Returns the square root of number
LN(number)	Returns the natural logarithm (base 3) of number
LOG(number, base)	Returns the logarithm of number using base
CEIL(number)	Returns the smallest integer that is greater than or equal to number
FLOOR(number)	Returns the largest integer that is less than or equal to number

Comparison Functions

SQL Function	Description
value1 = value2	Returns TRUE if value1 is equal to value2.
value1 <> value2	Returns TRUE if value1 is not equal to value2.
value1 != value2	Returns TRUE if value1 is not equal to value2.
value1 > value2	Returns TRUE if value1 is greater than value2.
value1 < value2	Returns TRUE if value1 is less than value2.
value1 >= value2	Returns TRUE if value1 is greater than or equal to value2.
value1 <= value2	Returns TRUE if value1 is less than or equal to value2.
value1 IS NULL	Returns TRUE if value1 is NULL.
value1 IS NOT NULL	Returns TRUE if value1 is not NULL.
value1 BETWEEN value2 AND value3	Returns TRUE if value1 is greater than or equal to value2 and less than or equal to value3
value1 NOT BETWEEN value2 AND value3	Returns TRUE if value1 is less equal value2 or greater than value3
string1 LIKE pattern [ESCAPE escape-character]	Returns TRUE if string1 matches pattern; See the PostgreSQL documentation for more details.
string1 NOT LIKE pattern [ESCAPE escape-character]	Negation of (string LIKE pattern).
value1 IN (value2,value3,...)	Returns TRUE if value1 exists in the given list (value2, value3,...). Otherwise returns NULL if the list contains NULL. e.g 4 IN (1,2,4) returns TRUE; 4 IN (1,2,3) returns FALSE; 4 IN (1,2,NULL) returns NULL)
value1 NOT IN (value2,value3,...)	Returns FALSE if value1 exists in the given list (value2, value3,...). Otherwise returns NULL if the list contains NULL. 4 NOT IN (1,2,3) returns TRUE; 4 NOT IN (1,2,4) returns FALSE; 4 NOT IN (1,2,NULL) returns NULL.

Conditional Functions

SQL Function	Description
CASE value WHEN value_1 THEN result_1 (WHEN value_2 THEN result_2 ...) (ELSE result_n) END	Returns result_x if value is equal to value_x. When no value matches, returns result_n if provided; Otherwise returns NULL
CASE WHEN condition_1 THEN result_1 (WHEN condition_2 THEN result_2 ...) (ELSE result_n) END	Returns result_x if condition_x returns TRUE. When no condition is met, returns result_n if provided; Otherwise returns NULL
COALESCE(value1,value2,...)	Returns the first value that is not NULL from value1, value2, ... E.g COALESCE(NULL, NULL, 5, NULL) returns 5

Logical Functions

SQL Function	Description
boolean1 OR boolean2	Returns TRUE if boolean1 is TRUE or boolean2 is TRUE
boolean1 AND boolean2	Returns TRUE if both boolean1 and boolean2 are TRUE
NOT boolean	Returns TRUE if both boolean is FALSE.
boolean IS TRUE	Returns TRUE if both boolean is TRUE.
boolean IS FALSE	Returns TRUE if both boolean is FALSE.
boolean IS NOT TRUE	Returns TRUE if both boolean is FALSE.
boolean IS NOT FALSE	Returns TRUE if both boolean is TRUE.

String Functions

SQL Function	Description
string1 \|\| string2	Returns a string that concatenates string1 with string2. E.g 'ab' \|\| 'cd' returns 'abcd'.
CONCAT(value1, value2,...)	Returns a string that concatenates value1, value2,.... If value is not a string, it is automatically CAST into string. Returns NULL if any argument is NULL. E.g. CONCAT('AB', 12, 'CD') returns "AB12CD"
LENGTH(string)	Returns the number of characters in string. E.g LENGTH('abc') returns 3
LOWER(string)	Returns string in upper case. E.g. LOWER('ABC') returns 'abc'.
UPPER(string)	Returns string in upper case. E.g. UPPER('abc') returns 'ABC'.
TRIM([BOTH \| LEADING \| TRAILING] [string1] FROM string2)	Returns a string that removes leading and/or trailing characters string1 from string2. E.g. TRIM(BOTH 'x' FROM 'xxAbcx') returns 'Abc'
SUBSTRING(string FROM number1 [FOR number2]	Returns a substring of string starting from position number1 (1-indexed) with length number2 (to the end by default). E.g SUBSTRING('abcdef' FROM 2 FOR 3) returns 'bcd'.
REGEXP(string1, string2)	Returns TRUE if string1 is matched by regular expression string2. E.g. REGEXP('2020-01-01', '\d{4}-\d{2}-\d{2}') returns TRUE. See the Rust Crate regex for documented syntax.'
REVERSE(string)	Returns the reversed string. E.g REVERSE('abc') returns 'cba'.

Type Conversion Functions

SQL Function	Description
CAST(value AS T)	Returns a new value being cast to type T. E.g. CAST('42' AS INT) returns 42; Returns NULL if type conversion fails or value is NULL.
value::T	Alias for CAST(value AS T)

Allowed type conversions

Type	Allowed casts	Description
STRING	STRING, NUMBER, BOOLEAN, TIMESTAMP	E.g. CAST(42 AS TEXT) returns '42'
BOOLEAN	BOOLEAN, STRING	Only strings 'true' and 'false' can be cast from strings. E.g CAST('true' AS BOOLEAN) returns TRUE, CAST(1 AS BOOLEAN) returns FALSE. CAST(true AS TEXT) returns 'true'.
NUMBER	NUMBER, STRING	E.g. CAST('42' AS INTEGER) returns 42
TIMESTAMP	TIMESTAMP, STRING	Conversions to and from string accept only RFC3339 formatted timestamps.

Data reference functions

SQL Function	Description
JSONPATH(string1)	Returns the value of the field specified by string1 from the datapoint being evaluated. E.g JSONPATH('user.name') returns 'Bob' given a datapoint {"user": {"name": "Bob"}}. Returns NULL if the specified field is not found on the datapoint, or the field's value is not a scalar value. Refer to goessner.net for JsonPath syntax.
identifier	Any identifiers in the expression is interpreted as a field name and returns the corresponding value from the datapoint being evaluated. E.g age + 5 returns 15 given a datapoint {"age": 10}. Returns NULL if identifier is not a root field on the datapoint, or the field does not contain a scalar value. For references to nested fields, see the JSONPATH function