Skip to content

Query shape reference

Every other page in this section teaches one piece of the query language by example. This page is the other kind of reference: every field and every Expression form in one place, each linked to the page that explains it. Skim this first to see the whole shape at a glance, or come back to it once you know roughly what you're looking for and just need the exact name.

The request body

POST /rel's body is one of these, at the top level:

type Query = Relation | ComplexQuery | WellKnownQuery | Query[]
Form Meaning
A Relation A read (see Shaping a query), or, wrapped in a ComplexQuery, a write.
A WellKnownQuery A call to a named, pre-parsed query — see Well-known queries.
A ComplexQuery Optionally write data back through query (a Relation or a WellKnownQuery), and/or shape the response beyond the plain selection — see Writing data back and Complex queries.
Query[] Several queries in one request, one transaction — see Batching queries.

Relation

Every field a query node can carry. schema through limit all apply the same way whether the node is the query's root or a join target nested arbitrarily deep.

interface Relation {
  relation?: string
  function?: string
  schema?: string
  alias?: string
  on?: { [local_column: string]: string }
  arguments?: Expression[] | { [name: string]: Expression }
  where?: Expression
  write_mode?:
    | "insert" | "upsert" | "merge" | "merge-new" | "merge-update"
    | "update" | "deleteonly" | "readonly"
  on_conflict?: string | string[]
  insert_columns?: string[]
  update_columns?: string[]
  join?: { [alias: string]: Relation }
  select?: Expression
  distinct?: boolean
  distinct_on?: Expression[]
  order_by?: (
    | Expression
    | [direction: "asc" | "desc" | "asc-nulls-first" | "desc-nulls-last", Expression]
  )[]
  offset?: number
  limit?: number
}
Field Covered in
relation Shaping a query — exactly one of relation/function.
function Calling functions.
schema Shaping a query — defaults to the search path.
alias Shaping a query — self-references and self-joins.
on Joining and embedding relations — required on a join target.
arguments Calling functions — positional or named, only with function.
where Filtering with where.
write_mode Writing data back.
on_conflict Writing data back — defaults to the primary key.
insert_columns Writing data back.
update_columns Writing data back.
join Joining and embedding relations.
select Selecting fields — defaults to ["full"].
distinct Distinctness.
distinct_on Distinctness — must be a prefix of order_by.
order_by Ordering and pagination.
offset Ordering and pagination — per parent row, inside a join.
limit Ordering and pagination — per parent row, inside a join.

WellKnownQuery and ComplexQuery

interface WellKnownQuery {
  wellknown: string
  params?: any
}

interface ComplexQuery {
  query: Relation | WellKnownQuery
  data?: any // shaped like `query`'s own `select` ; write, if present, else a read
  returns?: "none" | "results"
  count?: boolean
  stats?: boolean
  query_plan?: boolean
  sql?: boolean
  rollback?: boolean
}
Type Field Covered in
WellKnownQuery wellknown Well-known queries.
WellKnownQuery params Well-known queries ## Declaring and using parameters.
ComplexQuery query Writing data back.
ComplexQuery data Writing data back — omit it entirely for a read.
ComplexQuery returns, count, stats, query_plan, sql, rollback Complex queries.

Expression

Every legal value for where, select, order_by, distinct_on, and function arguments. Operators (FoldedOperator/BinaryOperator/UnaryOperator) get their own exhaustive table on the Operators reference — this table covers every other shape Expression can take.

Form Meaning Covered in
null / true / false / a number A literal value. Filtering with where.
"*" Every field of the current relation, plus every join alias. Selecting fields.
a bare string A column, alias, or computed field reference. Filtering with where, Computed fields.
[string] A one-element array — a string literal, not a reference. Filtering with where.
[UnaryOperator, Expression] A unary operator call. Operators reference.
[BinaryOperator, left, right] A two-operand operator call. Operators reference.
[FoldedOperator, ...Expression[]] A variadic, left-folding operator call. Operators reference.
["between"|"not_between", min, exp, max] Range test. Operators reference.
["bigint"|"numeric", value: string] A precise numeric literal, past float64's range. Operators reference.
["in"|"not_in", subject, ...candidates] Set membership; candidates are always literals. Operators reference.
["any"|"all", op, subject, array] Compare against every element of an array/to-many column. Operators reference.
["concat_ws", separator, ...Expression[]] Join strings with a separator. Operators reference.
["coalesce", ...Expression[]] First non-null operand. Operators reference.
["format", format: string, ...Expression[]] printf-style string building. Operators reference.
["agg"|"aggregate", identifier, arguments, filter?] Aggregate an incoming relation's column. Aggregates.
["call", identifier, ...arguments] Call an allowed function explicitly — needed for a cross-schema computed field, or any other function call. Computed fields.
{[name]: Expression} An object literal — a select shape. Selecting fields.
["own"] / ["full"] All columns / all columns plus joins. Selecting fields.
["own_except"|"full_except", except] All columns except the ones named. Selecting fields.
["own_and"|"full_and", and] All columns plus computed keys. Selecting fields.
["own_except_and"|"full_except_and", except, and] Both of the above at once. Selecting fields.
["arr"|"array", ...Expression[]] An array literal. Operators reference.
["index", array, index] 1-indexed array access. Operators reference.
["slice", array, from, to] 1-indexed array slice. Operators reference.
["lst"|"list", ...Expression[]] A list literal — synonym for arr/array. Operators reference.
["get", column, default?] Read-only column reference. Selecting fields.
["set", column, default?] Write-only column reference. Selecting fields.
["get-set", column, default_get?, default_set?] Independent read/write defaults on one column. Selecting fields.
["$param", name, cast?] A well-known query's own declared parameter. Well-known queries.

FunctionIdentifier — call's and agg's first argument — is either a bare, unqualified string (resolved via the search path) or a schema-qualified reference:

type FunctionIdentifier = string | { schema: string, name: string }

See Computed fields and Aggregates.