Filtering
AQL has two ways to apply a filter. They sound the same, but they target different things:
filter()filters a table. Drop rows that don't match.where()filters a metric. Restrict what the aggregation sees.
You'll use both. The difference matters once you start defining reusable metrics.
Practice in the AQL Playground: Filters · Function: filter · where() vs filter() vs explore filters.
filter(): narrow down a table
filter() is the SQL WHERE clause. Give it a table, give it a condition, get back a smaller table.
orders
| filter(orders.status = 'delivered')
| select(orders.id, orders.total_value)
The condition can be any boolean expression valid on a row.
where(): narrow down a metric
where() attaches a filter to an aggregation. It doesn't return a table; it returns a metric that only counts the matching rows.
// Count of delivered orders
count(orders.id) | where(orders.status = 'delivered')
This is what you want inside a metric definition:
measure delivered_orders {
definition: @aql count(orders.id) | where(orders.status = 'delivered') ;;
}
The filter rides along with the metric everywhere it's used.
When to use which
| You want to… | Reach for |
|---|---|
| Drop rows before aggregating | filter() |
| Define a metric that only counts certain rows | where() |
| Filter on a derived aggregate (e.g. groups with > 100 users) | filter() |
| Filter on a plain dimension or another measure | where() works, and is more reusable |
A subtle case: where() only accepts certain shapes (dimension-vs-value, dimension-in-list, or dimension-vs-measure). filter() accepts any boolean expression but only works on tables. For the full breakdown, see where vs filter.
Example: both in the same query
Find countries with at least 100,000 users who placed an order:
orders
| group(orders.country)
| select(orders.country, users_count: count_distinct(orders.user_id))
| filter(users_count >= 100000)
users_count is a column that only exists after the select(), so where() can't see it. filter() is the right tool.
Next
→ Grouping and aggregation: collapse many rows into one number per group.