Querying nested JSON

Telemetry stores nested JSON objects as typed Arrow structs. Access their fields through paths such as tool.name; a quoted identifier like "tool.name" instead means a single column whose literal name contains a dot. See the nested data reference.

The examples cover filters, aggregates, schema changes, and troubleshooting. Inspect the table schema before copying a query. The events you sent determine each column's spelling and type.

Design a stable nested event

Use nested objects when the fields form one durable concept. Keep values typed, omit sensitive payloads, and avoid placing frequently changing structures into an array.

{
  "event_name": "tool_call_completed",
  "event_id": "evt_7f31",
  "account_id": "acct_8f31",
  "release": "2026.07.3",
  "workflow": {
    "name": "answer_question",
    "version": "v2"
  },
  "tool": {
    "name": "inventory_lookup",
    "outcome": "success",
    "duration_ms": 184,
    "usage": {
      "input_units": 820,
      "output_units": 244
    }
  }
}

The row grain is one completed tool call. tool.duration_ms is always numeric, tool.outcome comes from a controlled set, and identifiers are pseudonymous. Request arguments, model prompts, generated content, credentials, and raw error messages are deliberately absent.

Send the object through the SDK:

await telemetry.log("tool_call_completed", event);

The Log API adds the managed event time used in queries and recursively removes null values, empty objects, and empty arrays. Read event data types and nullability before using an empty value as a business state.

Inspect before you aggregate

Start with a bounded sample. For the nested objects in the event above, use struct field paths:

SELECT
  timestamp_utc,
  event_id,
  workflow.name,
  tool.name,
  tool.outcome,
  tool.duration_ms
FROM tool_call_completed
WHERE timestamp_utc >= now() - INTERVAL '1 hour'
ORDER BY timestamp_utc DESC
LIMIT 50;

Only if your schema has separate top-level columns whose literal names contain dots, quote each complete column name. This example does not access the nested objects above:

SELECT
  "workflow.name",
  "tool.name",
  "tool.duration_ms"
FROM tool_call_completed
LIMIT 50;

Double quotes do not turn a missing struct field into a valid nested path. Inspect the schema and preserve field-name case; quote individual path components when needed, such as "tool"."name".

Filter and aggregate nested fields

Nested fields work in filters, groups, calculations, and ordering. This query compares tool volume, failures, and p95 duration over a complete, bounded window:

SELECT
  tool.name AS tool_name,
  COUNT(*) AS calls,
  SUM(CASE WHEN tool.outcome = 'error' THEN 1 ELSE 0 END) AS errors,
  100.0 * SUM(CASE WHEN tool.outcome = 'error' THEN 1 ELSE 0 END)
    / NULLIF(COUNT(*), 0) AS error_rate_pct,
  approx_percentile_cont(tool.duration_ms, 0.95) AS p95_duration_ms
FROM tool_call_completed
WHERE timestamp_utc >= now() - INTERVAL '7 days'
  AND workflow.name = 'answer_question'
GROUP BY tool.name
HAVING COUNT(*) >= 20
ORDER BY error_rate_pct DESC, calls DESC;

The following query applies only to separate top-level columns named literally tool.name, tool.duration_ms, and workflow.name, not the nested event above:

SELECT
  "tool.name" AS tool_name,
  approx_percentile_cont("tool.duration_ms", 0.95) AS p95_duration_ms
FROM tool_call_completed
WHERE "workflow.name" = 'answer_question'
GROUP BY "tool.name";

Handle missing paths deliberately

An older row created before tool.usage.output_units was introduced will not have that field. A new event with a null, empty-object, or empty-array value also stores no value for that path after normalization.

Use IS NULL to measure coverage before relying on a new field:

SELECT
  release,
  COUNT(*) AS calls,
  SUM(CASE WHEN tool.usage.output_units IS NULL THEN 1 ELSE 0 END)
    AS missing_output_units,
  100.0 * SUM(CASE WHEN tool.usage.output_units IS NULL THEN 1 ELSE 0 END)
    / NULLIF(COUNT(*), 0) AS missing_rate_pct
FROM tool_call_completed
WHERE timestamp_utc >= now() - INTERVAL '24 hours'
GROUP BY release
ORDER BY release;

Do not replace a missing number with zero unless zero is the correct business meaning. "Not reported," "not applicable," and an actual measured zero are different states.

Evolve nested paths safely

Treat each dotted path as a schema contract:

Keep the same meaning and type at every depth. Adding an optional nested field is usually compatible; changing a path's type or reusing it for a new meaning is not.

Decide when to flatten

Nested objects are useful for stable namespaces such as tool, workflow, or billing. A flat field can be better when it is used in nearly every query or dashboard.

Prefer a flat or separately emitted field when:

• the value defines the row grain or primary event outcome;
• operators must scan it in nearly every investigation;
• several producers cannot agree on one nested structure;
• an array really represents multiple independent outcomes.

Changing from nested to flat later is a schema migration. Choose based on the questions and ownership boundaries, not on payload aesthetics.

Troubleshoot a nested-field query

If a query cannot find a nested path:

1. Query a recent raw sample with LIMIT 50.
2. Inspect the table schema for the exact dotted name and type.
3. Use a struct path such as tool.name or tool['name']; use "tool.name" only for a literal top-level dotted column. Check the syntax reference.
4. Confirm the producer actually sent a non-null, non-empty value.
5. Group missing values by release or producer version.
6. Check for a type change between old and new producers.
7. Reduce the query to one field and one recent time window before restoring joins or aggregates.

Telemetry uses DataFusion SQL, so PostgreSQL, BigQuery, Snowflake, or MySQL JSON functions copied from another system may not apply. Use the syntax exercised in the Telemetry SQL reference.

Production checklist

• Give every nested object one durable meaning and owner.
• Keep every path's type, units, and controlled values stable.
• Exclude secrets, user content, raw payloads, and unbounded error text.
• Test success, failure, missing-field, and old-version fixtures.
• Measure new-field adoption before making a dashboard or alert depend on it.
• Document the row grain, retention need, and migration plan.

Continue with designing an event schema, schema evolution, and the required-field null-rate recipe.