---
title: "UNNEST"
description: "UNNEST expands a list (array) or struct column into multiple rows or columns, turning nested data into a flat, relational shape."
canonical: "https://motherduck.com/glossary/unnest/"
related:
  - title: "UNPIVOT | MotherDuck Docs"
    url: "https://motherduck.com/docs/sql-reference/duckdb-sql-reference/duckdb-statements/unpivot/"
  - title: "Analyze JSON Data Using SQL and DuckDB"
    url: "https://motherduck.com/blog/analyze-json-data-using-sql/"
  - title: "What's New in DuckDB 1.5! | MotherDuck"
    url: "https://motherduck.com/videos/whats-new-duckdb-15/"
gated_asset:
  title: "DuckLake on MotherDuck"
  url: "https://motherduck.com/product/ducklake/"
---

# UNNEST

> UNNEST expands a list (array) or struct column into multiple rows or columns, turning nested data into a flat, relational shape.

## Overview
`UNNEST` is the standard SQL mechanism for flattening nested collection types — lists/arrays and, in engines that support it, structs — into rows. It's essential whenever source data (JSON, Parquet with nested columns, API responses) arrives with one row containing an array of values that you need to analyze one element at a time.

```sql
SELECT customer_id, UNNEST(tags) AS tag
FROM customers;
```

If a customer has `tags = ['vip', 'newsletter']`, this produces two output rows, one per tag, with `customer_id` repeated.

## Unnesting multiple lists together
When multiple list columns are unnested in the same `SELECT`, most engines (including DuckDB) unnest them side by side, positionally, rather than producing a cross product:

```sql
SELECT UNNEST([1, 2, 3]) AS a, UNNEST([10, 11]) AS b;
-- (1, 10), (2, 11), (3, NULL)  -- shorter list is padded with NULL
```

## DuckDB specifics
DuckDB's `UNNEST` also flattens `STRUCT` columns into multiple named columns (one per struct field), and supports a `recursive := true` argument to fully flatten arbitrarily nested lists-of-structs or lists-of-lists in one call:

```sql
SELECT UNNEST([{'a': 42, 'b': 84}, {'a': 100, 'b': NULL}], recursive := true);
```

A `max_depth` argument caps how many nesting levels are unnested, useful when you want to flatten only partway into a deeply nested structure. DuckDB also supports the equivalent function-call syntax, `unnest(list_column)`, and pairs naturally with `list_value`/list literals (`[1, 2, 3]`) and `generate_series` for building or exploding array-shaped data.