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: