Build arrays and records

Opening your lesson

BigQuery builds nested data as easily as it reads it:

ARRAY_AGG(x ORDER BY y LIMIT n) collects a group's values into an array, in order, optionally only the first n.
STRUCT(a AS name, b AS other) makes a record, and ARRAY_AGG(STRUCT(...)) an array of records: one row per customer with all their orders inside.
STRING_AGG(x, ', ' ORDER BY x) joins values into one string.
GENERATE_DATE_ARRAY('2025-06-01', '2025-06-30') makes an array of every day, and UNNEST turns it into rows: a date spine to join other data to, so days with no data still appear.

Building nested results is how you hand an application everything about a customer in one row, and how BigQuery tables like the GA4 export are shaped in the first place.

SELECT customer_id,
       ARRAY_AGG(order_id ORDER BY order_date DESC LIMIT 3) AS recent_orders
FROM shop.orders
GROUP BY customer_id;

Each customer with their three most recent order IDs, newest first.

Choose your character

Your Work Gym account