Collect values with ARRAY_AGG

A report often wants one row per make with a short list inside it. ARRAY_AGG builds that list.

How it works

GROUP BY collapses many rows into one row per group. ARRAY_AGG collects the values of those rows into one array on the group row. DISTINCT removes repeats, an ORDER BY inside the call sorts the elements, and LIMIT keeps only the first few. Without an ORDER BY inside the call, it takes the rows of the group as they come.

Common mistake

Every value of the group goes into one array, so a large group gives a very large cell. LIMIT inside the call keeps it small.

Worked example

SELECT make, ARRAY_AGG(DISTINCT test_result ORDER BY test_result) AS results
FROM `learn.mot.fact_mot_test`
WHERE completed_date BETWEEN '2024-06-03' AND '2024-06-05'
  AND make IN ('FORD', 'BMW')
GROUP BY make

Each make gets one row. Its results column is an array of the different test results seen in those three days.

DISTINCT removes the repeats and the ORDER BY puts FAILED before PASSED.

All BigQuery lessons