Operators and Functions
Operators and functions can be used to transform columns/fields before being synced to a client.
When filtering on parameters (token or client 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.
When transforming output columns/fields, or filtering on row/document 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:
- It must be deterministic — no random or time-based functions.
- No external state can be used.
- It must operate on data available within a single row/document. 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.->> Returns the extracted value. |
| 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. |
| substring(text, start, length) | Extracts a portion of a string based on specified start index and length. Start index is 1-based. |
| 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. |
| json_keys(data) | Returns the set of keys of a JSON object as a JSON array. |
| 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 (in Postgres) geometry from WKB to GeoJSON. Combine with JSON operators to extract specific fields. |
| ST_AsText(geometry) | Convert PostGIS (in Postgres) geometry from WKB to Well-Known Text (WKT). |
| ST_X(point) | Get the X coordinate of a PostGIS point (in Postgres) |
| ST_Y(point) | Get the Y coordinate of a PostGIS point (in Postgres) |
Most of these functions are based on the built-in SQLite functions and SQLite JSON functions.
Was this page helpful?