Query Language
The Data API's query language, on one page. The same filter grammar is used by a request's where, a role's row filters and payload checks, a DataView's filter, a page's Resources, a column's conditions and workflow steps. The guides explain each part: Reading, Filtering, Aggregating.
Parameters
| Parameter | Example | |
|---|---|---|
fields | columns and relation paths to return; without it, every column you may read | fields=number,status,site.name |
where | which rows | where[status][_eq]=NEW |
search, search_fields | rows containing words, and where to look | search=compressor |
order_by | order; - for descending | order_by=-priority,due_at |
limit, offset | pagination: 50 by default, 500 at most | limit=50&offset=100 |
count | also return the total in meta.count | count=true |
flags | named conditions checked per row, in meta.flags | flags[overdue][due_at][_lt]=$CURRENT_TIMESTAMP |
group_by | groups | group_by=status |
_count, _sum, _avg, _min, _max | aggregates | _count[*]=n&_sum[total]=amount |
having | filter on aggregates | having[n][_gt]=10 |
Operators
| Operator | Means | Value |
|---|---|---|
_eq, _neq | equal, different | one value |
_gt, _gte, _lt, _lte | greater, at least, less, at most | one value |
_like, _ilike | pattern (% any text, _ one character); _ilike ignores case | a pattern |
_regex | POSIX regular expression | a regular expression |
_in, _nin | one of, none of | values separated by commas |
_between | between two values, both included | two values separated by a comma |
_null, _nnull | has no value, has a value | true |
_contains, _contained_by, _overlap | array or jsonb containment | {A,B} or JSON |
_and, _or, _not | combine conditions | a list, or one condition |
In a stored filter (JSON), a value is a string, a number or a boolean: {"quantity": {"_lte": 20}}, {"active": {"_eq": true}}. _in, _nin and _between take a list, {"status": {"_in": ["NEW", "ASSIGNED"]}}, or the same values as comma-separated text, "NEW,ASSIGNED". In a query string, repeat the key for a list: where[status][_in][]=NEW&where[status][_in][]=ASSIGNED.
{ "_or": [
{ "priority": { "_eq": "URGENT" } },
{ "_and": [ { "status": { "_eq": "NEW" } }, { "reported_at": { "_lt": "$INTERVAL.24h" } } ] }
] }
Relation paths
A dotted path follows a foreign key: site.name, site.customer.name, technician.user.email. It works in fields, where, order_by and search_fields, and needs a READ field grant on the path. Paths go one way, from the table holding the foreign key; up to 3 levels.
Variables
| Variable | |
|---|---|
$USER.<column> | a column of the caller's own user row: $USER.id, $USER.branch |
$CURRENT_TIMESTAMP, $CURRENT_DATE, $CURRENT_TIME | now, today, the current time, PostgreSQL's |
$INTERVAL.<n><unit> | now minus a duration: 15m, 24h, 7d, 2w, 1y |
$PERIOD.<period>.from, .to | a calendar period's start (included) and end (excluded): today, yesterday, this_week, last_week, this_month, last_month, this_quarter, last_quarter, this_year, last_year |
$ROW.<column> | another column of the same row |
PostgreSQL computes them, in the caller's time zone. $INTERVAL.7d always means "seven days ago", never an interval to add. Inside _in and _nin, only literal values and $USER.*.
Limits
| Limit | Default | Error |
|---|---|---|
| Filter depth | 10 | QUERY_DEPTH_LIMIT_EXCEEDED |
| Query nodes | 200 | QUERY_COMPLEXITY_LIMIT_EXCEEDED |
| Query parameters | 200 | QUERY_PARAMETER_LIMIT_EXCEEDED |
| Query string | 32 KB | QUERY_STRING_TOO_LARGE |
| Request body | 5 MB | REQUEST_BODY_TOO_LARGE |
| Relation depth | 3 |
All in Settings › Data API › Limits. A request past a limit is refused before any SQL runs.