points_struct#

Structured time series: samples whose value is a JSON object rather than a single scalar.

Telemetry table, so every query must filter by dataset_rid.

For what a channel is, see Channels in the data model.

Columns#

Column

Type

Nullable

Notes

ts

TIMESTAMP

No

Sample time, nanosecond precision.

value

VARIANT

Yes

The structured sample, as JSON. Index by key to reach a field.

channel

VARCHAR

No

Channel name.

dataset_rid

VARCHAR

No

Dataset the sample belongs to. Required in the WHERE clause.

tags

MAP

No

Key-value pairs recorded with the series. See Filtering by tag.

Unlike the other points_* tables, value holds no single scalar type. Index into it by key, chain indexes to reach nested fields, and index arrays by position starting from 0.

A field read this way comes back as JSON text, so a string arrives with its quotes ("ok" rather than ok). To compare or compute with a field, CAST it to VARCHAR, DOUBLE, or INTEGER.

Examples#

Pull one field out of each sample:

SELECT ts, CAST(value['<field>'] AS DOUBLE) AS <field>
FROM points_struct
WHERE dataset_rid = '<dataset-rid>'
  AND channel = '<channel name>'
ORDER BY ts
LIMIT 1000

Reach a nested field by chaining:

SELECT ts, CAST(value['<field>']['<nested field>'] AS DOUBLE) AS <nested field>
FROM points_struct
WHERE dataset_rid = '<dataset-rid>'
  AND channel = '<channel name>'
ORDER BY ts

Filter on a string field. Without the CAST, the comparison includes the field’s quotes and matches nothing:

SELECT ts, value
FROM points_struct
WHERE dataset_rid = '<dataset-rid>'
  AND channel = '<channel name>'
  AND CAST(value['<field>'] AS VARCHAR) = '<value>'
ORDER BY ts

Note

Field names inside value are whatever the producer recorded and vary by channel. The catalog describes value only as VARIANT, so it cannot tell you which keys a given channel carries. Read a few rows and inspect them to find out.

Selecting value itself returns each sample as a JSON string.

Next steps#