Flattening Nested Data in BigQuery with UNNEST()

One of the things that makes BigQuery different from a traditional database is that a single cell can hold more than one value. A column can contain an array of items, or a structured object, or arrays of structured objects nested several levels deep. This is powerful for storing data the way it naturally occurs, like an order with many line items, but it makes querying awkward, because most SQL expects flat rows. UNNEST() is the function that bridges the gap. It takes those nested, repeated structures and flattens them into ordinary rows you can query like any other table.

What UNNEST() Does

UNNEST() is a built-in BigQuery function that flattens array-type fields. An array holds multiple values in a single field, and UNNEST() turns each of those values into its own row. Once flattened, the data behaves like a normal table, so you can filter, join, and aggregate it with standard SQL.

The two situations where you reach for it are flattening repeated fields, which is BigQuery’s term for array columns, and simplifying complex nested data built from structs, which are BigQuery’s structured objects. Both are common in real-world datasets, especially anything exported from systems like Google Analytics or Firebase, where nested data is the norm.

The Basic Syntax

At its simplest, UNNEST() takes an array and gives each element a row, with an alias you choose to refer to those elements:

SELECT array_element
FROM UNNEST(array_field) AS array_element

Here array_field is the array you want to flatten, and array_element is the alias for each individual value once it has been broken out into its own row. That alias is how you refer to the flattened values in the rest of your query.

A Simple Example

The clearest way to see it work is to flatten a literal array of values:

SELECT color
FROM UNNEST(['red', 'green', 'blue']) AS color

This produces three rows:

color
-------
red
green
blue

The single array of three colours becomes three separate rows, each holding one colour. That is the entire idea of UNNEST() in its purest form.

Flattening Arrays of Structs

In practice, array fields rarely hold simple values. More often they hold structs, structured objects with named fields of their own. Flattening these lets you pull the struct’s fields out into columns:

SELECT item.product_id, item.quantity
FROM UNNEST([
  STRUCT('prod1' AS product_id, 5 AS quantity),
  STRUCT('prod2', 3),
  STRUCT('prod3', 10)
]) AS item

This gives you:

product_id | quantity
-----------|---------
prod1      | 5
prod2      | 3
prod3      | 10

Each struct in the array becomes a row, and you access its fields with dot notation, item.product_id and item.quantity. This struct-inside-array pattern is exactly how something like a list of order items is typically stored.

Flattening an Array Column from a Table

The real use comes when the array lives in a table column rather than a literal. Imagine an orders table where each order has an items column that is an array of structs:

To flatten it, you join the table to the unnested array using a comma, which in BigQuery performs a correlated cross join between each row and its own array:

SELECT order_id, item.product, item.qty
FROM `dataset.orders`,
UNNEST(items) AS item

The result is that each item within each order becomes its own row, carrying the order_id alongside the product and quantity. One order with three items turns into three rows. The comma before UNNEST() is doing important work here: it pairs each order with the elements of its own items array, so the flattening happens per order rather than across the whole table.

Going Deeper with Nested UNNEST()

Data often nests more than one level. An order contains items, and each item might contain its own array of tags:

orders: [
  {
    "order_id": "ord1",
    "items": [
      { "product": "A", "tags": ["tag1", "tag2"] },
      { "product": "B", "tags": ["tag3"] }
    ]
  }
]

To fully flatten this, you chain UNNEST() calls, each one unnesting an array exposed by the previous one:

SELECT order_id, item.product, tag
FROM `dataset.orders`,
UNNEST(items) AS item,
UNNEST(item.tags) AS tag

The first UNNEST(items) breaks the order into one row per item, and the second UNNEST(item.tags) then breaks each of those rows into one row per tag. The result is a fully flat table where every tag, for every product, in every order, sits on its own row. Notice that the second unnest reads from item.tags, an array that only exists once the first unnest has exposed it.

Combining Structs and Arrays

A single query can pull from both struct fields and unnested array elements at once. If an order has a customer struct alongside its items array:

SELECT
  order_id,
  customer.name,
  item.product,
  item.qty
FROM `dataset.orders`,
UNNEST(items) AS item

Here customer.name comes from a struct that is accessed directly with dot notation and needs no unnesting, while item.productand item.qty come from the flattened array. The two combine cleanly in the same result, with the customer’s name repeated on each item row. The distinction worth holding onto is that a struct is a single object you reach into directly, whereas an array is a repeated field you must unnest.

Unnesting Two Arrays in Parallel

Sometimes you have two separate arrays that line up by position, like a list of products and a matching list of quantities. To pair them correctly, you use WITH OFFSET, which gives each element its index, then join the two arrays on those indexes:

SELECT product, quantity
FROM UNNEST(['A', 'B', 'C']) AS product
WITH OFFSET AS idx
JOIN UNNEST([10, 20, 30]) AS quantity
WITH OFFSET AS idx2
ON idx = idx2

This produces:

product | quantity
--------|---------
A       | 10
B       | 20
C       | 30

WITH OFFSET is the key. It tags each element with its position in the array, and joining on those positions ensures the first product pairs with the first quantity, the second with the second, and so on. Without it, you would get every combination of products and quantities rather than the correct pairing.

Keeping UNNEST() Efficient

A few habits keep these queries fast and inexpensive. Filter early, adding WHERE conditions to cut down the rows before or after unnesting so you are not flattening data you will throw away. Limit the data scanned with WHERE and LIMIT to control cost, since flattening can multiply your row count quickly. And avoid excessive nesting where you can, keeping your schema manageable so queries stay readable and efficient.

A Practical Workflow

When faced with a nested dataset, a reliable approach is to work through it in steps. First, identify which fields are arrays and which are nested structs, since they are handled differently. Then apply UNNEST() to each array you need to flatten. Select only the nested fields that matter for your analysis rather than everything. Use clear aliases like AS item and AS tag so the query stays readable as the nesting deepens. And filter early to keep the query efficient.

A Multi-Level Example

Putting it all together, here is a query flattening three levels at once, where each item has both tags and attributes:

SELECT
  order_id,
  item.product,
  tag,
  attribute.key,
  attribute.value
FROM `dataset.orders`,
UNNEST(items) AS item,
UNNEST(item.tags) AS tag,
UNNEST(item.attributes) AS attribute

Each UNNEST() peels back one layer: items first, then the tags within each item, then the attributes within each item. The result is a fully flattened table where every combination of attribute, tag, and item appears as its own row. Be aware that this multiplies rows quickly, since unnesting several arrays together produces every combination of their elements, so it is worth filtering down to what you actually need.

The Takeaway

UNNEST() is how you turn BigQuery’s nested, repeated data into the flat rows that standard SQL expects. Flatten a single array and each element becomes a row; flatten an array of structs and you access their fields with dot notation; chain UNNEST() calls to flatten data nested several levels deep. Remember the comma join that pairs each row with its own array, reach for WITH OFFSET when you need to align parallel arrays by position, and filter early because flattening can multiply rows fast. Once you are comfortable with it, the nested structures that once looked intimidating become just another table to query.

See you soon.