Malloy Queries

<!--
Copyright (c) Credible Data Inc.
SPDX-License-Identifier: MIT
-->

Malloy Query Reference

Only use field names defined in the model. Ground yourself first with get_context; never invent entities or guess field names.

Tool names are written bare here - get_context, execute_query, search_malloy_docs. The exact prefixed name depends on the host surface; match each against the tools you actually have.

Query Patterns

Simple aggregation:

run: source -> {
  aggregate: total_revenue, order_count
}

Group by dimension:

run: source -> {
  group_by: category
  aggregate: revenue
  order_by: revenue desc
  limit: 10
}

Time trend:

# line_chart
run: source -> {
  group_by: order_date.month
  aggregate: revenue
  order_by: 1
}

Filtered query:

run: source -> {
  where: status = 'active'
  group_by: region
  aggregate: count_orders, total_revenue
}

Run a pre-built view:

run: source -> view_name

Refine a view with additional options:

run: source -> view_name + { limit: 10, where: region = 'US' }

Percent of total: use all(), not parent().

run: source -> {
  group_by: category
  aggregate:
    revenue
    pct_of_total is revenue / all(revenue)
}

Conditional dimensions with pick: pick is a keyword, not a function.

run: source -> {
  group_by:
    tier is pick 'Premium' when price > 100
            pick 'Standard' when price > 50
            else 'Budget'
  aggregate: count()
}

Wrong: pick('Premium') { ... } (that's not Malloy syntax).

Window functions with calculate:: running totals, lag(), lead(), and other window operations belong in calculate:, not aggregate:.

run: source -> {
  group_by: order_month is order_date.month
  aggregate: revenue
  calculate: prev_month_revenue is lag(revenue)
  order_by: order_month
}

Field Paths and Joins

Use the joins the model declares. When you answer a question over a published model, every query is rooted on one source, and you reach a joined source's fields by a dotted path within the query body (for example stores.region). Do not write join_one or join_many in an answer query, and never invent a join key. (A join written in a query does compile, and model files and notebooks use them; the rule is that an answer uses the joins the model declares.) If the field you need is on a source the model does not join, say so and ask the user to add the join to the model.

The -> operator separates a source from a view (query transformation). It does NOT navigate between joined sources.

Wrong:

run: candidate -> hiring_manager -> { aggregate: employee_count }

Right:

run: candidate -> { aggregate: hiring_manager.employee_count }

Use the field paths defined in the model verbatim. If the model defines hiring_manager.employee_count, do not strip the hiring_manager. prefix; that prefix is the join namespace, not a separate source to navigate to.

Dates and Time

Comparisons need @ literals

Use @ date literals for comparisons. Never compare a timestamp to a bare number.

Wrong (compares a timestamp to the integer 2020):

where: order_date.year >= 2020

Right:

where: order_date >= @2020-01-01

Truncation accessors return timestamps, not integers

order_date.month returns a month-truncated timestamp (e.g., 2025-06-01 00:00:00), useful for group_by:. It is NOT a 1-12 integer.

Available truncations: .year, .quarter, .month, .week, .day, .hour, .minute, .second.

Anything else is an extraction function, not an accessor: day_of_year(), day_of_week() (1 = Sunday), day(), week(), month(), quarter(), year(), hour(), minute(), second().

Wrong: group_by: created_at.day_of_year → 'created_at' cannot contain a 'day_of_year' Right: group_by: doy is day_of_year(created_at)

month, year, day, date, count and friends are reserved as names

They cannot name an output field. The set is wider than it looks: the timeframes and their plurals (day/days, month/months, year/years, ...), type names (date, timestamp, number, string), aggregate names (count, sum, avg, min, max, all), and now, source, table, by, on, is, asc, desc. The functions are fine: month(order_date) extracts 1-12 anywhere, including where:.

Wrong: group_by: month is order_date.month → 'month' is a reserved word, so to use it as a name you must quote it Right: group_by: order_month is order_date.month Right (when a column is literally named month): group_by: `month`

Filtering by date range

For a single contiguous range, prefer the ? apply operator with a partial-date literal: it's the idiomatic Malloy form and works with date or timestamp fields:

where: order_date ? @2025                    -- anywhere in 2025
where: order_date ? @2025-Q3                 -- Q3 2025 (Jul-Sep)
where: order_date ? @2025-06 to @2025-09     -- June through August (upper bound excluded)
where: order_date ? @2025-06-01 for 3 months -- same range, duration form
where: order_date ? now - 1 year for 1 year  -- the last full year

~ is for strings, not dates: where: order_date ~ @2025 does not compile (Malloy reports mysterious error in range computation). Use ?, or = for a whole year, month or day.

Bounded >=/< with two literals also works and is sometimes clearer:

where: order_date >= @2025-06-01 and order_date < @2025-09-01

Every bound names the field, and a range only goes with ?:

The last one is the dangerous one: a comparison operator in front of a range is accepted, so nothing warns you and the count is simply wrong. Cannot compare a timestamp to a boolean means the right-hand side of a comparison is itself a condition; split it into one comparison per bound.

Aggregates vs Dimensions

where: filters rows before aggregation. having: filters aggregate results. Picking the wrong one is the single most common query error.

• where: only sees dimensions / raw columns.
• having: only sees aggregates / measures.

Wrong: where: total_revenue > 1000 (total_revenue is an aggregate) Right: having: total_revenue > 1000

Wrong: having: region = 'US' (region is a dimension) Right: where: region = 'US'

Prefer inline expressions in having: rather than defining an extra named aggregate just to filter on:

having: count() > 20

Don't put aggregates in group_by:, or dimensions in aggregate:.

Wrong: group_by: total_sales (where total_sales is sum(price)) → Cannot use an aggregate field in a group_by operation Right: group_by: category; aggregate: total_sales

A measure is not a calculate: field either. calculate: takes a window function over an aggregate, not the aggregate itself.

Wrong: calculate: t is total_sales → Cannot use an aggregate field in a calculate operation Right: aggregate: total_sales (or calculate: prev is lag(total_sales) for a window)

Scalar functions are not aggregates. concat(), substr(), arithmetic on raw fields, etc. belong in group_by: or select:, never aggregate:.

Wrong: aggregate: full_name is concat(first_name, ' ', last_name) Right: group_by: full_name is concat(first_name, ' ', last_name)

Counting:

• count(): row count.
• count(field): distinct count of that field.
• There is no count(distinct field) syntax, and count(*) is wrong.

Wrong: count(distinct customer_id), count(*) Right: count(customer_id), count()

There is no method form of count on a joined field either: shipments.shipment_id.count() fails with 'shipments.shipment_id' is not a source or join. Write count(shipments.shipment_id).

Aliases from group_by: aren't visible in where:. where: is evaluated before group_by:, so it can't see aliases defined there. Reference the source field directly.

Wrong:

group_by: region_alias is customer.region
where: region_alias = 'US'

Right:

where: customer.region = 'US'
group_by: region_alias is customer.region

String Matching

Malloy's ~ operator is regex, not SQL LIKE. Use the raw-string r'...' form; no % wildcards.

Wrong: where: name ~ '%Alonso%' Right: where: name ~ r'Alonso'

Both sides must be strings. For multi-value equality, use the ? partial-match operator:

where: region ? 'US' | 'CA' | 'MX'

Order By

order_by: can reference a group_by alias or a column position. It cannot reference a dotted join path; alias the field in group_by: first.

Wrong: order_by: customer.region Right:

group_by: region is customer.region
aggregate: revenue
order_by: region

Field Selection Tips

When the model exposes both a human-readable name and an internal code/ID (e.g., aircraft_model_name vs aircraft_model_code, customer_name vs customer_id), prefer the human-readable one for anything the user will see (group-bys in charts, labels, breakdowns). Check the #(doc) field descriptions in the model to disambiguate.

Chart Annotations

Chart annotations (e.g., # bar_chart, # line_chart, # big_value) go before run:, view:, or nest:, never inside curly braces. Field-level tags (# label, # currency, # x, # y) go above individual fields inside the query block:

# bar_chart
run: source -> {
  group_by: category
  aggregate:
    # label="Revenue"
    # currency
    revenue
  order_by: revenue desc
  limit: 10
}

Charts render only the first aggregate. For multiple measures on one chart, place # y above the aggregate: keyword or use the y=['a','b'] shorthand. A chart annotation left as the last line inside { } fails with "Parser enountered unexpected statement type 'unimplemented'" (the compiler's spelling); one after the closing } fails with "Object annotation not connected to any object".

Read the malloy-charts skill for chart types, properties, data shape requirements, and selection guidance.

More Compile Mistakes

Aggregating a joined field takes method syntax. sum, avg, min and max over a dotted path through a join_many fail with Join path is required for this calculation; use 'inventory_items.item_cost.sum()'. The message gives the fix. Over a join_one path both forms compile and mean different things: sum(o.total) adds each order's total once per base row (once per line item), o.total.sum() once per order. For the joined source's own total (order value, customers) you almost always want the second; the first is right only when the question is weighted per base row. min and max are unaffected.

Wrong: measure: cogs is sum(inventory_items.item_cost) Right: measure: cogs is inventory_items.item_cost.sum()

count(joined.field) is the exception: it is the correct distinct count through a join, so keep it as written (see Counting above). Do not rewrite it as joined.count(field): that form does not compile (Expression illegal inside path.count()). joined.count() does compile, but it counts rows of the joined source, which is a different number from count(joined.field) when the field repeats.

Scalar functions never take method form, and nothing chains onto a function call. round, floor and ceil are always round(x, 2). The errors (something is missing before 'round', Cannot call function round(number, number) with source) name round without saying so, and read like a typo somewhere else.

Wrong: avg(price).round(2), price.avg().round(2), avg_price.round(2) Right: round(avg(price), 2), round(price, 2)

sum and avg need a numeric field. avg(status) fails with Can't use type string. A name that reads numeric (order_number, zip, account_id) is often typed string, so check the type in the get_context result. Count a string field instead of averaging it.

A measure is already an aggregate. aggregate: busiest is max(flight_count) fails with Aggregate expression cannot be aggregate and does not name the field. Aggregate per group, then take the maximum in a second stage:

run: flights -> { group_by: carrier, aggregate: n is flight_count } -> { aggregate: busiest is max(n) }

For only the top row, use order_by: n desc with limit: 1.

A window's braces bind to the function. Put { partition_by: ..., order_by: ... } directly after the window call, not after the division: sum_cumulative(n) { partition_by: region, order_by: n desc } / region_total. For a share within a group, compute the denominator in aggregate: (region_total is all(count(), region)); a window's partition_by does not apply to all().

A dotted path must name a join the source declares. If the source declares the join as carrier, carriers.name fails with 'carriers.name' is not a source or join. Confirm the join name and the field under it in a get_context result; do not infer either from a table name or a plural/singular guess.

order_by: can only name an output column. order_by: total fails with Unknown field total in output space when the query never emits total. group_by or aggregate it first, and alias it if it comes through a join.

Charts: one aggregate per view. A # bar_chart or # line_chart view renders only its first aggregate. For several metrics, nest separate chart views in a # dashboard, or use y=['revenue','cost'].

Define measures and dimensions in the source, and reference them in the view. aggregate: revenue in a view, not a fresh sum(total) there.

Truncate for charts, extract for comparisons. ts.month is the first day of the month (right for a time-series chart, which then orders correctly); month(ts) is the number 1 to 12 (right for comparing across years). year(ts) renders as 2,018; tag it # number=id, as for zip codes and IDs.

Combine an alternation with other filters using a comma. where: is_us = true, party ? 'Democrat' | 'Republican'. The ? alternation means "any of these values". With and it works only when the alternation comes second.

Strings. An apostrophe inside single quotes ends the literal (no viable alternative at input 's'), so use double quotes: "Mac's Diner". There is no concatenation operator (unexpected '+', no viable alternative at input '||'): write concat(origin, '-', destination).

A ; inside a clause ends it. Between clauses a newline, comma or ; all work. Within one clause, separate fields with commas or newlines, or the next field is orphaned (no viable alternative at input 'charters'). The same message appears for an unnamed aggregate after the first: aggregate: n, max(x) fails at max, so name every entry.

Query execution failed: ... means Malloy compiled and the warehouse refused the SQL, often over a type. The position it quotes is in the generated SQL, not in your query. BigQuery, for example, will not partition a window function on a FLOAT64 column; partition_by: takes only a field name, so cast in group_by: (season_key is season::string) and partition on season_key, or fix the type in the model.