The relationship between ARRAY_AGG and UNNEST

If you're working with nested data in BigQuery, you've might've seen UNNEST, which helps 'unpack' arrays into individual rows.

But there's also ARRAY_AGG, which, if you haven't encountered it before, which takes all rows for your GROUP BY bucket and creates an ARRAY out of them.

So, in essence, ARRAY_AGG and UNNEST are doing the exact opposite of each other.

Check my previous posts on the topic:

• Unnesting ARRAYS in BigQuery

• Using ARRAY_AGG in BigQuery

SELECT
  customer_id,
  ARRAY_AGG(STRUCT(order_id,
                   product)) AS orders
FROM unnested_data
GROUP BY customer_id

SELECT
  customer_id,
  _order.order_id,
  _order.product
FROM array_data
LEFT JOIN UNNEST(orders) AS _order

---

Enjoyed this? Here are some related articles you might find useful:

• Flattening JSON arrays in BigQuery