BigQuery ARRAY_AGG: How to Group Values into Arrays

BigQuery ARRAY_AGG is an aggregate function that collects the values in a group into a single array, so ARRAY_AGG(product_id) with a GROUP BY customer_id gives you one row per customer with an array of that customer's product IDs. It is the tool for rolling many rows into one, keeping the most recent value per group, or packing rich per-row data into a single column. Below is the syntax and the four modifiers you will actually use (DISTINCT, IGNORE NULLS, ORDER BY, LIMIT), plus the null-element error that surprises everyone once.

The basic form

SELECT
  customer_id,
  ARRAY_AGG(product_id) AS products
FROM sales.orders
GROUP BY customer_id

Each output row has a customer and an array of every product ID they ordered. Because BigQuery supports arrays as a native column type, you can keep that structure, UNNEST it later, or count its length with ARRAY_LENGTH(products).

IGNORE NULLS: the modifier you will need first

A BigQuery array cannot contain a NULL element. If any value in the group is NULL, plain ARRAY_AGG throws "Array cannot have a null element." The fix is IGNORE NULLS, which drops NULLs before building the array.

SELECT
  customer_id,
  ARRAY_AGG(coupon_code IGNORE NULLS) AS coupons
FROM sales.orders
GROUP BY customer_id

Get in the habit of adding IGNORE NULLS whenever the aggregated column is nullable, which is most of the time. It is the single most common reason an ARRAY_AGG query fails in production after working fine on a clean sample.

DISTINCT, ORDER BY, and LIMIT

ARRAY_AGG accepts several modifiers inside the parentheses, and you can combine them. DISTINCT removes duplicates, ORDER BY controls the order of elements in the array, and LIMIT caps how many elements it keeps.

SELECT
  customer_id,
  ARRAY_AGG(DISTINCT product_id IGNORE NULLS) AS unique_products,
  ARRAY_AGG(product_id ORDER BY created_at DESC LIMIT 5) AS last_5_products
FROM sales.orders
GROUP BY customer_id

The order of elements in an aggregated array is not guaranteed unless you add ORDER BY, so if the sequence matters (most recent first, alphabetical, highest value), state it explicitly. LIMIT inside ARRAY_AGG is handy for "top N per group" style results without a window function.

Keeping the latest value per group

A very common need is one value per group, specifically the most recent one. ARRAY_AGG with ORDER BY and LIMIT 1, then indexing element zero, does this in a single expression.

SELECT
  user_id,
  ARRAY_AGG(status ORDER BY updated_at DESC LIMIT 1)[OFFSET(0)] AS latest_status
FROM app.events
GROUP BY user_id

This is a compact alternative to a window function. When you need to keep the whole latest row rather than one field, the QUALIFY clause is usually cleaner, which we cover in how to filter on window functions in BigQuery.

Aggregating STRUCTs for rich rows

ARRAY_AGG is not limited to scalars. Wrap several columns in a STRUCT and you get an array of records, which is how you pack a customer's full order history into one row while keeping each order's fields together.

SELECT
  customer_id,
  ARRAY_AGG(
    STRUCT(order_id, product_id, amount)
    ORDER BY created_at DESC
  ) AS orders
FROM sales.orders
GROUP BY customer_id

Each element is now a record with named fields you can reference after UNNEST. This pattern is the backbone of denormalized tables in BigQuery, where nesting related data avoids a join at query time.

Common uses and one caution

• One row per entity: collapse many child rows into a single parent row with an array column.
• Comma-separated lists: combine with ARRAY_TO_STRING(ARRAY_AGG(name), ', '), or use STRING_AGG to build the delimited string in one step.
• Top N per group: ORDER BY plus LIMIT inside the call, no window function needed.
• Latest value: ORDER BY ... DESC LIMIT 1 then [OFFSET(0)].

The caution is size. Aggregating a huge group into one array can produce very large rows and use a lot of memory, and BigQuery has row-size limits. If a group could hold hundreds of thousands of elements, add a LIMIT or reconsider the shape. For everyday rollups it is efficient and clear.

Skip the syntax and just ask

If you want the result and not the ARRAY_AGG mechanics, Agentsql connects read-only to BigQuery, turns a plain-English question like "list each customer's five most recent products" into Standard SQL, runs it, and shows you the query so you can check it. See the BigQuery integration for how it connects, or read how to query BigQuery without writing SQL, then see how it works.