Query Reference

Query Reference

Every request is validated twice: once by the generated JSON schema and again by the runtime builders. Unknown top-level keys are rejected.

Top-Level Request Shape

The read tool accepts these top-level keys:

• explain
• expressions
• select
• distinct
• where
• groupBy
• orderBy
• limit
• offset
• with
• withAggregate

with and withAggregate are only available when the model has configured relations in the resolved registry.

Required Fields

These are always required:

• explain: string describing what the query is trying to do
• select: non-empty array of projection items, maximum 10
• distinct: boolean

limit defaults to 10 and offset defaults to 0 when omitted.

expressions

expressions is an optional array of reusable row-level numeric expressions:

[
  {
    "as": "discounted_total",
    "expression": {
      "op": "sub",
      "left": { "column": "total" },
      "right": { "column": "discount" }
    }
  }
]

Each expression item has:

• as: required expression name
• expression: required expression object

Expression nodes may be:

• { "column": "price" }
• { "value": 19.99 }
• { "expression": "earlier_name" }
• { "op": "add" | "sub" | "mul" | "div", "left": ..., "right": ... }

Rules:

• maximum 15 named expressions
• only earlier expressions in the same array may be referenced
• a stored expression must use at least one numeric column
• expression columns must all come from the root model or one single relation path
• expression columns must be numeric
• named expressions are resolved before real columns with the same name

You can reuse these names later in select, where, groupBy, and orderBy through the normal column field.

select

select items are either plain fields:

{ "column": "title" }
{ "column": "discounted_total" }

or aggregates:

{ "type": "count", "as": "article_count" }
{ "type": "sum", "column": "views", "as": "total_views" }
{ "type": "sum", "column": "discounted_total", "as": "discounted_revenue" }

Supported aggregate types are:

• count
• min
• max
• sum
• avg

Rules:

• plain root-model columns do not need an alias
• plain relation-path columns such as author.name require an explicit as
• plain named expressions may be selected through the column field and default to their expression name as the output alias
• plain relation-path columns trigger automatic join planning; there is no separate user-authored join clause today
• without groupBy, you may select plain columns or aggregates, but not both in the same request
• without groupBy, aggregate-only selects produce one whole-result summary row
• without groupBy, a whole-result aggregate cannot be combined with per-row attachments such as with or withAggregate
• with groupBy, every plain selected column must be one of the grouped columns
• sum and avg require numeric columns or numeric named expressions
• count does not accept a column
• aggregate aliases default to {type} for count and {type}_{column} for column-based aggregates when as is omitted

distinct

distinct is required for every request.

Use it to control duplicate root rows when to-many relations participate in filters, ordering, or selected relation-path columns.

Important rule:

• if distinct is true, every raw orderBy column must also appear in select

where

where is an array of leaf conditions or nested groups.

Leaf shape:

{ "column": "status", "operator": "=", "value": "published", "boolean": "and" }
{ "column": "discounted_total", "operator": ">", "value": 100, "boolean": "and" }

Relation existence shape:

{ "relation": "author", "operator": "exists", "boolean": "and" }

Nested group shape:

{
  "where": [
    { "column": "status", "operator": "=", "value": "published", "boolean": "and" },
    { "column": "views", "operator": ">", "value": 1000, "boolean": "or" }
  ],
  "boolean": "and",
  "negate": false
}

Rules:

• every condition or nested group needs a boolean
• maximum nesting depth comes from whereMaxDepth
• maximum conditions per group comes from whereMaxConditionsPerGroup
• leaf column values resolve named expressions first, then real columns
• relation existence checks use relation plus exists or not_exists
• enum columns only accept known enum values
• value shapes are validated against both the operator and the column type

Operators by value kind:

• booleans: =, !=, null, not_null
• numeric-like columns: =, !=, >, >=, <, <=, in, not_in, between, null, not_null
• string-like columns: =, !=, >, >=, <, <=, like, in, not_in, null, not_null

groupBy

groupBy is an object with a required columns array:

{
  "groupBy": {
    "columns": ["status"]
  }
}

Rules:

• maximum 5 grouped columns
• grouped columns may be root columns, supported relation-path columns, or named expressions
• orderBy may target grouped expressions or earlier aliases while grouping
• with and withAggregate can only be combined with groupBy when the relation's own join key is one of the grouped columns

orderBy

orderBy is an array of objects:

[
  { "column": "published_at", "direction": "desc" }
]

Rules:

• directions are asc or desc
• without groupBy, order by root columns, named expressions, supported relation-path columns, or earlier aliases
• with groupBy, order by grouped expressions or earlier aliases
• without groupBy, if select is aggregate-only, order by the aggregate alias instead of a raw column
• with distinct: true, raw order columns must also be selected

Aliases available to orderBy can come from:

• select aliases
• selected named expressions
• withAggregate aliases

Join Behavior

There is currently no top-level join request field.

Instead, joins are derived automatically from supported dotted relation paths used elsewhere in the request, such as:

• where on author.name
• where on a named expression built from author.price
• select of author.name
• groupBy on author.company.name
• orderBy on author.name

The remaining roadmap item is explicit agent-controlled joins, not the current auto-derived relation joins.

with

with is an array of eager-load requests:

[
  { "path": "author" },
  { "path": "author.company", "select": ["id", "name"] }
]

Rules:

• path must be a configured direct relation or a nested path derived from registered related models
• nested path depth is limited by relationPathMaxDepth
• select, when present, must be a non-empty array of the related model's own column names
• key columns needed for attachment and nested children are auto-preserved even when select narrows the relation

withAggregate

withAggregate is an array of direct relation aggregates:

[
  { "relation": "revisions", "type": "count", "as": "revision_count" },
  { "relation": "revisions", "type": "max", "column": "created_at", "as": "last_revision_at" }
]

Rules:

• only configured direct relations are allowed here
• count takes no column
• sum and avg require numeric related columns
• aliases must not collide with root columns, relation names, or other result aliases
• related model scopes are applied to these aggregates
• when as is omitted, the alias is auto-derived from the relation, aggregate type, and column name