Operators and Functions

Operators and functions can be used to transform columns before being synchronized to a client.

When filtering on parameters (token parameters in the case of parameter queries, and bucket parameters in the case of data queries), operators can only be used in a limited way. Typically only = , IN and IS NULL are allowed on the parameters, and special limits apply when combining clauses with AND, OR or NOT. The exact rules are still being refined — make sure to validate and test the sync rules.

When transforming output columns, or filtering on row values, those restrictions do not apply.

If a specific operator or function is needed, please contact us so that we can consider inclusion in our roadmap.

Some fundamental restrictions on these operators and functions are:

1. It must be deterministic — no random or time-based functions.

2. No external state can be used.

3. It must operate on data available within a single row. For example, no aggregation functions allowed.

Operators

Category	Operators	Notes
Comparison	= != < > <= >=	If either parameter is null, this evaluates to null.
Null	IS NULL, IS NOT NULL
Mathematical	+ - * /
Logical	AND, OR, NOT
Cast	CAST(x AS type) x :: type	Cast to text, numeric, integer, real or blob
JSON	json ->> 'path' json -> 'path'	-> returns the value as a JSON string, while ->> returns the extracted value. The path can start with $., or just contain the key directly.
Text concatenation	||	Joins two text values together.
Arrays	<left> IN <right>	Returns true if the left value is present in the right JSON array. In data queries, only the left value may be a bucket parameter. In parameter queries, the left or right value may be a bucket parameter. Differs from the SQLite operator in that it can be used directly on a JSON array.

Functions

Function	Description
upper(text)	Convert text to upper case.
lower(text)	Convert text to lower case.
hex(data)	Convert blob or text data to hexadecimal text.
base64(data)	Convert blob or text data to base64 text.
length(data)	For text, return the number of characters. For blob, return the number of bytes. For null, return null. For integer and real, convert to text and return the number of characters.
typeof(data)	text, integer, real, blob or null
json_extract(data, path)	Same as ->> operator, but the path must start with $.
json_array_length(data)	Given a json array (as text), returns the length of the array. if data is null, returns null. If the value is not a json array, returns 0.
json_valid(data)	Returns 1 if the data can be parsed as JSON, 0 otherwise.
ifnull(x,y)	Returns x if non-null, otherwise returns y.
unixepoch(datetime, [modifier])	Returns a datetime as unix timestamp. If modifier is "subsec", the result is a floating point number, with milliseconds including in the fraction. The datetime argument is required - this function cannot be used to get the current time.
datetime(datetime, [modifier])	Returns a datetime as a datetime string, in the format YYYY-MM-DD HH:MM:SS. If the specifier is "subsec", milliseconds are also included. If the modifier is "unixepoch", the argument is interpreted as a unix timestamp. Both modifiers can be included: datetime(timestamp, 'unixepoch', 'subsec'). The datetime argument is required - this function cannot be used to get the current time.
ST_AsGeoJSON(geometry)	Convert PostGIS geometry from WKB to GeoJSON. Combine with JSON operators to extract specific fields.
ST_AsText(geometry)	Convert PostGIS geometry from WKB to Well-Known Text (WKT).
ST_X(point)	Get the X coordinate of a PostGIS point.
ST_Y(point)	Get the Y coordinate of a PostGIS point.

Most of these functions are based on the built-in SQLite functions and SQLite JSON functions.