Malloy Documentation
search

A query on an array, array -> { ... }, runs a Malloy query on the elements of an array in the current row. Its result is a value of that row, so it is used like any other dimension: grouped by, filtered on, or aggregated. Nothing is unnested, so the row is not fanned out.

dimension: page_views is hits -> {
  aggregate: n is count() { where: type = 'PAGE' }
}

This is an experimental feature. Enable it with ##! experimental.array_queries at the top of your .malloy file.

For when to query an array in place rather than unnest it, and for worked examples, see Working with Array and Nested Data.

The examples on this page use the Google Analytics sample: each session has an array of hits, and each hit has an array of product.

##! experimental.array_queries

source: ga_sessions is
  duckdb.table('../data/ga_sample.parquet') extend {
  measure: session_count is count()
}

Writing a query on an array

array_field -> { query } -> { query } ...
  • The left side is a field which names an array, such as hits. To query an expression, make it a dimension first and query that.

  • The query runs with the array's elements as its source. Inside the braces, names are the element's own fields: type, not hits.type. The row's other fields and the source's parameters are not visible there.

  • For an array of scalars, the element is each: tags -> { aggregate: n is count() { where: each = 'sale' } }.

  • An array inside the elements can be aggregated, as in a query on a source: product.productQuantity.sum().

  • Each stage is a { ... } block. A later stage runs on the rows of the stage before it.

What it returns

What the arrow returns depends only on the shape of its query.

Query Result
One stage of aggregate: with one field That value
select:, group_by:, or more than one stage An array of records, on DuckDB and BigQuery
One stage of aggregate: with several fields An error

Examples

A count with a condition, as a number or as a flag:

dimension:
  page_views is hits -> {
    aggregate: n is count() { where: type = 'PAGE' }
  }
  purchased is hits -> {
    aggregate: n is count() {
      where: eCommerceAction.action_type = '6'
    }
  } > 0

An aggregate of an array inside the elements, here the units bought in each session:

dimension: units_bought is hits -> {
  aggregate: n is product.productQuantity.sum()
}

One element, with select:, order_by: and limit:. The result is an array of records, which a path can reach into. This runs on DuckDB and BigQuery.

dimension: landing_page is hits -> {
  select: hitNumber, page.pagePath
  order_by: hitNumber
  limit: 1
}

run: ga_sessions -> {
  group_by: landing_page.pagePath
  aggregate: session_count
}

Using the result. A dimension made by an arrow can be grouped by, filtered on and aggregated. Written inline, an aggregate of it uses the function form:

run: ga_sessions -> {
  where: purchased
  aggregate:
    units is units_bought.sum()
    avg_views is page_views.avg()
    longest is max(hits -> { aggregate: n is time.max() })
}

Database support

An arrow which computes one value works on every database with nested data. An arrow which returns an array works on DuckDB and BigQuery.

Database One value An array
DuckDB, BigQuery yes yes
Postgres, MySQL yes —
Trino, Presto, Databricks yes, with the limits below —
Snowflake yes, with the limits below —
SQL Server — —

On Trino, Presto, Databricks and Snowflake, the value is computed with the database's array functions, which take one aggregate over the elements' own fields. Inside the arrow, these are refused:

  • a having:;

  • a field which is not one aggregate, such as time.max() - time.min(). Compute each aggregate in its own arrow, and combine them outside;

  • a condition on an array inside the elements, unless the aggregate is of that array. count() { where: product.productQuantity > 1 } is refused; product.count() { where: productQuantity > 1 } works.

On Snowflake also, one condition cannot read both an element's own fields and an array inside it. Write them as separate conditions.

Errors

Each of these is reported when the model is translated, or when the query is compiled.

You wrote The error
A field of the row inside the arrow, such as count() { where: device.browser = 'Chrome' } 'device' is a field of the row; the query after 'hits ->' sees only the fields of its elements
-> after an expression, such as ['a', 'b'] -> { ... } Missing '}' before '->', or '->' on an expression: define the expression as a dimension and query that
(hits -> { ... }).sum() A function of an expression is written `sum(expression)`; a method can follow only a field name
An arrow without a name in group_by: Missing '}' before '->', or a 'group_by:' entry without a name (ex: `some_name is ...`)
aggregate: with several fields A query on an array with only `aggregate:` must have one field
An array result on a database which returns one value The trino dialect can only query an array for one value: one stage of `aggregate:` with one field
A having:, on Trino, Presto, Databricks or Snowflake The trino dialect cannot query this array: it has a `having:`
A field which is not one aggregate, on the same four The trino dialect cannot query this array: `n` is not one aggregate
A condition on an inner array, on the same four The trino dialect cannot query this array: it reads `product.productQuantity`, from an array inside the elements, which only an aggregate over that array can read
One condition on both an element and an inner array, on Snowflake The snowflake dialect cannot query this array: one condition reads both `product` and the elements' own fields, which snowflake can read only in separate conditions
Any arrow, on SQL Server The sqlserver dialect cannot query an array with ->