pagehive

query_table

Run a data query: where, any (AND-ed with where when both are set), search, sort, paging, group_by, include, fields, and visibility.

TablesRead-onlyAll plans

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

NameTypeAbout
tablerequiredstringThe table to query.
whereobjectFilters by column, all of which must match: a value, or one operator such as {"gte": 10}, {"contains": "x"} or {"empty": true}.
anyarray of objectsA list of where filters, of which at least one must match.
searchstringText to find in the table's searchable columns.
sortstring or array of stringA column to sort by; a leading - sorts descending. A list sorts by several columns.
limitnumberHow many rows to return per page.
pagenumberThe page number, starting at 1.
cursorstringThe next_cursor from the previous page, to continue.
group_bystringA column to group by. Returns one row per value with its count and the aggregates you ask for. Not with fields.
sumarray of stringColumns to add up, per group or for the whole result.
avgarray of stringColumns to average.
minarray of stringColumns to return the smallest value of.
maxarray of stringColumns to return the largest value of.
includearray of stringRelation columns whose linked rows to include in each row.
fieldsarray of stringColumn 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.
visibilityone of: shared, private, allOnly 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_idstringOnly 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