malloy-queries · git:20260930.0473cf8 · 2026-09-30 · sha256 8e42a55fc552151b

malloy-queries git:20260930.0473cf8A

Immutable. This exact content never changes and is served at /api/v1/blob/8e42a55fc552151b.

No tool to install first
claude plugin marketplace add https://markdownregistry.com/mp/a/art_33xunjjrcrvry7cb/8e42a55fc552151ba466ef3b652d4cc0816e42997acae63e916fa9164aed72d2.json

Then /plugin install malloy-queries@markdownregistry-33xunjjrcrvry7cb in Claude Code. It installs this exact version, the one graded A here, pinned to the commit it was graded at, and leaves it there.

---
name: malloy-queries
description: Malloy query patterns and syntax, for writing or debugging a query - dates, aggregates vs dimensions, join paths, filters, string matching, chart annotations, and the errors a SQL habit produces.
---
<!--
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:**
```malloy
run: source -> {
  aggregate: total_revenue, order_count
}
```

**Group by dimension:**
```malloy
run: source -> {
  group_by: category
  aggregate: revenue
  order_by: revenue desc
  limit: 10
}
```

**Time trend:**
```malloy
# line_chart
run: source -> {
  group_by: order_date.month
  aggregate: revenue
  order_by: 1
}
```

**Filtered query:**
```malloy
run: source -> {
  where: status = 'active'
  group_by: region
  aggregate: count_orders, total_revenue
}
```

**Run a pre-built view:**
```malloy
run: source -> view_name
```

**Refine a view with additional options:**
```malloy
run: source -> view_name + { limit: 10, where: region = 'US' }
```

**Percent of total:** use `all()`, not `parent()`.
```malloy
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.
```malloy
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:`.
```malloy
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

**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:
```malloy
run: candidate -> hiring_manager -> { aggregate: employee_count }
```
Right:
```malloy
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`):
```malloy
where: order_date.year >= 2020
```
Right:
```malloy
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:

```malloy
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:

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

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

| Wrong | Error | Right |
|---|---|---|
| `d > @2021 and < @2022` | `unexpected '<'` | `d ? @2021`, or `d >= @2021-01-01 and d < @2022-01-01` |
| `d > @2021 and @2022` | `'logical operator' Can't use type date` | same |
| `d = (@2021 to @2022)` | `A Range is not a value` | `d ? @2021 to @2022` |
| `ts > @2021 to @2022` | **none** - it compiles, and dropped a 2021-03-04 row | `ts ? @2021 to @2022` |

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:
```malloy
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()`

**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:
```malloy
group_by: region_alias is customer.region
where: region_alias = 'US'
```
Right:
```malloy
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:
```malloy
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:
```malloy
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:

```malloy
# 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.

## 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>'` | Often a `;` between fields within one clause - fields under one `aggregate:`/`group_by:` are comma- or newline-separated (the error points at the field right after the `;`). |
| `unexpected '<field>', expected 'not' or 'null'` | A **reserved word used as a name**, in a multi-line `aggregate:` / `group_by:` list. `second`, `minute`, `hour`, `day`, `week`, `month`, `quarter`, `year` are reserved. On its own line the compiler says so plainly (`'second' is a reserved word, so to use it as a name you must quote it`), but as a later entry in a multi-line list the parser has already committed, and the error points at the NEXT field instead - so you read it as a problem with the line below. Rename the field or backtick it. Verified against the compiler both ways. |
| `'logical operator' Can't use type <string\|date\|...>` | **Any `?` apply combined with `and` needs parentheses**, not just the alternation form. `where: t ? 'a' | 'b' and flag` swallows `and flag` into the alternation list, and `where: d ? @2015 and x = 'y'` fails the same way with `Can't use type date`. Wrap the apply: `where: (d ? @2015) and x = 'y'`. The error names the *type on the left of the apply*, so it points at a clause that is perfectly fine and tells you nothing about the missing parentheses; on a date column it can also surface a spurious second error about `!= null` on an unrelated line. |
| `Circular reference to '<name>' in definition` | A **measure aliased to its own name** (`aggregate: games is games`), which compiles fine until a `having:` references it. Alias to a different name, or drop the alias entirely. |
| `Unknown function '<name>'. Use '<name>!(...)' to call a SQL function directly.` | The function does not exist in Malloy (`substring` is `substr`; there is no `median`, and no `percentile`, either as `percentile(x, 0.5)` or as `x.percentile(50)`). If `get_context` lists a percentile or median measure, use that. **In an ad-hoc query the suggested fix is a dead end**: a query sent to a server is compiled in restricted mode, so `median!(...)` then fails with "direct SQL function calls are not permitted" and following the error message costs two round trips. A saved model file is not compiled that way, so the same call is allowed there and fails only on its own merits. Use the Malloy spelling, or express it another way (an ordered `limit` for a median-like value); reach for `!(...)` only in a model file, and only knowing it pins you to one dialect. |
| `Required filter "<name>" (dimension: <dim>) was not provided` | The source declares a required `#(filter)`. `get_context` lists it under `source_info.filter_params` with its `name`, `type` and `required`; pass a value through `execute_query`'s filter-parameters argument, keyed by the filter's `name` (not the dimension). A `where:` on the dimension does not satisfy it. |
| `'<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. |
| `field is a bar chart, but is not a repeated record` | Chart annotation placed inside `{ }`. Move `# bar_chart` above `run:` / `view:` / `nest:`. |
| `Parser enountered unexpected statement` | Spelled that way by the compiler. Most often a chart annotation left as the last line inside `{ }` - move it above `run:`. Also syntax Malloy doesn't allow in that position (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").