# 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:

```json
[
  {
    "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:

```json
{ "column": "title" }
{ "column": "discounted_total" }
```

or aggregates:

```json
{ "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:

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

Relation existence shape:

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

Nested group shape:

```json
{
  "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:

```json
{
  "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:

```json
[
  { "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:

```json
[
  { "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:

```json
[
  { "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