The SQL filter evaluates a boolean expression against each datapoint. You write the body of a WHERE clause without the word WHERE itself — for example, Age > 20.

📘
Which syntax applies depends on the source
This reference documents the expression dialect that Validio's own engine evaluates, which applies to non-SQL sources such as data streams.
For SQL sources — data warehouses, query engines, and transactional databases — the filter expression is evaluated by that system in its own SQL dialect. Refer to the documentation for your data source instead.

For how to configure a SQL filter on either kind of source, see SQL Filter. To compose an expression with an LLM instead of writing it by hand, see Generating SQL in Validators and Filters.

The filter editor verifies your expression against the source as you write, reporting syntax and field errors. Filters have no sample-row preview, unlike the custom SQL source and Custom SQL validator editors.

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
`number1 + 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 e) 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 than 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 (string1 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. For example, `4 IN (1,2,4)` returns TRUE, `4 IN (1,2,3)` returns FALSE, and `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. For example, `4 NOT IN (1,2,3)` returns TRUE, `4 NOT IN (1,2,4)` returns FALSE, and `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, ... For example, `COALESCE(NULL, NULL, 5, NULL)` returns 5.

Filter Functions

SQL Function Description

JSON_MATCH_KEYS(signature) Returns TRUE if the record matches the provided signature. The signature should be a string containing a JSON object, describing exactly the keys expected to be present in records — for example, JSON_MATCH_KEYS('{"name":"John", "age":30, "car":null}'). The match compares the keys in the record against those of the signature JSON object, including any nested objects and their keys. If a key is in the signature and not in the record, or vice versa, this function returns FALSE. Returns NULL if the input is not a valid JSON object.

SQL Function	Description
`JSON_MATCH_KEYS(signature)`	Returns TRUE if the record matches the provided signature. The signature should be a string containing a JSON object, describing exactly the keys expected to be present in records — for example, `JSON_MATCH_KEYS('{"name":"John", "age":30, "car":null}')`. The match compares the keys in the record against those of the signature JSON object, including any nested objects and their keys. If a key is in the signature and not in the record, or vice versa, this function returns FALSE. Returns NULL if the input is not a valid JSON object.

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 boolean is FALSE.
`boolean IS TRUE`	Returns TRUE if boolean is TRUE.
`boolean IS FALSE`	Returns TRUE if boolean is FALSE.
`boolean IS NOT TRUE`	Returns TRUE if boolean is FALSE or NULL.
`boolean IS NOT FALSE`	Returns TRUE if boolean is TRUE or NULL.

String Functions

SQL Function	Description
`string1 \|\| string2`	Returns a string that concatenates string1 with string2. For example, `'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 a string. Returns NULL if any argument is NULL. For example, `CONCAT('AB', 12, 'CD')` returns 'AB12CD'.
`LENGTH(string)`	Returns the number of characters in string. For example, `LENGTH('abc')` returns 3.
`LOWER(string)`	Returns string in lower case. For example, `LOWER('ABC')` returns 'abc'.
`UPPER(string)`	Returns string in upper case. For example, `UPPER('abc')` returns 'ABC'.
`TRIM([BOTH \| LEADING \| TRAILING] [string1] FROM string2)`	Returns a string that removes leading and/or trailing characters string1 from string2. For example, `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). For example, `SUBSTRING('abcdef' FROM 2 FOR 3)` returns 'bcd'.
`REGEXP(string1, string2)`	Returns TRUE if string1 is matched by regular expression string2. For example, `REGEXP('2020-01-01', '\d{4}-\d{2}-\d{2}')` returns TRUE. See the Rust regex crate for the documented syntax.
`REVERSE(string)`	Returns the reversed string. For example, `REVERSE('abc')` returns 'cba'.

Type Conversion Functions

SQL Function	Description
`CAST(value AS T)`	Returns a new value cast to type T. For example, `CAST('42' AS INT)` returns 42, and 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	For example, `CAST(42 AS TEXT)` returns '42'.
BOOLEAN	BOOLEAN, STRING	Only the strings 'true' and 'false' can be cast from strings. For example, `CAST('true' AS BOOLEAN)` returns TRUE, `CAST(1 AS BOOLEAN)` returns FALSE, and `CAST(true AS TEXT)` returns 'true'.
NUMBER	NUMBER, STRING	For example, `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. For example, `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 identifier in the expression is interpreted as a field name and returns the corresponding value from the datapoint being evaluated. For example, `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.

Comments

SQL filter query syntax only supports comments using the following multi-line syntax, /* your_comment */. For example,

"FIELD" = 'Value' /* additional feature filtering */