# Queries guide

How to build read queries with `QueryBuilder`. Every example is engine-agnostic
input; the engine is chosen at `.transpile({ engine })`.

- [The shape of a query](#the-shape-of-a-query)
- [Source](#source)
- [Selecting fields](#selecting-fields)
- [Filters](#filters)
- [Operators](#operators)
- [Aggregations](#aggregations)
- [Grouping](#grouping)
- [Date grouping](#date-grouping)
- [Having](#having)
- [Ordering](#ordering)
- [Pagination](#pagination)
- [Nested fields](#nested-fields)
- [Output metadata](#output-metadata)
- [Rules & limits](#rules--limits)

---

## The shape of a query

```ts
import { QueryBuilder, type QueryInput } from '@qrvey/query-builder';

const input: QueryInput = {
  source: { name: 'orders' },      // required
  select: [ /* … */ ],             // OR groupBy + aggregations
  filter: { /* … */ },
  groupBy: [ /* … */ ],
  aggregations: [ /* … */ ],
  having: { /* … */ },
  orderBy: [ /* … */ ],
  limit: 25,                        // default 25
  skip: 0,                          // offset pagination
  cursor: [ /* … */ ],              // cursor pagination (search_after)
};

const result = new QueryBuilder(input, { engine: 'postgresql' }).transpile();
```

A query is either a **row query** (`select`) or an **aggregation query**
(`groupBy` and/or `aggregations`) — not both (see [Rules & limits](#rules--limits)).

---

## Source

One source per query. `schema` optionally qualifies the table for SQL engines
and is ignored by Elasticsearch/OpenSearch (an index has no schema).

```ts
{ source: { name: 'orders' } }
{ source: { name: 'orders', schema: 'analytics' } }
// PostgreSQL → "analytics"."orders"     ClickHouse → `analytics`.`orders`
```

---

## Selecting fields

Each selected field declares its `type` (`string | number | boolean | date`),
which drives parameter typing and casts.

```ts
{
  source: { name: 'orders' },
  select: [
    { field: 'id', type: 'string' },
    { field: 'amount', type: 'number' },
    { field: 'created', type: 'date' },
  ],
}
```

- **SQL** → `SELECT "id", "amount", "created" FROM "orders" …`
- **ES/OS** → `{ _source: ['id', 'amount', 'created'], … }`

Omit `select` (and `groupBy`/`aggregations`) is invalid — a query must produce
something.

---

## Filters

A filter is a tree: a leaf **condition** or a logical **combinator** (`and`,
`or`, `not`). Combinators nest arbitrarily.

```ts
// leaf
{ filter: { field: 'status', type: 'string', operator: 'eq', value: 'active' } }

// nested
{
  filter: {
    and: [
      { field: 'status', type: 'string', operator: 'eq', value: 'active' },
      {
        or: [
          { field: 'amount', type: 'number', operator: 'gte', value: 100 },
          { not: { field: 'vip', type: 'boolean', operator: 'eq', value: false } },
        ],
      },
    ],
  },
}
```

- **SQL** → `WHERE ("status" = $1 AND ("amount" >= $2 OR NOT ("vip" = $3)))`
- **ES/OS** → nested `bool.must` / `bool.should` / `bool.must_not`

Values are always parameterized (SQL) or placed as query values (ES) — never
string-concatenated.

---

## Operators

| Operator | Meaning | Value |
| -------- | ------- | ----- |
| `eq` / `neq` | equals / not equals | scalar |
| `gt` / `gte` / `lt` / `lte` | comparisons | scalar |
| `in` / `notIn` | membership | array |
| `between` | inclusive range | `[low, high]` |
| `contains` / `notContains` | substring (LIKE `%v%`) | string |
| `startsWith` / `endsWith` | prefix / suffix | string |
| `exists` / `isNotNull` | value present | — |
| `isNull` | value absent | — |

```ts
{ field: 'amount', type: 'number', operator: 'between', value: [10, 100] }
// SQL → "amount" BETWEEN $1 AND $2      ES/OS → { range: { amount: { gte: 10, lte: 100 } } }

{ field: 'name', type: 'string', operator: 'contains', value: '50%_off' }
// LIKE / wildcard metacharacters inside the value are escaped automatically.
```

`contains`/`startsWith`/`endsWith` map to `LIKE` (SQL) and `wildcard` (ES/OS);
`%`, `_`, `*` and `?` inside the user value are escaped so they match literally.

---

## Aggregations

Functions: `count`, `sum`, `avg`, `min`, `max`. `count` without a field means
"count rows/documents" (`COUNT(*)` in SQL, total hits in ES/OS); every other
function requires a `field` and its `type`.

```ts
{
  source: { name: 'orders' },
  aggregations: [
    { function: 'count' },                                  // COUNT(*)
    { function: 'sum', field: 'amount', type: 'number' },
    { function: 'avg', field: 'amount', type: 'number' },
  ],
}
```

Aggregations get a deterministic output name (e.g. `__sum_amount`, `__count`)
used consistently in the SQL alias and the [output metadata](#output-metadata).

---

## Grouping

Group by one or more fields; combine with aggregations.

```ts
{
  source: { name: 'orders' },
  groupBy: [{ field: 'country', type: 'string' }],
  aggregations: [{ function: 'sum', field: 'amount', type: 'number' }],
}
```

- **SQL** → `SELECT "country" AS "country", SUM("amount") AS "__sum_amount" FROM "orders" GROUP BY "country"`
- **ES/OS** → a `terms` aggregation with a nested metric

Each group level can carry a `limit` (top-N values for that level).

---

## Date grouping

Add `granularity` to a `date` group field: `hour`, `day`, `week`, `month`,
`quarter`, `year`.

```ts
{
  source: { name: 'orders' },
  groupBy: [{ field: 'created', type: 'date', granularity: 'month' }],
  aggregations: [{ function: 'count' }],
}
```

- **PostgreSQL** → `date_trunc('month', "created")`
- **ClickHouse** → `toStartOfMonth(\`created\`)`
- **ES/OS** → `date_histogram` with `calendar_interval: month`

---

## Having

Filter over aggregation results (mirrors [filters](#filters) but the leaf targets
an aggregation).

```ts
{
  source: { name: 'orders' },
  groupBy: [{ field: 'country', type: 'string' }],
  aggregations: [{ function: 'sum', field: 'amount', type: 'number' }],
  having: {
    aggregation: { function: 'sum', field: 'amount', type: 'number' },
    operator: 'gt',
    value: 1000,
  },
}
```

- **SQL** → `… GROUP BY "country" HAVING SUM("amount") > $1`

---

## Ordering

Order by a field or by an aggregation. In grouped queries you may only order by
grouped fields or aggregations.

```ts
// by field
{ orderBy: [{ field: 'created', type: 'date', direction: 'desc' }] }

// by aggregation
{
  orderBy: [
    { aggregation: { function: 'sum', field: 'amount', type: 'number' }, direction: 'desc' },
  ],
}
```

Ordering by a date-grouped field renders the group expression (e.g.
`toStartOfMonth("created")`), so the SQL stays valid.

---

## Pagination

Two mutually exclusive modes:

### Offset

```ts
{ limit: 20, skip: 40 }   // SQL → LIMIT 20 OFFSET 40   ES/OS → size: 20, from: 40
```

### Cursor (`search_after`)

```ts
{
  orderBy: [{ field: 'created', type: 'date', direction: 'desc' }], // required
  limit: 20,
  cursor: [1710000000000],
}
```

Cursor pagination requires an `orderBy` and cannot be combined with `skip`. The
result `metadata.pagination.mode` tells the executor which strategy to use (see
the [Integration guide](INTEGRATION.md)).

Defaults & limits: `limit` defaults to `25`; `limit`/`skip` max `10_000`; up to
`50` selected fields.

---

## Nested fields

Any field reference (`select`, `filter`, `groupBy`, `aggregations`, `orderBy`)
can read a value nested inside a JSON/object column via `path`.

```ts
{ field: 'profile', type: 'string', path: ['address', 'city'] }
```

| Engine | Rendering |
| ------ | --------- |
| PostgreSQL | `("profile" -> 'address' ->> 'city')` cast by type |
| ClickHouse | `JSONExtractString(\`profile\`, 'address', 'city')` |
| Elasticsearch/OpenSearch | dotted field name `profile.address.city` |

The output metadata names a nested field by its dotted path
(`profile.address.city`), consistent across engines.

---

## Output metadata

Every result carries `metadata.outputFields`, describing each column/field the
query returns, so consumers map rows/buckets to a stable shape regardless of
engine.

```ts
metadata.outputFields;
// [
//   { name: 'country', kind: 'group', sourceField: 'country', type: 'string' },
//   { name: '__sum_amount', kind: 'aggregation', sourceField: 'amount', function: 'sum', type: 'number' },
// ]
```

See the [API reference](API.md#queryoutputmetadata) for the full type.

---

## Rules & limits

The plan is validated before compilation; violations throw a `QueryBuilderError`:

- A query must define `select`, `groupBy`, or `aggregations`.
- `select` cannot be combined with `groupBy` (grouped output is inferred).
- `select` cannot be combined with `aggregations` unless grouping rules are met.
- `skip` and `cursor` cannot be provided together.
- `cursor` pagination requires `orderBy`.
- Grouped queries can only order by grouped fields or aggregations.
- `limit`/`skip` ≤ `10_000`; at most `50` selected fields.

See the [API reference](API.md#errors) for the error codes.