4 Ways to Concatenate 3 or More Lists in DuckDB

If you’ve ever used list_concat() or any of its aliases to concatenate lists in DuckDB, you may have been disappointed to find out that it only concatenates two lists. Any more than two lists and it returns an error. At least that’s how it works at the time of this writing.

Fortunately, there are some alternatives we can use in order to concatenate three or more lists.

Here are four ways to concatenate three or more lists in DuckDB.

The concat() Function

The trusty concat() function is often used to concatenate two or more strings, but we can actually use it on any data type, including lists:

SELECT concat(
    ['Dog', 'Cat'],
    ['Tree', 'Flower'],
    ['Red', 'Blue']
    ) AS concat_function;

Result:

+-------------------------------------+
|           concat_function           |
+-------------------------------------+
| [Dog, Cat, Tree, Flower, Red, Blue] |
+-------------------------------------+

The concat() function skips NULL lists:

SELECT concat(
    ['Dog', 'Cat'],
    NULL,
    ['Red', 'Blue']
    ) AS concat_function;

Result:

+-----------------------+
|    concat_function    |
+-----------------------+
| [Dog, Cat, Red, Blue] |
+-----------------------+

But it retains any NULL values within the lists:

SELECT concat(
    ['Dog', 'Cat'],
    ['Tree', NULL],
    ['Red', 'Blue']
    ) AS concat_function;

Result:

+-----------------------------------+
|          concat_function          |
+-----------------------------------+
| [Dog, Cat, Tree, NULL, Red, Blue] |
+-----------------------------------+

The Concatenation Operator (||)

The second option for concatenating three or more lists is to use the concatenation operator:

SELECT 
    ['Dog', 'Cat'] || 
    ['Tree', 'Flower'] || 
    ['Red', 'Blue'] AS concatenation_operator;

Result:

+-------------------------------------+
|       concatenation_operator        |
+-------------------------------------+
| [Dog, Cat, Tree, Flower, Red, Blue] |
+-------------------------------------+

One difference is that the concatenation operator doesn’t skip NULL lists. If any of the lists are NULL, it returns NULL:

.nullvalue 'null'
SELECT 
    ['Dog', 'Cat'] || 
    NULL || 
    ['Red', 'Blue'] AS concatenation_operator;

Result:

+------------------------+
| concatenation_operator |
+------------------------+
| null                   |
+------------------------+

Here I used .nullvalue 'null' to output any NULL values as null so that we can see that it is in fact, NULL.

However, if a list item is NULL then the lists are still concatenated with the NULL values (just like with the concat() function):

SELECT 
    ['Dog', 'Cat'] || 
    ['Tree', NULL] || 
    ['Red', 'Blue'] AS concatenation_operator;

Result:

+-----------------------------------+
|      concatenation_operator       |
+-----------------------------------+
| [Dog, Cat, Tree, NULL, Red, Blue] |
+-----------------------------------+

The list_concat() Function

While the list_concat() function only concatenates two lists at a time, there’s nothing to stop us from nesting list_concat()s inside each other:

SELECT list_concat(
    ['Dog', 'Cat'],
    list_concat(['Tree', 'Flower'],['Red', 'Blue'])
    ) AS list_concat_function;

Result:

+-------------------------------------+
|        list_concat_function         |
+-------------------------------------+
| [Dog, Cat, Tree, Flower, Red, Blue] |
+-------------------------------------+

Any NULL lists are skipped:

SELECT list_concat(
    ['Dog', 'Cat'],
    list_concat(NULL,['Red', 'Blue'])
    ) AS list_concat_function;

Result:

+-----------------------+
| list_concat_function  |
+-----------------------+
| [Dog, Cat, Red, Blue] |
+-----------------------+

But any NULL values within the lists are included:

SELECT list_concat(
    ['Dog', 'Cat'],
    list_concat(['Tree', NULL],['Red', 'Blue'])
    ) AS list_concat_function;

Result:

+-----------------------------------+
|       list_concat_function        |
+-----------------------------------+
| [Dog, Cat, Tree, NULL, Red, Blue] |
+-----------------------------------+

The flatten() Function

The flatten() function is used to concatenate a list of lists into a single list. In other words, we can concatenate three or more lists, as long as they’re inside another list:

SELECT flatten([
    ['Dog', 'Cat'],
    ['Tree', 'Flower'],
    ['Red', 'Blue']
    ]) AS flatten_function;

Result:

+-------------------------------------+
|          flatten_function           |
+-------------------------------------+
| [Dog, Cat, Tree, Flower, Red, Blue] |
+-------------------------------------+

It’s important that the lists are enclosed within another list, otherwise an error will occur.