Unnesting Lists & Structs with DuckDB’s UNNEST() Function

DuckDB has an unnest() function that we can use to unnest lists and structs. Well, it can also be applied to NULL, but that’ll return an empty result.

By “unnest” I mean it takes the list or struct, and it returns its contents as rows in a table. You might say that it converts lists and structs into tables, where each item in the list or struct becomes a row in the table.

Below are examples of using DuckDB’s unnest() function to unnest lists and structs.

Lists

Let’s start with examples to demonstrate how unnest() works with lists.

Unnest a Simple List

Lists can be unnested like this:

SELECT unnest(['Wag', 'Bark', 'Pant']) AS Result;

Result:

+--------+
| Result |
+--------+
| Wag    |
| Bark   |
| Pant   |
+--------+

We can see that the list was converted to a table, with each list item ending up as a row in that table.

Any NULL list items return NULL for its row:

SELECT unnest(['Scratch', NULL, 'Purr']) AS Result;

Result:

+---------+
| Result  |
+---------+
| Scratch |
|         |
| Purr    |
+---------+

I ran that in the DuckDB CLI, and by default, NULL values return an empty string, which is what we got here. We can change that with the .nullvalue dot-command.

Adding a Column

We can use techniques like the following to add a column to the result:

SELECT 
    unnest(['Wag', 'Bark', 'Pant']) AS Name, 
    'Dog' AS Type;

Result:

+------+------+
| Name | Type |
+------+------+
| Wag  | Dog  |
| Bark | Dog  |
| Pant | Dog  |
+------+------+

We only needed to provide Dog once for it to be listed on all rows.

Unnesting Two Lists Side-By Side

If we want the second column to have different values on each row, then we can use another list, with its own unnest():

SELECT 
    unnest(['Wag', 'Tweet', 'Scratch']) AS Name, 
    unnest(['Dog', 'Bird', 'Cat']) AS Type;

Result:

+---------+------+
|  Name   | Type |
+---------+------+
| Wag     | Dog  |
| Tweet   | Bird |
| Scratch | Cat  |
+---------+------+

If the lists are of different sizes, then we’ll end up with NULL in one or more columns:

.nullvalue 'null'
SELECT 
    unnest(['Wag', 'Tweet', 'Scratch', 'Homer']) AS Name, 
    unnest(['Dog', 'Bird', 'Cat']) AS Type;

Result:

+---------+------+
|  Name   | Type |
+---------+------+
| Wag     | Dog  |
| Tweet   | Bird |
| Scratch | Cat  |
| Homer   | null |
+---------+------+

I started this example with .nullvalue 'null' in order to show any NULL values. This is a dot-command that we can use in the DuckDB CLI to show NULL values. As mentioned, the default value for NULL values is an empty string, so setting it to 'null' like we did here makes it easier to tell when a NULL value is returned.

Unnest from a Subquery

We can use unnest() to unnest a list column from a subquery. We can also use other expressions to modify the result, such as concatenating with a string.

Example:

SELECT unnest(dogs) || ' (Dog)' 
FROM (VALUES (['Wag', 'Bark', 'Pant'])) tbl(dogs);

Result:

+----------------------------+
| (unnest(dogs) || ' (Dog)') |
+----------------------------+
| Wag (Dog)                  |
| Bark (Dog)                 |
| Pant (Dog)                 |
+----------------------------+

Unnest an Empty List

Unnesting an empty list returns an empty result. Here’s the output from my terminal when I pass an empty list:

D SELECT unnest([]);
D

Nothing is returned.

Unnest `NULL`

Unnesting NULL also returns an empty result. Here’s the output from my terminal when I pass NULL:

D SELECT unnest(null);
D

Same effect as passing an empty list.

Structs

We can also use the unnest() function to unnest structs:

SELECT unnest({'name': 'Hesham', 'age': 35});

Result:

+--------+-----+
|  name  | age |
+--------+-----+
| Hesham | 35  |
+--------+-----+

Recursive Unnest

We can use recursive := true to recursively unnest any nested lists (lists inside lists):

SELECT 
    unnest([
        ['Wag', 'Tweet', 'Scratch'], 
        ['Bark', 'Purr']
        ], recursive := true ) AS recursive;

Result:

+-----------+
| recursive |
+-----------+
| Wag       |
| Tweet     |
| Scratch   |
| Bark      |
| Purr      |
+-----------+

Here’s what happens if we omit recursive := true or use recursive := false:

SELECT 
    unnest([
        ['Wag', 'Tweet', 'Scratch'], 
        ['Bark', 'Purr']
        ], recursive := false ) AS recursive_false,
    unnest([
        ['Wag', 'Tweet', 'Scratch'], 
        ['Bark', 'Purr']
        ] ) AS recursive_omitted;

Result:

+-----------------------+-----------------------+
|    recursive_false    |   recursive_omitted   |
+-----------------------+-----------------------+
| [Wag, Tweet, Scratch] | [Wag, Tweet, Scratch] |
| [Bark, Purr]          | [Bark, Purr]          |
+-----------------------+-----------------------+

Only the outer list has been unnested.

We can use the recursive := true option to unnest a list of structs:

SELECT 
    unnest(
        [
            {'name': 'Barnesh', 'age': 71}, 
            {'name': 'Alesha', 'age': 17}
            ], 
        recursive := true
        );

Result:

+---------+-----+
|  name   | age |
+---------+-----+
| Barnesh | 71  |
| Alesha  | 17  |
+---------+-----+

However, if a struct contains any lists, those lists aren’t unnested.

Here’s an example of what I mean:

SELECT 
    unnest(
        [
            {'name': 'Barnesh', 'scores': [12,34,17]}, 
            {'name': 'Alesha', 'scores': [43,76,89]}
            ], 
        recursive := true
        );

Result:

+---------+--------------+
|  name   |    scores    |
+---------+--------------+
| Barnesh | [12, 34, 17] |
| Alesha  | [43, 76, 89] |
+---------+--------------+

Here’s an example of a nested struct:

SELECT 
    unnest(
        { 'person' : {'name': 'Barnesh', 'age': 71} }, 
        recursive := true
        );

Result:

+---------+-----+
|  name   | age |
+---------+-----+
| Barnesh | 71  |
+---------+-----+

Changing recursive to false or removing it altogether results in this:

SELECT 
    unnest(
        { 'person' : {'name': 'Barnesh', 'age': 71} }, 
        recursive := false
        ) AS recursive_false,
    unnest(
        { 'person' : {'name': 'Barnesh', 'age': 71} }
        ) AS recursive_omitted;

Result:

+------------------------------+------------------------------+
|            person            |            person            |
+------------------------------+------------------------------+
| {'name': Barnesh, 'age': 71} | {'name': Barnesh, 'age': 71} |
+------------------------------+------------------------------+

Specify the Depth for Unnesting

DuckDB also provides us with an alternative to the recursive parameter. We can provide the depth to which we want unnesting to occur. More specifically, we provide the maximum depth.

Here’s an example of setting a maximum depth of 1 (which is the default):

SELECT unnest(
    [[['Red', 'Blue'], ['Yellow','Green']], [['Dog','Cat'],['Tree','Flower','Seed']]],
    max_depth := 1
    ) AS Result;

Result:

+------------------------------------+
|               Result               |
+------------------------------------+
| [[Red, Blue], [Yellow, Green]]     |
| [[Dog, Cat], [Tree, Flower, Seed]] |
+------------------------------------+

That’s the same result that we’d get if we hadn’t specified a max_depth.

Let’s increase it to 2:

SELECT unnest(
    [[['Red', 'Blue'], ['Yellow','Green']], [['Dog','Cat'],['Tree','Flower','Seed']]],
    max_depth := 2
    ) AS Result;

Result:

+----------------------+
|        Result        |
+----------------------+
| [Red, Blue]          |
| [Yellow, Green]      |
| [Dog, Cat]           |
| [Tree, Flower, Seed] |
+----------------------+

And now 3:

SELECT unnest(
    [[['Red', 'Blue'], ['Yellow','Green']], [['Dog','Cat'],['Tree','Flower','Seed']]],
    max_depth := 3
    ) AS Result;

Result:

+--------+
| Result |
+--------+
| Red    |
| Blue   |
| Yellow |
| Green  |
| Dog    |
| Cat    |
| Tree   |
| Flower |
| Seed   |
+--------+

Lists

Unnest a Simple List

Adding a Column

Unnesting Two Lists Side-By Side

Unnest from a Subquery

Unnest an Empty List

Unnest NULL

Structs

Recursive Unnest

Specify the Depth for Unnesting

Unnest `NULL`