Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Scenario Discovery query language reference

Query workflow

Scenario Discovery uses an asynchronous query model with three API operations:

You can also use CancelQuery to cancel a running query, and ListQueries to list queries for a workspace.

Example workflow

# 1. Start a query
aws iotsitewise start-query \
  --region $REGION \
  --workspace-name "my-workspace" \
  --query-statement "SELECT * FROM raw_time_series LIMIT 10"
# Response: { "queryId": "abc-123", "status": "SUBMITTED" }

# 2. Poll for completion
aws iotsitewise describe-query \
  --region $REGION \
  --workspace-name "my-workspace" \
  --query-id "abc-123"
# Response: { "queryId": "abc-123", "status": "COMPLETED", ... }

# 3. Get results (paginated)
aws iotsitewise get-query-results \
  --region $REGION \
  --workspace-name "my-workspace" \
  --query-id "abc-123" \
  --max-results 10000
# Response: { "columnInfo": [...], "rows": [...], "nextToken": "..." }

Query constraints

Query statuses

Reference tables

Scenario Discovery provides four queryable tables within each workspace. All queries are scoped to the workspace specified in the StartQuery request.

raw_time_series

Contains timestamped telemetry data ingested into the workspace.

annotations

Contains OpenLABEL annotation data associated with video or sensor streams. Each row corresponds to the annotation data for a single video frame.

dataset

Contains metadata about datasets in the workspace.

datasegment

Contains metadata about data segments (timeseries references) within datasets.

Supported data types

Timestamp formats:

TIMESTAMP '2025-12-21 23:59:58.123'
TIMESTAMP '2025-12-21 23:59:58'
TIMESTAMP '2025-12-21'

Supported SQL clauses

SELECT expression [, ...]
  [ FROM table_name [AS alias] [, ...] ]
  [ WHERE condition ]
  [ GROUP BY expression [, ...] ]
  [ HAVING condition ]
  [ ORDER BY expression [ ASC | DESC ] [ NULLS FIRST | NULLS LAST ] [, ...] ]
  [ LIMIT count ]

Variant functions and dot notation

Scenario Discovery supports two approaches for working with VARIANT columns (semi-structured JSON data).

Dot notation (recommended for simple access)

Use dot notation to access nested fields directly on VARIANT columns:

-- Access nested struct fields
SELECT t.struct_value.observation.state.max_speed
FROM raw_time_series t
LIMIT 10

-- Filter on nested values
SELECT DISTINCT t.dataset_id, t.series_id, t.event_timestamp
FROM raw_time_series t
WHERE CAST(t.struct_value.observation.state.max_speed AS INT) > 0

-- Access annotation context fields
SELECT a.contexts.road_type, a.contexts.weather
FROM annotations a
LIMIT 10

variant_extract

Extracts a value from a variant object by key name. Returns a VARIANT that you can further access or cast.

Signature: variant_extract(variant_expr, key)

SELECT
    variant_extract(obj, 'object_type'),
    variant_extract(obj, 'object_name'),
    variant_extract_nested(obj, 'attributes.confidence')
FROM annotations a,
UNNEST(CAST(a.objects AS VARIANT ARRAY)) AS t(obj)
WHERE a.dataset_id = 'my-dataset-id'
LIMIT 50

variant_extract_nested

Extracts a nested value using a dot-separated path string. The second parameter must be a string literal.

Signature: variant_extract_nested(variant_expr, 'dotted.path.key')

SELECT
    variant_extract(obj, 'object_type'),
    variant_extract_nested(obj, 'attributes.confidence')
FROM annotations a,
UNNEST(CAST(a.objects AS VARIANT ARRAY)) AS t(obj)
WHERE a.dataset_id = 'my-dataset-id'
    AND CAST(variant_extract_nested(obj, 'attributes.confidence') AS DOUBLE) >= 0.7
LIMIT 50

to_json

Converts a variant value to a JSON string.

Signature: to_json(variant_expr)

SELECT to_json(a.objects) FROM annotations a LIMIT 5

UNNEST for arrays

Use UNNEST to expand VARIANT arrays into rows:

SELECT
    a.dataset_id,
    a.series_id,
    a.frame_id,
    a.event_timestamp,
    variant_extract(obj, 'object_type'),
    variant_extract(obj, 'object_name'),
    variant_extract_nested(obj, 'attributes.confidence')
FROM annotations a,
UNNEST(CAST(a.objects AS VARIANT ARRAY)) AS t(obj)
WHERE a.dataset_id = 'my-dataset-id'
    AND CAST(variant_extract_nested(obj, 'attributes.confidence') AS DOUBLE) >= 0.7
ORDER BY a.dataset_id
LIMIT 50

Operators and functions

Logical operators

Comparison operators

Aggregate functions

Type conversion

Example queries

Basic telemetry queries

List all telemetry data (limited):

SELECT * FROM raw_time_series LIMIT 10

Filter by time range:

SELECT alias, event_timestamp, double_value
FROM raw_time_series
WHERE event_timestamp > TIMESTAMP '2025-01-01 00:00:00'
  AND event_timestamp < TIMESTAMP '2025-01-02 00:00:00'
LIMIT 100

Aggregate telemetry values:

SELECT
    alias,
    COUNT(*) AS data_points,
    AVG(double_value) AS avg_value,
    MAX(double_value) AS max_value,
    MIN(double_value) AS min_value
FROM raw_time_series
GROUP BY alias
ORDER BY data_points DESC

Query struct telemetry using dot notation:

SELECT DISTINCT t.dataset_id, t.series_id, t.event_timestamp
FROM raw_time_series t
WHERE CAST(t.struct_value.observation.state.max_speed AS INT) > 0

Annotation queries

List annotations for a dataset:

SELECT dataset_id, series_id, frame_id, event_timestamp
FROM annotations
WHERE dataset_id = 'my-dataset-id'
ORDER BY event_timestamp
LIMIT 100

Extract detected objects with confidence filtering:

SELECT
    a.dataset_id,
    a.series_id,
    a.frame_id,
    a.event_timestamp,
    variant_extract(obj, 'object_type'),
    variant_extract(obj, 'object_name'),
    variant_extract_nested(obj, 'attributes.confidence')
FROM annotations a,
UNNEST(CAST(a.objects AS VARIANT ARRAY)) AS t(obj)
WHERE a.dataset_id = 'my-dataset-id'
    AND CAST(variant_extract_nested(obj, 'attributes.confidence') AS DOUBLE) >= 0.7
ORDER BY a.event_timestamp
LIMIT 50

Query annotation contexts using dot notation:

SELECT
    a.dataset_id,
    a.frame_id,
    a.contexts.road_type,
    a.contexts.weather,
    a.contexts.time_of_day
FROM annotations a
WHERE a.dataset_id = 'my-dataset-id'
LIMIT 100

Dataset and data segment queries

List all datasets:

SELECT dataset_id, name, dataset_type, version, created_at
FROM dataset
ORDER BY created_at DESC

List data segments for a dataset:

SELECT source_dataset_id, dataset_id, timeseries_id, alias, data_type, start_time, end_time
FROM datasegment
WHERE dataset_id = 'my-dataset-id'

Count data segments by type:

SELECT data_type, COUNT(*) AS segment_count
FROM datasegment
GROUP BY data_type

Cross-table queries

Find telemetry data for a specific dataset's segments:

SELECT t.alias, t.event_timestamp, t.double_value
FROM raw_time_series t, datasegment ds
WHERE t.dataset_id = ds.dataset_id
  AND t.series_id = ds.timeseries_id
  AND ds.data_type = 'JSON'
LIMIT 100

Troubleshooting

ValidationException on StartQuery

Query stuck in RUNNING

ResourceNotFoundException on GetQueryResults

Query returns FAILED

Empty results

Slow pagination