# Query dialects

The five clauses — `filter`, `select`, `sort`, `group`, `limit` — look the same everywhere, but three
kinds of store answer them slightly differently. This page is the diff, so you do not have to read three
long pages to find it.

## The three dialects at a glance

| | **Generic search** | **Time series** | **Data sets** |
|---|---|---|---|
| URL | `/north/v80/search/<resource>` | `/north/v80/timeseries/provision/organizations/{org}/{id}/data` | `/north/v80/datasets/provision/organizations/{org}/{id}/data` |
| Field names | Dotted paths of the resource | `bucketColumn`, `identifierColumn`, `columns.name`, `context.name` | `identifierColumn`, `columns.name` |
| `filter` | Full operator set | Same syntax, only on filterable columns | Same syntax, only on filterable columns |
| `select` | Objects with `name` and `fields` | Same keys as `filter` | **Array of column names** |
| `sort` | An object of `parameters`, each a field and a direction | A **string**: the identifier of a sort declared in the definition | A **string**: the identifier of a sort declared in the definition |
| `group` | Supported | Not applicable | **Does not exist** |
| `limit` | `start` and `size` | Same, with CSV caveat below | Same, with CSV caveat below |
| Response | Array named after the resource | `columns` plus `data` matrix | `columns` plus `data` matrix |
| CSV output | — | Yes | Yes |

## Why time series and data sets are stricter

Both are **pre-computed projections**: you declare their columns up front, and the platform builds indexes
for exactly those. That is what makes them fast, and it is also why you cannot filter or sort on an arbitrary
field.

**Sorting is declared, not composed.** A generic search accepts any field in its `sort` object. A time series
or a data set accepts only the **identifier of a sort declared in its definition** — a named, ordered list of
columns with directions — plus the reverse of each one, which the platform exposes automatically because the
same index serves it backwards. There is no per-column `sortable` flag and no cap on how many sorts a
definition may hold.

```json
{ "sort": { "parameters": [ { "name": "provision.device.identifier", "type": "ASCENDING" } ] } }   // generic
{ "sort": "sortByDeviceAsc" }                                                                      // time series, data sets
```

**The real limit is a budget, not a number.** Each filterable column and each declared sort consumes
**optimization units**, and each definition has a budget of them. Both stores offer an `optimizationPlan`
endpoint that reports what a definition would consume before you commit, and expose
`usedSearchOptimizationUnits` and `freeSearchOptimizationUnits` on the definition itself.

See [Defining a time series](../time_series/defining/#filtering-and-sorting-limits) and
[Defining a data set](../data_sets/defining/#limits).

**Filters have four modes**, not two: `NO`, `YES` for optional equality, `ALWAYS` for a filter every query
must supply, and `RANGE` for `>`, `<` and `BETWEEN`. `RANGE` applies to numeric columns only; `date-time`
columns are always range-searchable.

## The matrix response

Generic searches return objects, one per row. Time series and data sets return a **matrix** instead: a
`columns` array naming the fields, and a `data` array of rows, each row an array of values in that same
order.

```json
{
  "page": { "number": 26 },
  "columns": ["Prov Identifier", "Coll manufacturer", "Coll model"],
  "data": [
    ["MyDevice1", "OpenGate", "OpenGate"],
    ["MyDevice2", "OpenGate", "OpenGate"]
  ]
}
```

Read the values off `columns` rather than hardcoding positions. If you do rely on the order, this is what it
is when you omit `select`:

| Store | Column order without `select` |
|---|---|
| Time series | `bucketColumn`, then `identifierColumn`, then the `context` columns, then the aggregated `columns` |
| Data sets | `identifierColumn`, then the defined `columns.name` in declaration order |

Time series additionally offer an **aggregated read**, `POST .../{id}/dataset`, which collapses every bucket
of a device into a single output row. There `select.columns` takes a `column`, an `alias` and an
`aggregation` function per output variable, and the result is always sorted ascending by `identifierColumn`,
which is included whether you ask for it or not. See [Time series](../time_series/).

## CSV output changes the rules

Time series and data sets can answer in CSV instead of JSON, and that switch changes two behaviours that
surprise people:

**`limit` flips meaning.** In JSON, omitting `limit` applies the configured defaults. In CSV, omitting it
means *give me everything*:

| Body | JSON response | CSV response |
|---|---|---|
| `{"filter": {}, "limit": {"size": 500, "start": 1}}` | 500 rows from row 1 | 500 rows from row 1 |
| `{"filter": {}}` | Configured default page | **The complete data set** |
| `{"filter": {}, "limit": {}}` | Configured default page | **Error**: an incomplete `limit` is rejected |

**Sorting is disabled.** CSV retrieval turns sorting off deliberately, to keep large exports fast. If you need
ordered output, either sort downstream or use the JSON response.

{{% notice style="warning" title="Complete retrieval is expensive" icon="triangle-exclamation" %}}
Omitting `limit` in CSV mode downloads the entire store. On a large time series that is a long, heavy request.
Page it unless you genuinely want everything.
{{% /notice %}}

CSV formatting — the quoting character, the escape character, the end-of-line sequence and how nulls are
represented — is customizable through HTTP header options, and you are responsible for the result being
well-formed CSV.

{{% notice style="note" title="Undocumented header names" icon="circle-info" %}}
The specific header names for those CSV options are not currently published in the API specification. Until
they are, ask your platform contact for the exact names.
{{% /notice %}}

## What stays the same

Worth stating plainly, because it is most of the surface:

- `POST` with a JSON body, always.
- `X-ApiKey` for authentication.
- The [filter operators](../data_lake/features/filtering/) — `eq`, `neq`, `like`, `gt`, `lt`, `gte`, `lte`,
  `in`, `nin`, `exists`, `and`, `or` — behave identically in all three dialects.
- `limit` uses `start` and `size` everywhere.
- The `utc=true` header option returns date fields in UTC in all of them.