3 Functions for Extracting a Value from a List in DuckDB

by

DuckDB provides us with a list_extract() function that extracts a value from a list, based on the element’s index in that list.

But as if one function isn’t enough, it also provides us with a couple of aliases that we can use instead of list_extract() if we prefer.

So let’s call it three functions that we can use to extract a value from a list.

The Functions

  • list_extract(): Extracts a value from the list, based on the value’s index within the list.
  • list_element(): Alias of list_extract().
  • array_extract(): Alias of list_extract(), although array_extract() can also get values from structs.

The syntax goes like this:

list_extract(list, index)

Where index is the position of the element within the list that we want to extract. The alias functions all accept the same arguments and work the same way.

The index is 1-based (counting starts at 1).

Example

Here’s an example of the list_extract() function:

SELECT list_extract(['Cat', 'Dog', 'Bird'], 2);

Result:

Dog

Here, I extracted the value at the second position in the list.

All 3 Functions

Here’s an example with all three functions:

SET VARIABLE animals = ['Cat', 'Dog', 'Bird'];

SELECT 
    list_extract(getvariable('animals'), 3) AS list_extract,
    list_element(getvariable('animals'), 3) AS list_element,
    array_extract(getvariable('animals'), 3) AS array_extract;

Output:

+--------------+--------------+---------------+
| list_extract | list_element | array_extract |
+--------------+--------------+---------------+
| Bird         | Bird         | Bird          |
+--------------+--------------+---------------+

As expected, they all produced the same result.

Specifying a Non-Existent Index

Specifying an index that doesn’t exist in the list returns NULL:

.nullvalue 'null'
SELECT list_extract(['Cat', 'Dog', 'Bird'], 4);

Output:

null

Here I used .nullvalue 'null' to set my DuckDB CLI to return null whenever there’s a NULL value. The default is an empty string, and so setting it to null helps show that a NULL value was returned.

Specifying a Negative Index

Specifying a negative index works backwards from the end of the list:

SELECT list_extract(['Cat', 'Dog', 'Bird'], -1);

Output:

Bird

Specifying an Expression for the Index

The index can be an expression other than an integer literal, such as this:

SELECT list_extract(['Cat', 'Dog', 'Bird'], 1 + 2);

Output:

Bird

Nested Lists

If the list contains other lists, then the function has no trouble in returning whatever list is at the specified index:

SELECT list_extract([
    ['Cat', 'Dog', 'Bird'], 
    ['Zebra', 'Rat', 'Mouse']
    ], 2);

Output:

[Zebra, Rat, Mouse]

However, we can also use the flatten() function to concatenate all sub-lists into a single list:

SELECT list_extract(
    flatten([
        ['Cat', 'Dog', 'Bird'], 
        ['Zebra', 'Rat', 'Mouse']
    ]), 2);

Output:

Dog

To better understand how it came up with that result, here’s the list that the flatten() function produced:

SELECT flatten([
        ['Cat', 'Dog', 'Bird'], 
        ['Zebra', 'Rat', 'Mouse']
    ]);

Result:

[Cat, Dog, Bird, Zebra, Rat, Mouse]

So Dog is the value at position 2 in the list that flatten() produced.

Searching a String

It’s possible to pass a string instead of a list:

SELECT 
    list_extract('Cat', 1) AS i1,
    list_extract('Cat', 2) AS i2,
    list_extract('Cat', 3) AS i3;

Result:

+----+----+----+
| i1 | i2 | i3 |
+----+----+----+
| C  | a  | t  |
+----+----+----+

Searching Structs

Even though DuckDB’s documentation lists the array_extract() function as an alias of list_extract(), there’s something that array_extract() can do that list_extract() can’t; it can return a value from a struct:

SELECT array_extract(
    { 'name' : 'Fetch', 'type' : 'Dog' },
    'name'
);

Result:

Fetch

This is the same result we’d get if we’d used the struct_extract() function.