---
title: Command Reference
description: Full ROSQL language reference — every statement, clause, and operator
sidebar_position: 8
keywords: [rosql, language, syntax, reference, commands, statements, clauses]
---

# Command Reference

Complete reference for every ROSQL statement and clause.

---

## Data source statements

### `FROM <source>`

Select a data source. The primary entry point for most queries.

```sql
FROM traces
FROM logs
FROM metrics
FROM topics
FROM recordings
```

Sources:
- `traces` — `otel_traces` table
- `logs` — `otel_logs` table
- `metrics` — `otel_metrics` table
- `topics` — `topic_messages` table
- `recordings` — `mcap_metadata` table
- `odom`, `joint_states`, `battery`, `cmd_vel`, `imu` — topic aliases

---

### `SELECT <fields> FROM <source>`

Select specific fields.

```sql
SELECT trace_id, span_name, duration FROM traces WHERE status = 'ERROR'
SELECT AVG(duration) AS avg_dur, COUNT(*) AS total FROM traces
SELECT PERCENTILE(duration, 95) FROM traces WHERE action_name = '/navigate_to_pose'
```

Aggregate functions: `AVG`, `COUNT`, `MAX`, `MIN`, `SUM`, `PERCENTILE`

---

## Filtering

### `WHERE <condition>`

Filter rows. Supports standard comparison operators, `AND`, `OR`, `IN`, field access.

```sql
FROM traces WHERE status = 'ERROR'
FROM traces WHERE duration > 500 ms AND status = 'ERROR'
FROM logs WHERE severity IN ('ERROR', 'WARN')
FROM topics WHERE topic_name = '/battery_state' AND fields['percentage'] < 20
```

**Duration units** — ROSQL automatically converts to nanoseconds:
- `500 ms` → `500000000`
- `2 s` → `2000000000`
- `1 min` → `60000000000`

**JSON field access**: `fields['key']` on `topic_messages.fields` (JSONB)

---

### `SINCE <time>`

Filter by timestamp (relative or absolute).

```sql
FROM traces WHERE status = 'ERROR' SINCE 1 hour ago
FROM traces SINCE 30 minutes ago
FROM traces SINCE yesterday
FROM traces SINCE last week
FROM traces SINCE '2025-03-25T00:00:00Z'
```

Relative tokens: `X minutes ago`, `X hours ago`, `X days ago`, `yesterday`, `last week`, `last month`

---

### `LIMIT <n>`

Limit result rows.

```sql
FROM traces LIMIT 10
```

Default limit: 100.

---

### `ORDER BY <field> [DESC|ASC]`

Sort results.

```sql
SELECT span_name, duration FROM traces ORDER BY duration DESC LIMIT 20
```

---

## Scoping

### `FOR ROBOT <robot_id>`

Scope a query to a specific robot.

```sql
FOR ROBOT 'robot_sim_001' FROM logs WHERE severity = 'ERROR'
FOR ROBOT 'robot_42' HEALTH() SINCE 30 minutes ago
```

Equivalent to filtering by a `robot_id` attribute.

---

## Cross-signal correlation

### `DURING(<subquery>)`

Correlate one data source with a time window defined by another. The outer query is filtered to rows whose timestamp falls within the time windows where the subquery returned results.

```sql
FROM traces WHERE status = 'ERROR'
DURING(
  FROM topics WHERE topic_name = '/battery_state'
  AND fields['percentage'] < 15
)
SINCE 6 hours ago
```

The subquery can use any data source. This is ROSQL's most powerful feature — it replaces complex multi-table JOINs with a declarative, readable correlation.

---

## Message causality

### `MESSAGE JOURNEY FOR TRACE <trace_id>`

Walk the `parent_span_id → span_id` causal chain recursively and return all spans in the causality tree.

```sql
MESSAGE JOURNEY FOR TRACE 'a3f1c9d2e8b04f7a'
```

Returns all columns from `otel_traces` for every span in the causal chain, in traversal order.

---

### `MESSAGE PATHS FOR TOPIC <topic_name>`

Find all message paths (publish → subscribe edges) for a given topic.

```sql
MESSAGE PATHS FOR TOPIC '/cmd_vel'
```

Returns publisher and subscriber node pairs for the topic.

---

### `TRACE <trace_id>`

Return all spans belonging to a specific trace ID.

```sql
TRACE 'trace-002'
```

Equivalent to `FROM traces WHERE trace_id = '...'` but with semantic clarity.

---

## Health and anomaly

### `HEALTH() [FOR ROBOT <robot_id>] [SINCE <time>]`

Fan out across traces, logs, and metrics to produce a composite health assessment.

```sql
HEALTH() FOR ROBOT 'robot_sim_001'
HEALTH() FOR ROBOT 'robot_42' SINCE 30 minutes ago
```

Returns a structured health report including error rates, log severity counts, and metric status.

---

### `ANOMALY(<field>)`

Detect statistical anomalies in a field using z-score analysis (AVG/STDDEV).

```sql
ANOMALY(duration)
```

Returns spans whose `duration` deviates significantly from the mean.

---

### `PATH DEVIATION FOR ROBOT <robot_id>`

Detect if a robot deviated from its planned path by querying `/odom` trajectory data.

```sql
PATH DEVIATION FOR ROBOT 'robot_sim_001'
```

Requires the `topic_messages` table with `/odom` data.

---

## Recordings

### `SHOW RECORDING`

Find MCAP recordings that cover the most recent event window.

```sql
SHOW RECORDING
```

Returns `robot_id`, `session_id`, `start_time`, `end_time`, `s3_key`, `topics` from `mcap_metadata`.

---

## Pipeline syntax

Use `|` to chain query stages, making complex queries more readable.

```sql
FROM traces
| WHERE duration > 500 ms
| WHERE status = 'ERROR'
| FACET robot_id
| COMPARE TO last week
```

Each stage receives the output of the previous stage. Equivalent to nesting clauses.

---

## Grouping and comparison

### `FACET <field>`

Group results by a field.

```sql
SELECT AVG(duration) FROM traces FACET robot_id
FROM traces WHERE status = 'ERROR' | FACET robot_id
```

### `COMPARE TO <time>`

Compare current results to a historical baseline.

```sql
FROM traces WHERE status = 'ERROR' SINCE 1 hour ago | COMPARE TO last week
```

Returns current and baseline counts side by side.

---

## Notes

- All timestamps in results are UTC ISO 8601
- `duration` is stored as nanoseconds (`BIGINT`); ROSQL expressions like `500 ms` are automatically converted
- Default `LIMIT` is 100 if not specified
- `SINCE` is always relative to query execution time
