malloy-queries

name	malloy-queries
description	Malloy query patterns, syntax rules, and chart annotation reference. Consult before writing or debugging any query. Covers dates, aggregates vs dimensions, join paths, filters, string matching, and common error patterns.

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: month is order_date.month
  aggregate: revenue
  calculate: prev_month_revenue is lag(revenue)
  order_by: month
}

Field Paths and Joins

Joins are defined in the model. Never write join_one / join_many inside a query. Every query is rooted on one source, and you reach joined sources via dot notation within the query body.

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, and it cannot be chained: writing order_date.month.day fails. Stop at the first truncation.

Available truncations: .year, .quarter, .month, .week, .day, .hour, .minute, .second. Stop at the first.

month, year, day, quarter are reserved words

Don't try to extract a numeric month with a function call: month(order_date), year(order_date), etc. fail to parse in where: because the names are reserved. The same names can also break aliases:

Wrong: group_by: month is order_date.month → parse error 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

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

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

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)) Right: group_by: category; aggregate: total_sales

Scalar functions are not aggregates. concat(), substring(), 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()

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 misplaced chart annotation (e.g., inside { }) typically produces the error "field is a bar chart, but is not a repeated record".

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

When a Query Fails

Read the error against the tables above and below. Most failures match a known pattern and can be fixed directly. If the cause is not obvious after that, remove pieces (filters, joins, nested views) until the query compiles; whatever you removed last is the bug.

Error message	Likely cause / fix
Cannot compare a timestamp to a number	Comparing date.year to an integer. Use date >= @2020-01-01 instead.
no viable alternative at input '<word>'	Two common causes: a reserved keyword used as an alias or as a function call (e.g., month is ..., month(date_field)) - rename, backtick, or use date_field.month / a ?-apply filter instead; or a semicolon used to separate fields within a clause - fields under one aggregate:/group_by: are comma- or newline-separated, never ; (the error points at the field right after the ;).
'<field_name>' is not defined	Field doesn't exist in the source. Re-check against the model definition; you may have stripped a join prefix.
missing {DAY, HOUR, MINUTE, MONTH, QUARTER, SECOND, WEEK, YEAR}	Chained date property too deep (e.g., .month.something). Stop at the first truncation.
field is a bar chart, but is not a repeated record	Chart annotation placed inside { }. Move # bar_chart above run: / view: / nest:.
Parser encountered unexpected statement	Unsupported feature, or syntax placed where Malloy doesn't allow it (e.g., pick inside a nested view).
Query silently returns zero rows	Filter value mismatch (case, spelling, format). Run a distinct-values query on the dimension to confirm the literal.

Syntax Help

For anything not covered here, call search_malloy_docs with the topic (for example "string functions", "nested queries").