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.
ARRAY_AGG(expr)returns an array with one element for each row of the group.ARRAY_AGG(DISTINCT expr ORDER BY expr)removes repeats and sorts. WhenDISTINCTis used, theORDER BYhas to use the same expression as the argument.IGNORE NULLSskipsNULLelements. BigQuery raises an error when an array in the final result contains aNULLelement, so use it when the source can beNULL.LIMIT ninside the call keeps at mostnelements for each group.
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 makeEach 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.