What it does
Run a data query: where, any (AND-ed with where when both are set), search, sort, paging, group_by, include, fields, and visibility. The SQL runs in Postgres. Sort keeps the first occurrence of each column and allows at most 5 distinct columns. On an own_and_shared table a member or site agent reads the shared rows plus its own private rows; visibility narrows that to shared or private.
Plans
Works on every plan: Free, Basic, Pro and Ultra.
Parameters
| Name | Type | About |
|---|---|---|
tablerequired | string | The table to query. |
where | object | Filters by column, all of which must match: a value, or one operator such as {"gte": 10}, {"contains": "x"} or {"empty": true}. |
any | array of objects | A list of where filters, of which at least one must match. |
search | string | Text to find in the table's searchable columns. |
sort | string or array of string | A column to sort by; a leading - sorts descending. A list sorts by several columns. |
limit | number | How many rows to return per page. |
page | number | The page number, starting at 1. |
cursor | string | The next_cursor from the previous page, to continue. |
group_by | string | A column to group by. Returns one row per value with its count and the aggregates you ask for. Not with fields. |
sum | array of string | Columns to add up, per group or for the whole result. |
avg | array of string | Columns to average. |
min | array of string | Columns to return the smallest value of. |
max | array of string | Columns to return the largest value of. |
include | array of string | Relation columns whose linked rows to include in each row. |
fields | array of string | Column keys to return, such as ["slug","title"]. id, position, revision, created_at, updated_at, _member, _created_by and _updated_by always come back. Filters, search and sort can use other columns. Not with group_by. It keeps long text columns out of the response. |
visibility | one of: shared, private, all | Only for an own_and_shared table. shared: rows that belong to no member. private: your own private rows (the owner: every member's). all, the default: everything you may read, so the shared rows plus your own. |
site_id | string | Only for a site shared with you (whoami lists them): read or change that site's data as its member. |
Example
Call query_table with these arguments:
{
"table": "recipes",
"where": {
"vegetarian": true,
"minutes": {
"lte": 30
}
},
"sort": "minutes",
"limit": 20,
"fields": [
"title",
"minutes"
]
}
Ids like 01a1… stand for your own. list_sites and list_tables give you the real ones.
Related guides
- Search, filters and pagingLet visitors find rows in a long table.
- Show live data on a pageRows on the first paint, refreshed in the browser.