Writing queries#

Patterns for querying the Nominal SQL interface: how to scope a query, filter and aggregate it, work across tables, and bound what comes back.

Scoping telemetry queries#

Every query against a telemetry table must filter by the dataset(s) it reads. The telemetry tables are points_double, points_int, points_string, points_struct, logs, and channels, and each requires a dataset_rid predicate, or a join on dataset_rid to a metadata table as shown under Joins:

SELECT ts, channel, value
FROM points_double
WHERE dataset_rid = '<dataset-rid>'

SELECT ts, channel, value
FROM points_double
WHERE dataset_rid IN ('<dataset-rid>', '<dataset-rid>')

Omitting it fails validation:

Query on table 'points_double' must filter by an entity selector: [dataset_rid]; apply the filter in the SELECT that reads this table

The metadata tables (assets, runs, run_assets, datasets, events, and event_assets) carry no such requirement, so start from datasets when you need a dataset_rid:

SELECT dataset_rid, name FROM datasets LIMIT 20

Reading a whole dataset#

To read every channel in a dataset, whatever its type, use the dataset’s RID in double quotes as the table name. No additional WHERE dataset_rid = ... filter is needed:

SELECT *
FROM "<ri.catalog.instance.dataset.identifier>"
ORDER BY ts
LIMIT 100

Each row holds its value in the column for its data_type: value_double, value_int, value_string, value_struct, or message for a log entry.

Filtering#

Filter on time by comparing ts against TIMESTAMP literals:

SELECT ts, value
FROM points_double
WHERE dataset_rid = '<dataset-rid>'
  AND ts >= TIMESTAMP '2026-07-17 21:00:00'
  AND ts <  TIMESTAMP '2026-07-17 22:00:00'
ORDER BY ts

Two things to watch:

String comparison is case-sensitive. channel = 'Vehicle_Speed' does not match a channel recorded as vehicle_speed. Wrap both sides in UPPER when you’re unsure of the casing, at the cost of a slower filter.
value is nullable on every points_* table. A sample can exist with no value, so add AND value IS NOT NULL when nulls would distort what you’re computing.

Filtering by tag#

Where the same channel name appears for several physical sources, a channel filter alone is ambiguous. The points_* and logs tables each carry a tags column of type MAP, holding the key-value pairs recorded alongside the series, and indexing it by key narrows the query to one source.

Index a tag by key and compare it like any other value:

SELECT ts, value
FROM points_double
WHERE dataset_rid = '<dataset-rid>'
  AND tags['<tag key>'] = '<tag value>'
ORDER BY ts

Tag keys vary between datasets. To see which keys a dataset uses, list its channels and their tags from channels:

SELECT channel, tags
FROM channels
WHERE dataset_rid = '<dataset-rid>'

Note

Tag filtering is not a substitute for dataset_rid. A tags predicate does not satisfy the scoping requirement, so telemetry queries need both.

Aggregation#

Aggregating turns millions of samples into something you can read or plot without pulling every row across the wire. To downsample, bucket each timestamp with DATE_BIN and group by the bucket:

SELECT DATE_BIN(INTERVAL '1' MINUTE, ts, TIMESTAMP '1970-01-01 00:00:00') AS bucket,
       AVG(value) AS mean_value,
       MIN(value) AS min_value,
       MAX(value) AS max_value
FROM points_double
WHERE dataset_rid = '<dataset-rid>'
  AND channel = '<channel name>'
GROUP BY bucket
ORDER BY bucket

The third argument is the origin the buckets are measured from. Passing the Unix epoch gives you boundaries aligned to clock time.

To get one column’s value at the row where another is highest or lowest, such as each channel’s latest reading, use MAX_BY or MIN_BY:

SELECT channel,
       MAX_BY(value, ts) AS last_value,
       MAX(ts) AS last_seen
FROM points_double
WHERE dataset_rid = '<dataset-rid>'
GROUP BY channel
ORDER BY channel

Beyond these, the aggregates include standard deviation, variance, root mean square, and continuous percentiles. See Aggregate functions for the full list.

Joins#

Joins pair telemetry with the metadata that describes it. channels and datasets both carry a dataset_rid to match on:

SELECT d.name, c.channel, c.data_type
FROM channels AS c
JOIN datasets AS d ON c.dataset_rid = d.dataset_rid
WHERE c.dataset_rid = '<dataset-rid>'
  AND d.dataset_rid = '<dataset-rid>'
ORDER BY c.channel

Filter the metadata side too. The join condition doesn’t narrow what is read from datasets, so with only c.dataset_rid filtered, the query reads every dataset in the workspace and can fail on the metadata limit. In any join between telemetry and metadata tables, filter the metadata table directly.

Find datasets by name and read them in one query. Instead of looking up a dataset_rid first, join to datasets on dataset_rid and filter on the dataset’s name. The join takes the place of the dataset_rid filter:

SELECT d.name, p.ts, p.channel, p.value
FROM points_double AS p
JOIN datasets AS d ON p.dataset_rid = d.dataset_rid
WHERE d.name = '<dataset name>'
ORDER BY p.ts
LIMIT 100

Every dataset that matches the filter is read, so keep the filter specific.

Metadata tables join to each other the same way. run_assets connects runs to the assets they cover:

SELECT r.title, a.title AS asset
FROM run_assets AS ra
JOIN runs AS r ON ra.run_rid = r.run_rid
JOIN assets AS a ON ra.asset_rid = a.asset_rid
ORDER BY r.start_time DESC
LIMIT 20

To read a run’s data, go through the run’s assets: runs has no dataset_rid column, but run_assets connects a run to its assets, and each asset’s dataset_rid names its dataset. Filtering ts to the run’s start and end keeps only what was recorded during the run:

SELECT a.title AS asset, p.ts, p.channel, p.value
FROM points_double AS p
JOIN assets AS a ON p.dataset_rid = a.dataset_rid
JOIN run_assets AS ra ON ra.asset_rid = a.asset_rid
JOIN runs AS r ON r.run_rid = ra.run_rid
WHERE r.run_rid = '<run-rid>'
  AND p.ts BETWEEN r.start_time AND r.end_time
ORDER BY p.ts
LIMIT 100

For a run that’s still open, which has no end_time, use p.ts >= r.start_time instead.

Combining telemetry tables#

Telemetry tables combine through IN subqueries and PIVOT rather than JOIN.

To filter one telemetry table by another, use an IN subquery. Each read needs its own dataset_rid filter, including the one inside the subquery:

SELECT ts, channel, value
FROM points_double
WHERE dataset_rid = '<dataset-rid>'
  AND channel IN (
    SELECT channel FROM <telemetry table>
    WHERE dataset_rid = '<dataset-rid>' AND <condition>
  )
ORDER BY ts

To compare channels side by side, PIVOT a subquery so each channel becomes a column. Bucket ts with DATE_BIN so samples from different channels land in the same row, and pick each bucket’s last reading with MAX_BY(value, ts):

SELECT *
FROM (
  SELECT DATE_BIN(INTERVAL '1' SECOND, ts, TIMESTAMP '1970-01-01 00:00:00') AS bucket,
         channel, ts, value
  FROM points_double
  WHERE dataset_rid = '<dataset-rid>'
) PIVOT (MAX_BY(value, ts) FOR channel IN ('<channel name>' AS <column name>, '<channel name>' AS <column name>))
ORDER BY bucket

Limiting results#

Add a LIMIT while exploring, since a dataset can hold millions of samples. The service also limits how large a result can be and how long a query can run. See Limits.

Function availability#

The Function reference lists the functions queries can call, with their signatures. It also marks the ones that work only on telemetry tables, such as DATE_BIN, MIN_BY, and DERIVATIVE.