Queries

Query expressions return data for a block. Use one of these common forms:

@expr/query — returns a table scoped to the page’s time range, filters, and parameters. Use it for stats, tables, and most charts.
@expr/timeseries_query — returns one row per time bucket and adds the _time_bucket column. Use it for plots over time.

The rest — @expr/range_query, @expr/unbound_query, @expr/autocomplete — cover the cases the defaults don’t fit. The exact fields live in the expression reference; this page is about how to drive them.

Workspace workflow catalog

{ "@expr/workflows": {} } returns a table of the current workspace’s resolved workflow definitions. Templates and overlays are applied. Columns are ref, name, path, enabled, scheduled, and detection; the last three are booleans. Disabled and unscheduled definitions remain in the table so a query can distinguish them from removed definitions. A detection includes the detect spelling and its top-level variants; a steps workflow is not a declarative detection.

Use the table as a query source or join it to run records by ref. Discovery errors fail the expression, including when some other definitions are valid. An empty workspace returns a zero-row table with the same column types.

Scheduler, manual-run, validation, and replay hosts supply the catalog. The replay catalog contains the definitions published for that replay. A workflow step checkpoints the table it reads; resuming that checkpoint does not reread the catalog. The next occurrence reads the current listing. Catalog reads are subject to the run’s deadline; a host without a workspace catalog fails the expression.

Query execution options

The object forms of @expr/query, @expr/range_query, @expr/timeseries_query, and @expr/unbound_query accept settings, keyed by database dialect:

{
  "@expr/query": {
    "sql": "SELECT l.id AS id, r.value AS value FROM l LEFT JOIN r ON l.id = r.id",
    "settings": { "clickhouse": { "join_use_nulls": 1 } }
  }
}

settings overrides connection defaults and typed options for the statement. Values are literal strings, numbers, or booleans. Noemata applies settings for the active database dialect. A setting the local executor cannot model sends the query to the database.

The same object forms accept typed execution options:

timeout: { "ms": 100, "on_timeout": "partial" } sets the server execution budget. "partial" allows rows produced before the budget expires; "reject" requires completion. ClickHouse maps these to max_execution_time (seconds) and timeout_overflow_mode (break or throw). ms: 0 disables ClickHouse’s server time limit. This does not change the configured client request timeout.
all_columns: true includes queryable columns normally omitted by SELECT *. ClickHouse enables asterisk_include_alias_columns and asterisk_include_materialized_columns. Omitted or false values leave configured settings unchanged.

The driver resolves options in this order: defaults, deployment query_settings, typed statement options, then statement settings. The remote cache uses the resolved settings. Explicit timeouts and expanded columns require remote execution.

ClickHouse data queries run with readonly: 2 and cancel_http_readonly_queries_on_client_close: 1 by default. A query therefore stops on the server when its last Noemata subscriber disconnects or its owning workflow reaches its deadline. Administrative, file-store, and ingestion statements use separate clients and remain writable.

Declare execution options at the statement root. Composed tagged subqueries cannot declare settings, timeout, or all_columns. Separately evaluated @expr/query inputs have their own statement settings. Generated workflow joins carry internal requirements; an explicit root setting that conflicts with a requirement is rejected.

ClickHouse defaults to join_use_nulls: 0 in Noemata. Set it to 1 when an outer join must fill missing scalar values with SQL NULL. Generated detection workflow joins request 1 explicitly.

Scalar WITH bindings can execute locally when their expressions are supported. ClickHouse bindings are visible in nested SELECTs by default; set enable_global_with_statement: 0 for local scope only. In a nested SELECT, a source column or a SELECT alias with the same name as an inherited binding is read instead of the binding, as ClickHouse does, including in a join’s ON condition. A query over a context table can therefore select columns named like view scalars and still run locally. Cycles, a binding named like a column of a table, aliased subquery, or join in the same SELECT, SELECT aliases that conflict with a binding declared by the same SELECT, ORDER BY a binding, and expressions whose errors cannot be preserved remain database queries.

UTC timestamp arithmetic with integral SECOND, MINUTE, HOUR, DAY, MONTH, and YEAR intervals can execute locally. Month and year intervals preserve the UTC time of day and clamp month ends. Interval arithmetic over non-UTC timestamps remains remote unless the expression explicitly normalizes the timestamp to UTC. ClickHouse uses session_timezone: UTC unless overridden, and external timestamp columns retain their declared time zones.

ClickHouse defaults to cast_keep_nullable: 0 in Noemata. CAST(nullable_numeric_column AS Float64) rejects NULL rows; set cast_keep_nullable: 1 to preserve source nullability. toFloat64(column) preserves NULL regardless of that setting.

Two ways to write a query

As a SQL string. The quickest form — write SQL against your view. Reference context with {name:Type} placeholders; they bind from the surrounding parameters and filters without any manual substitution:

{ "@expr/query": "SELECT count() FROM requests WHERE ServiceName = {service_name:String}" }

If you’d rather pass values explicitly than inherit them from context, use the { sql, parameters } form:

{
  "@expr/query": {
    "sql": "SELECT count() FROM requests WHERE ServiceName = {name:String}",
    "parameters": { "name": "checkout" }
  }
}

As a structured query. The object form — from, select, where, group_by, … — is easier to build up programmatically and to compose. select is a map of output column to SQL expression:

{
  "@expr/query": {
    "from": "traces",
    "select": { "as": { "p95": "quantile(0.95)(Duration)" } },
    "group_by": "ServiceName"
  }
}

In the structured form, from can itself be another query — nest one to layer a transformation over a base query (or over a shared @expr/use template) without writing a subquery by hand. Write the nested query in its tagged form — the bare tag name (query, range_query, timeseries_query, unbound_query), no @expr/ prefix — and it composes as a sub-query in a single statement:

{
  "@expr/query": {
    "from": { "query": "SELECT ServiceName, Duration FROM traces" },
    "select": { "as": { "p95": "quantile(0.95)(Duration)" } },
    "group_by": "ServiceName"
  }
}

The tag still shapes each nested level’s window — range_query requires a range, timeseries_query buckets, unbound_query carries its own bounds — so every level’s macros expand against its own context. Tag every level of a chain: a prefixed @expr/query left in a nested from is evaluated at that level (its full result shipped back and re-injected as data) instead of composed, which for a large intermediate result can overflow the request body — so a deep chain composes end-to-end only when each inner query is tagged.

A prefixed @expr/query in from means something different: it is evaluated to a table first and injected as data rather than composed into the SQL. Reach for the tagged form ({ "query": … }) when you want the database to do the work in one query with pushdown; reach for the expression form ({ "@expr/query": … }) when you already have a table to feed in — a context value, inline data, or a query whose full result you want to materialise and then slice locally.

A join target also accepts an evaluated expression. The client reads a reference table from a workspace file and sends it with the query as an external table. This query prices each instance family’s CPU using the @opentelemetry pricing table:

{
  "@expr/query": {
    "from": {
      "query": {
        "from": "thread_cpu",
        "select": ["HostFamily", "CloudRegion", "ProcessCpuSeconds"],
        "group_by": ["HostFamily", "CloudRegion"]
      }
    },
    "as": "cpu",
    "left_join": {
      "from": { "@expr/from_arrow": { "file": "@frames/@opentelemetry/cloud_pricing.arrow" } },
      "as": "price",
      "on": "price.family = cpu.HostFamily AND price.region = cpu.CloudRegion AND price.category = 'compute'"
    },
    "select": { "as": { "Dollars": "sum(cpu.ProcessCpuSeconds / 3600 * price.price_usd)" } }
  }
}

An Arrow file’s columns arrive nullable, so an unmatched row reads NULL in price.price_usd whatever join_use_nulls says. @expr/paired_query takes a table name as from and has no join, so a joined reading has no previous-period pair.

select, where, group_by, order_by, and limit can each take an @expr/* expression. Noemata resolves the expression before compiling the query. In a select map, the expression can provide a column name while the as key remains the output name: { "as": { "Metric": { "@expr/get": "metric" } } } selects the column named by metric and aliases it to Metric.

{
  "@expr/query": {
    "from": "metrics_sum",
    "select": [{ "as": { "dow": "toDayOfWeek(TimeUnix)", "Metric": { "@expr/get": "metric" } } }],
    "group_by": "dow"
  }
}

Each group_by dimension can also be an expression. Use this form to make one dimension depend on a control while leaving the other dimensions fixed:

{
  "@expr/query": {
    "from": "traces",
    "select": { "as": { "requests": "count()" } },
    "group_by": ["ServiceName", { "@expr/get": "breakdown" }]
  }
}

A where that resolves to an empty string drops the clause — that is how a control spells “no filter” — and a group_by that resolves to nothing drops the grouping the same way:

{
  "@expr/query": {
    "from": "logs",
    "select": { "as": { "events": "count()" } },
    "where": {
      "@expr/case": { "scope": { "Errors only": "SeverityText = 'ERROR'", "default": "" } }
    }
  }
}

Anything that isn’t SQL text is an error in where rather than being ignored: a predicate that silently disappeared would widen the result set, showing rows you meant to exclude. A join’s on stays literal — a join condition is structure rather than a filter.

To reference the same subquery more than once — say either side of a self-join — name it as a CTE under with instead of inlining it twice. A cte body takes the same shapes as from: raw SQL or a tagged nested query.

{
  "@expr/query": {
    "with": { "procs": { "cte": { "query": "SELECT pid, ppid, name FROM processes" } } },
    "from": "procs",
    "as": "parent",
    "left_join": { "from": "procs", "as": "child", "on": "parent.pid = child.ppid" },
    "select": { "as": { "parent_name": "parent.name", "children": "count(child.pid)" } },
    "group_by": "parent.name"
  }
}

A string is SQL; `{ identifier }` is one name

In a value position — a select item or as map value, group_by, order_by, a window’s partition_by — a string is spliced as SQL. So parent.name above is a qualification (the name column of the relation aliased parent), and count(child.pid) is a call.

To name a single column instead of writing SQL, use { "identifier": … }. The dialect quotes it, which reaches names SQL text cannot: a flattened attribute key like http.status_code (as text that reads as the status_code column of a relation http), a name holding a space, or one spelling a reserved word.

{
  "@expr/query": {
    "from": "logs",
    "select": [{ "identifier": "http.status_code" }, { "as": { "requests": "count()" } }],
    "group_by": { "identifier": "http.status_code" }
  }
}

Sorting: by name, or by an expression

order_by takes three forms. { "by": { "Requests": "DESC" } } sorts by columns the query produced — each name is quoted, so it reaches a column SQL text could not spell. A bare string is SQL, sorted ascending. To sort by an expression in a stated direction, pair them:

{
  "@expr/query": {
    "from": "traces",
    "select": { "as": { "ServiceName": "ServiceName", "requests": "count()" } },
    "group_by": "ServiceName",
    "order_by": ["count()", { "desc": {} }]
  }
}

The by form cannot express that: its keys are names, and an unaliased aggregate has none — ORDER BY "count()" names a column the query never produced. Either alias the aggregate in the select and sort by that alias, or pair the expression with a direction as above.

Names live under by (and { "all": {} } sorts by every projected column) so that a bare object is always an expression: { "identifier": "desc" } sorts by the column named desc, never by a column named identifier.

A group_by dimension the select doesn’t project is added to it for you, so charts and tables can read the dimension back by name. It goes in exactly as you wrote it, so the database names the column — grouping by m.MetricName gives one called MetricName. When you want to fix the name, project the dimension through as yourself; that also suppresses the automatic prepend:

{ "select": { "as": { "MetricName": "m.MetricName" } }, "group_by": "m.MetricName" }

Output names live under as, so a bare object in a select item is always a value expression and no name is reserved: { "as": { "identifier": "ServiceName" } } selects ServiceName AS "identifier", while { "identifier": "ServiceName" } selects the column of that name.

`grouping_sets`

For several groupings of the same source in a single scan, group_by takes a { "grouping_sets": … } object with two shapes. The map form names each set:

{
  "@expr/query": {
    "from": "spans",
    "select": [{ "as": { "grouping": { "grouping": {} }, "total": "sum(Duration)" } }],
    "group_by": {
      "grouping_sets": {
        "by_service": ["ServiceName"],
        "total": []
      }
    }
  }
}

The positional form is an array of sets and yields the set index as the label:

{ "group_by": { "grouping_sets": [["ServiceName"], []] } }

The { grouping: {} } placeholder in a select value lowers to a CASE over the bitmask of the union of dimensions and projects the map key (or the zero-based index, as a string, on the positional form). ClickHouse’s own GROUPING(<col>) written as SQL text passes through unchanged, so both surfaces are reachable — the placeholder is a distinct authored shape rather than a magic string. Two sets with the same dimensions are rejected: their rows share a bitmask, so no label distinguishes them at result time.

The union of dimensions is prepended to the select so every dimension appears as a column; a row from a set that omits the dimension carries the column type’s default (an empty string for String, zero for numerics). Use Nullable(<T>) on the source column when the omitted-set row should carry real NULLs, or filter by the projected { grouping: {} } column rather than by dimension value.

To read one set inside the same statement, wrap the grouping-sets query as a CTE and filter on the label. The outer select then returns the shape a plain group_by returns:

{
  "with": {
    "all_metrics": {
      "cte": {
        "@expr/query": {
          "from": "spans",
          "select": [{ "as": { "grouping": { "grouping": {} }, "total": "sum(Duration)" } }],
          "group_by": { "grouping_sets": { "by_service": ["ServiceName"], "total": [] } }
        }
      }
    }
  },
  "from": "all_metrics",
  "where": "grouping = 'by_service'",
  "select": ["ServiceName", "total"]
}

Reading sets in separate blocks

A CTE belongs to one statement, so blocks that each read a different set through a CTE each run the grouping-sets query. To run it once, put it in a @block/context extend and read each set from the shared table with @expr/grouping_set:

{
  "@block/context": {
    "extend": {
      "runs": {
        "@expr/query": {
          "from": "workflow_runs",
          "select": [
            {
              "as": {
                "grouping": { "grouping": {} },
                "runs": "count()",
                "failed": "countIf(Status = 'failed')"
              }
            }
          ],
          "group_by": {
            "grouping_sets": {
              "totals": [],
              "by_workflow": ["WorkflowRef"],
              "by_cause": ["Cause"]
            }
          }
        }
      }
    },
    "block": {
      "@block/table": {
        "from": {
          "@expr/grouping_set": {
            "from": { "@expr/get": "runs" },
            "set": "by_workflow",
            "select": ["WorkflowRef", "runs"],
            "order_by": { "by": { "runs": "DESC" } },
            "limit": 5
          }
        }
      }
    }
  }
}

set is a map key, or a zero-based index on the positional form: "set": 1 reads the second set. grouping is the column that holds the label and defaults to grouping. When the source table has no label column, the expression fails with an error that names the column it looked for.

The other options are a query body (select, where, order_by, limit, with, a join) and mean what they mean in @expr/query. @expr/grouping_set runs one query over the shared table: an inner WHERE grouping = '<set>' selects the set’s rows, the body runs over those rows, and the label column is dropped from the result. That query does not apply the page’s time range or filters, because the shared table was computed under them, and the result keeps the shared table’s time window. The in-memory planner runs that query when it supports every clause, in the SQL worker where one is configured. Otherwise the database runs it, with the shared table uploaded as an external table. Without a body, the result is the set’s rows with the column types, formats, and labels of the shared table.

The rows still contain the columns of dimensions that only other sets group by, filled with type defaults. List the columns you want in select.

In a pipeline, @expr/grouping_set reads the incoming table:

{ "@expr/pipeline": [{ "@expr/get": "runs" }, { "@expr/grouping_set": { "set": "totals" } }] }

Filtering one set

A grouping set cannot have its own where. GROUP BY GROUPING SETS adds each input row to the aggregates of every set in one pass, so a row-level -If condition applies to all sets alike. To aggregate only some rows for one set, add an aggregate with that condition ("failed": "countIf(Status = 'failed')" above) and read it from that set. Every set computes every aggregate in the select.

Whole-window and bucketed sets together

@expr/timeseries_query adds _time_bucket to every grouping set, so every row has a bucket. To combine a set over the whole window with bucketed sets in one statement, use @expr/query with an explicit resolution, and list _time_bucket only in the bucketed sets:

{
  "@expr/query": {
    "from": "spans",
    "resolution": "high",
    "select": [{ "as": { "grouping": { "grouping": {} }, "total": "sum(Duration)" } }],
    "group_by": {
      "grouping_sets": { "window_total": [], "per_bucket": ["_time_bucket"] }
    }
  }
}

Rows of the whole-window set hold the default value in _time_bucket. @expr/grouping_set keeps that column like any other dimension column, so leave it out of the reader’s select.

`resolution` and `transform`

Alongside the body, the four query expressions take two options that shape the window a query runs over: resolution decides how finely it’s bucketed, and transform decides which window is queried. resolution is a query-level knob and sits next to from/select (or next to sql); transform is an adjustment to the window, so it lives under a timerange key:

{
  "@expr/timeseries_query": {
    "from": "spans",
    "select": { "as": { "requests": "count()" } },
    "resolution": "low",
    "timerange": { "transform": { "shift": "previous_period" } }
  }
}

@expr/query, @expr/range_query, and @expr/timeseries_query take exactly these two. @expr/unbound_query takes them too — and since it ignores the surrounding range, its timerange also carries the explicit start/end bounds.

`resolution`

How finely the window is bucketed: "low" aims for ~12 buckets, "high" for ~64. The bucket size is derived from the active range and snapped to a round interval, so the same resolution gives coarser buckets on a wider window — a chart keeps its shape as you zoom out instead of growing more points.

@expr/timeseries_query defaults to "high"; drop to "low" for a sparkline or a small multiple, where 64 points is just noise. The other three set no resolution unless you ask for one — and without one there’s no _time_bucket to group by.

A third value, "none", states “no bucketing” outright — the same effect as omitting resolution, but explicit, so an expression can resolve to it. @expr/query, @expr/range_query, and @expr/unbound_query all accept it; @expr/timeseries_query rejects it, since a timeseries is defined by its buckets (reach for @expr/query when you want a single whole-window reading). Like the query body’s other options, resolution can itself be an @expr/* expression, so a chart’s granularity can follow a control.

Bounding the bucket width

Because a level targets a bucket count, the width it produces shrinks as the window narrows — and a bucket narrower than the source’s emission cadence holds at most one sample. Anything that needs two samples to mean something (a counter delta, an observed span) is then undefined, and the chart goes blank on short windows. Write the level as an object to put bounds on the width it may resolve to:

{
  "@expr/timeseries_query": {
    "from": "metrics_sum",
    "select": { "as": { "Rate": "CounterRate" } },
    "resolution": { "high": { "min_interval": { "value": 1, "unit": "minutes" } } }
  }
}

min_interval floors the width and max_interval caps it; both take a { value, unit } interval whose value is a positive whole number, and either may be given alone. Nothing else may sit beside the level — a bound written one level too high ({ "high": {}, "min_interval": … }) is rejected rather than silently ignored. A bound only binds when the derived width would cross it — at a wide window the level still picks its own width, so this costs no fidelity where none is needed. Floor a metric at a small multiple of its scrape interval (a 30s scrape needs at least 1m) and its rates stay defined at any window. A bound is satisfied by the nearest round width that respects it rather than taken verbatim, so 45s yields 1m.

Fixing the bucket width

To name the width outright, write { "interval": … } instead of a level:

{
  "@expr/timeseries_query": {
    "from": "spans",
    "select": { "as": { "Requests": "count()" } },
    "resolution": { "interval": { "value": 5, "unit": "minutes" } }
  }
}

The interval is used verbatim at every window, so the bucket count grows with the window: a 5-minute width over a week is 2,016 buckets. Use it where the buckets must match a known grid, such as a series compared against a scheduled evaluation or against another fixed-width series, and a level everywhere the chart is the consumer. The same value can come from an expression, like the level forms.

`timerange.transform`

Under the timerange key, transform moves, grows, or replaces the window the query runs over. Three variants:

{ "shift": … } moves the window. "previous_period" moves it back by exactly its own width, whatever the picker currently says; an explicit interval ({ "value": -7, "unit": "days" }) moves it by a fixed amount, negative for backwards.
{ "expand": … } grows the window symmetrically, by an interval on each side — { "value": 1, "unit": "hours" } buys an hour of lead-in and lead-out.
{ "window": … } replaces the window with one of the given width that ends where the current window ends — { "value": 1, "unit": "hours" } reads the trailing hour whatever the current width is. A workflow’s occurrence window is a few minutes wide, so a workflow that needs the last hour ending at the occurrence uses this. The interval must be a positive whole number of units.

A chart draws an expanded or window series against the frame’s own window, the way it draws the unshifted data: rows before the window’s start fall outside the axis unless the mark’s x.domain widens it. Only a shift moves rows onto the current axis.

An interval is { value, unit }, where unit is one of milliseconds, seconds, minutes, hours, days, weeks, months, or years.

The transform applies to the whole query — the time filter and the bucket grid both follow the transformed window. So a shifted timeseries carries the grid of its own earlier window, and a chart gap-fills against that grid before aligning the series onto the current window, where it overlays the unshifted data.

transform takes an expression, so which window is compared can follow a control — { "transform": { "@expr/case": { "compare_to": { "week": { "shift": { "value": -7, "unit": "days" } }, "default": { "shift": "previous_period" } } } } }. The same holds inside an @expr/paired_query’s background, whose shifted query is an ordinary query and resolves its own window.

Comparing against the previous period

{ "timerange": { "transform": { "shift": "previous_period" } } } is what backs a “vs previous period” comparison. Declare it as the background of an @expr/paired_query and each metric comes back holding both readings, which a @block/stat renders as its number and the comparison beneath it. Because the shift is relative to the active window, the comparison stays honest as the user changes the range: an hour compares against the hour before it, a week against the week before it.

The whole background is expressible too, and it accepts false — so one control can switch which window is compared and turn the comparison off, with the stat degrading to a plain number rather than erroring:

{
  "background": {
    "@expr/case": {
      "compare_to": {
        "none": false,
        "week": { "timerange": { "transform": { "shift": { "value": -7, "unit": "days" } } } },
        "default": { "timerange": { "transform": { "shift": "previous_period" } } }
      }
    }
  }
}

{
  "@block/context": {
    "extend": {
      "totals": {
        "@expr/paired_query": {
          "from": "metrics_sum",
          "select": ["Cost"],
          "resolution": "low",
          "background": { "timerange": { "transform": { "shift": "previous_period" } } }
        }
      }
    },
    "@block/stat": {
      "title": "Cost",
      "format": { "currency": "USD" },
      "from": "totals",
      "value": "Cost",
      "label": "vs previous period"
    }
  }
}

One entry, four queries: resolution buckets the rows for the sparkline, the whole-window reading for the headline comes back folded into the same column, and the background runs both grains again over the shifted window. This is what the shipped packs use for their KPI rows — declare it once in a @block/context, then let each stat pick its column with just a value.

Comparing two windows by hand

A background covers the comparisons a paired query can run itself. Anything else — a background that differs by a where, or one you want to join other columns against — is an ordinary join whose target carries its own timerange.transform. Every nested query is compiled as a sealed subquery with its own window, so the shifted side’s macros resolve against its window and a rate denominator stays per-window:

{
  "@expr/query": {
    "from": { "query": { "from": "spans", "select": { "as": { "Requests": "count()" } } } },
    "as": "cur",
    "cross_join": {
      "from": {
        "query": {
          "from": "spans",
          "select": { "as": { "Requests": "count()" } },
          "timerange": { "transform": { "shift": "previous_period" } }
        }
      },
      "as": "prev"
    },
    "select": { "as": { "Requests": "cur.Requests", "Background": "prev.Requests" } }
  }
}

Querying the table in hand

@expr/query, @expr/range_query, @expr/timeseries_query, and @expr/unbound_query are also pipeline operators. Drop the from and the incoming table becomes the driving relation, so select, where, and group_by read its columns and a join key joins another relation against it. as names the incoming table for a join’s on:

{
  "@expr/pipeline": [
    { "@expr/query": "SELECT ServiceName, Duration FROM spans" },
    {
      "@expr/query": {
        "select": { "as": { "ServiceName": "ServiceName", "p95": "quantile(0.95)(Duration)" } },
        "group_by": "ServiceName"
      }
    }
  ]
}

A step over an incoming table runs in memory when every source and expression is supported. The local planner accepts column projections, count(), order_by, limit/offset, and where comparisons (=, !=, <, <=, >, >=), AND/OR/NOT, and IS NULL/IS NOT NULL. The planner also accepts IN/NOT IN with nonempty constant string or integer lists of matching types; integers must fit the input type. Tuple, floating-point, and subquery membership fall back to the database. ClickHouse membership follows transform_null_in (default 0): 0 propagates NULL inputs and ignores NULL list entries; 1 matches NULL to NULL. ClickHouse also accepts isNull and isNotNull. Predicates can reference supported primitive columns, literals, and bound parameters; null predicates discard the row. Unsupported types or expressions send the whole query and its input table to the database. An @expr/filter step always runs in the client. SQL queries over in-memory tables also support searched and simple CASE, including nested cases and an implicit NULL when ELSE is omitted. Give computed SELECT expressions an explicit alias; an unaliased computation falls back to the database when its generated output name is not implemented. A SELECT alias named like a source column is read instead of the column in GROUP BY, HAVING and ORDER BY, as ClickHouse does. In WHERE and the SELECT list, a name that is also an alias of another value falls back to the database. ClickHouse additionally supports if, multiIf, and caseWithExpression; unsupported branch types or expressions send the whole query to the database.

Local SQL also supports ordinary GROUP BY, COUNT(*), COUNT(expression), SUM(expression), MAX(expression), ANY(expression), temporal MIN(expression), and HAVING. These aggregates ignore NULL arguments. ANY selects one non-null value; do not rely on which value it selects when a group contains different values. SUM accepts numeric and boolean inputs and preserves ClickHouse’s widened result types and 64-bit overflow behavior. ClickHouse SUM, MAX, and ANY over non-nullable arguments require an explicit aggregate_functions_null_for_empty setting to determine empty-input behavior. HAVING filters completed groups and can use aggregates omitted from SELECT; ClickHouse also permits unambiguous SELECT aliases. Supported grouped aggregates accept FILTER (WHERE predicate) and ClickHouse’s corresponding -If forms. Each predicate selects rows for that aggregate while preserving groups and the inputs to sibling aggregates. ClickHouse predicates must be boolean or UInt8 expressions; NULL predicates do not contribute. Filtered window aggregates, other aggregate functions, DISTINCT aggregates, and grouping sets use the database.

SQL joins can run locally when both inputs are available in memory. INNER, LEFT, RIGHT, and FULL joins support matching primitive equality keys (including NULL-safe IS NOT DISTINCT FROM) and additional supported ON conditions; CROSS joins are also supported. Select columns explicitly and alias equal-named columns. ClickHouse requires explicit ALL join syntax or configured join_default_strictness: ALL. Outer joins extending non-nullable columns also require configured join_use_nulls, so the local executor can reproduce NULLs or type defaults. Unsupported join modifiers, key types, or unknown settings send the query to the database. String ordering uses ClickHouse’s UTF-8 ordering. Local queries also support COALESCE and ClickHouse numeric addition, preserving nullable results and integer overflow behavior. Function names follow ClickHouse’s case rules: COUNT, MAX, COALESCE and IF are case-insensitive, while multiIf and plus are case-sensitive.

A step must be the structured form. SQL text and { sql } bodies carry their source inside the SQL, which has no slot for the incoming table, so they stay standalone.

Each tag keeps its own behaviour as an operator: @expr/range_query still requires a time range, @expr/unbound_query still takes its window explicitly, and @expr/timeseries_query still buckets by _time_bucket. @expr/paired_query has no operator form — it is always the outermost query.

`@expr/query`

The everyday query. Returns a table, bound to the surrounding time range when there is one (and runs unbound when there isn’t). Feed it to a @block/table, a @block/stat’s value, or a non-temporal chart.

{ "@expr/query": { "from": "spans", "select": { "as": { "avg_duration": "avg(Duration)" } } } }

`@expr/timeseries_query`

A query that returns one row per time bucket — the right source for a line or area chart over time. It injects _time_bucket into select and group_by and picks a bucket size from the active range, so you don’t have to. That’s resolution defaulting to "high".

{ "@expr/timeseries_query": { "from": "spans", "select": { "as": { "requests": "count()" } } } }

Bucketing is per level. A tagged sub-query in from: compiles as its own level with its own window macros, so the outer query’s resolution does not reach into it — and a _time_bucket written there resolves against nothing. Tag the inner query timeseries_query as well (or give a query tag its own resolution) and it buckets in its own right, _time_bucket prepended for you at both levels. That is what a reading needing an inner aggregate first looks like — a per-series delta bucketed before it is summed:

{
  "@expr/timeseries_query": {
    "from": {
      "timeseries_query": {
        "from": "io",
        "select": ["Host", "Device", { "as": { "Delta": "max(Bytes) - min(Bytes)" } }],
        "group_by": ["Host", "Device"],
        "resolution": "low"
      }
    },
    "select": ["Host", { "as": { "Bytes": "sum(Delta)" } }],
    "group_by": "Host",
    "resolution": "low"
  }
}

`@expr/paired_query`

Runs one query body twice — as a foreground and a background — and returns both in one table, so a comparison is one entry instead of two queries wired together by hand. Structured-only: every selected metric needs a declared output name, since that name is what the background pairs against.

select is an ordinary select clause, so a bare entry names itself and an aliased one names its key — ["Cost", "ErrorRate", { "AvgDuration": "avg(Duration)" }] declares Cost, ErrorRate and AvgDuration. Selecting a view scalar is just its name. A bare expression names itself too ("avg(Duration)" comes back under that literal text), so alias it unless you mean to reference it that way.

{
  "@expr/paired_query": {
    "from": "metrics_sum",
    "select": { "Cost": "sum(Value)" },
    "resolution": "none",
    "background": { "timerange": { "transform": { "shift": "previous_period" } } }
  }
}

background says only how the background differs:

timerange — the same metric over the window shifted back by a shift (“vs previous period”). Only a shift is accepted: each metric’s two readings are paired by row position, so the background window has to keep the foreground’s length and resolution, and an expand or window transform is rejected.
select — a different metric over the same window, keyed by the foreground alias it stands against: { "select": { "Errors": "count()" } } makes count() the background of Errors. It is also how you compare two slices of a dimension — write the pair as conditional aggregates, countIf(Version = 'canary') against countIf(Version = 'stable').

Each metric keeps its own name. Cost stays Cost; when a background is declared it holds a pair of foreground and background values instead of a single number. The column’s own shape is what tells a block to unpack it — nothing is renamed, there is no suffix to memorise, and a Tuple(foreground …, background …) you select yourself is a pair on the same terms. With no background declared, the result is exactly what a plain query would have returned.

resolution decides the shape and is required. "none" returns the metrics as one scalar row. "low" or "high" bucket them and fold each metric’s whole-window reading into the same column, so Cost becomes a { window, series } struct — window the headline (constant across rows), series the per-bucket value, each of those a foreground/background pair when a background is declared. That one column feeds a stat’s headline number and its sparkline, which is why a stat over a paired query authors only value.

The whole-window reading is always its own unbucketed query rather than a rollup of the buckets: a bucketed query measures each row over one bucket, so a whole-window number taken from it would divide a rate by the wrong amount of time, and a percentile of percentiles is not a percentile.

Likewise, a background over a different window is a second query, while a background over the same window is only an extra column — so comparing two metrics costs nothing extra.

Because a paired column holds two values rather than one, a block has to know to unpack it — @block/stat does. Handed to a block that doesn’t, a paired column won’t render meaningfully, and a result containing one can’t be used as the from: of another query. A paired query is also always the outermost query: there’s no bare paired_query tag to nest inside a from. Declare no background and none of this applies: the result is an ordinary table.

`@expr/range_query` (special case)

Like @expr/query but it requires a time range in scope and throws if there isn’t one. Reach for it only when running a query without a window would be a bug you’d rather catch loudly.

{
  "@expr/range_query": { "from": "spans", "select": { "as": { "avg_duration": "avg(Duration)" } } }
}

`@expr/unbound_query` (special case)

Ignores the surrounding time range entirely; you pass an explicit timerange with start/end bounds (or none). Use it for a fixed comparison window — “last 30 days regardless of what the page shows”.

Each bound is a TimeBound, the same grammar the time picker uses: date math ("now-30d", "now-1d/d"), an ISO timestamp ("2026-05-30T00:00:00Z"), or a { value, unit } interval — plus a raw unix-ms number for a truly fixed instant. end is optional and defaults to now, so { "start": "now-30d" } is a rolling 30-day window. Prefer date math to a frozen number: "now-30d" means the same thing whenever the query runs, keeping the query portable. Both bounds can also be @expr/* expressions. resolution and timerange.transform work here too, against that explicit window (transform sits in the same timerange object as the bounds).

{
  "@expr/unbound_query": {
    "from": "metrics_sum",
    "select": ["Cost"],
    "timerange": { "start": "now-30d" }
  }
}

`@expr/autocomplete` (special case)

Not a data query — it resolves to a list of suggestions for a field (column names, or matching values for a search prefix). It’s what backs a @block/field_filter; you rarely author it directly. Suggestions are drawn from the view tables that define the field; pass table to pin one explicitly (the filter blocks forward their own table prop for you).

{ "@expr/autocomplete": { "field": "StatusCode", "value": { "@expr/get_context": "input" } } }

Queries

Workspace workflow catalog

Query execution options

Two ways to write a query

A string is SQL; { identifier } is one name

Sorting: by name, or by an expression

grouping_sets

Reading sets in separate blocks

Filtering one set

Whole-window and bucketed sets together

resolution and transform

resolution

Bounding the bucket width

Fixing the bucket width

timerange.transform