The relationship between ARRAY_AGG and UNNEST

UpdatedMarch 2, 2026

•1 min read

The relationship between ARRAY_AGG and UNNEST

Constantin Lungu

Part of seriesBigQuery Arrays & Structs

title: "The relationship between ARRAY_AGG and UNNEST" seoTitle: "BigQuery ARRAY_AGG vs UNNEST: Opposite Operations Explained" seoDescription: "ARRAY_AGG packs rows into an ARRAY and UNNEST unpacks an ARRAY back into rows — they are inverse operations in BigQuery." datePublished: Sat Jun 08 2024 18:26:00 GMT+0000 (Coordinated Universal Time) cuid: clx6g4uh900000ajm1u87d5dg slug: the-relationship-between-arrayagg-and-unnest cover: https://cdn.hashnode.com/res/hashnode/image/stock/unsplash/VhDgReMsz8w/upload/910ca1350e91fa8b0c28ce16e9acd9c9.jpeg tags: databases, google-cloud, sql, bigquery, data-engineering

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

Found it useful? Subscribe to my Analytics newsletter at notjustsql.com.

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

Flattening JSON arrays in BigQuery

BigQuery Arrays & Structs

Part 5 of 19

Everything you need to work effectively with nested and repeated data in BigQuery — ARRAY, STRUCT, UNNEST, ARRAY_AGG, and related functions.

Pay attention to cardinality & grain when UNNESTING in BigQuery!

Whenever you're UNNESing an ARRAY, you're getting a Cartesian product between the row and the array contents. If you were to unnest another array, you'll get another Cartesian product between the output of the previous unnest and the elements in the ...

More from this blog

BigQuery Saves Your Query Results — Here's How to Find Them

Ever run a heavy BigQuery SQL query, processed gigabytes of data — and then accidentally closed the tab or forgot to save the results? 😬 Don't re-run it. Your results are still there. BigQuery automa

Mar 5, 20261 min read

BigQuery Saves Your Query Results — Here's How to Find Them

Parameters in BigQuery

You can use query parameters in BigQuery hashtag#SQL (now in the console as well!) — but how are they different from variables, and when should you use each? Both parameters and variables act as place

Feb 7, 20261 min read

Parameters in BigQuery

Flattening JSON arrays in BigQuery

I've noticed that a new JSON function has been added (in Preview) in BigQuery SQL - JSON_FLATTEN(). It allows us to flatten JSON arrays and return a single flat ARRAY, no matter how many nested levels there are. So where is this actually useful?➡️ Ha...

Dec 7, 20251 min read

Flattening JSON arrays in BigQuery

Using LAST_VALUE with STRUCTS

Even an “empty” STRUCT is still technically something. Not the same as a standalone NULL value. This is why, if you work with STRUCTs in SQL and try to find the latest non-empty struct using LAST_VALUE(...) IGNORE NULLS, you’ll notice it doesn’t help...

Oct 5, 20251 min read

Using LAST_VALUE with STRUCTS

Table grain quick validation with SQL

I was doing some exploratory data analysis on a number of tables I didn’t have much information about and, unfortunately, didn’t know their grain. I needed a quick way to validate my assumptions about the table grain, identify contradicting observati...

Oct 4, 20251 min read

Table grain quick validation with SQL

D

Datawise: A blog on SQL, BigQuery, Analytics, Python Tips and Tricks

204 posts

Data Engineer with a passion for transforming complex data landscapes into insightful stories. Here on my blog, I share insights, challenges, and the ever-evolving dance of technology and business.