Querying Nested JSON

Telemetry turns nested JSON objects into queryable dotted field paths. That keeps related context together at ingestion time while still making individual values available to SQL.

This guide covers the full path from an event contract to filters, aggregates, schema changes, and troubleshooting. Inspect the table schema before copying a query: the exact identifier spelling and type come from the events you sent.

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. Depending on the table schema, a nested path can appear as a compound identifier or require one double-quoted dotted identifier:

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;

If the schema exposes a literal dotted column name, quote the complete path:

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

Do not switch between quoted and unquoted forms by guesswork. Check the table schema, run a small sample, and use the form that matches the stored field.

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;

If your table uses quoted dotted identifiers, quote each complete path in the same query:

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. Try the schema's quoted identifier form rather than adding JSON-extraction syntax from another SQL dialect.
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 DataFusion 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.