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

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.

{ "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 and Defining a data set.

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.

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

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.

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:

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.

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.

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 — 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.