Extracting the First Non-NULL Value from a List with DuckDB’s LIST_ANY_VALUE() Function

DuckDB provides us with a good selection of list functions. This includes a list_any_value() function, which returns the first non-NULL value from a given list.

Syntax

list_any_value(list)

So the function accepts a single argument; the list.

Example

Here’s a simple example to demonstrate:

SELECT list_any_value( [ NULL, 'Red', 'Green', 'Blue' ] );

Result:

Red

We can see that it skipped the NULL value and took the first value after that.

Multiple NULLs

If there are multiple NULL values, then they’re all skipped until the first non-NULL value is found:

SELECT list_any_value( [ NULL, NULL, NULL, 'Red', 'Green', 'Blue' ] );

Result:

Red

When All Values are NULL

If all of the list’s values are NULL, then NULL is returned:

.nullvalue 'null'
SELECT list_any_value( [ NULL, NULL, NULL ] );

Result:

null

Here, I used .nullvalue 'null' in my DuckDB CLI in order to output null whenever a NULL value is returned. Without doing this, the CLI would return an empty string whenever a NULL value is returned. That would make it difficult for us to tell whether or not a NULL value is actually being returned, hence the .nullvalue 'null'.

Passing an Empty List

Passing an empty list results in NULL being returned:

SELECT list_any_value( [ ] );

Result:

null

Passing the Wrong Argument Type

If the argument is not a list, then an error occurs:

SELECT list_any_value( 'Red' );

Result:

Binder Error: No function matches the given name and argument types 'list_aggr(STRING_LITERAL, STRING_LITERAL)'. You might need to add explicit type casts.
	Candidate functions:
	list_aggr(ANY[], VARCHAR, [ANY...]) -> ANY

LINE 1: SELECT list_any_value( 'Red' );
               ^

Nested Lists

If the first non-NULL value is a list, then that list is returned:

SELECT list_any_value( [NULL, NULL, [ NULL, 'Red', 'Green', 'Blue' ]] );

Result:

[NULL, Red, Green, Blue]

Bear in mind that the list is still returned, even if it contains nothing but NULL values:

SELECT list_any_value([ 
    [ NULL, NULL], 
    [ NULL, 'Red', 'Green', 'Blue' ]
    ]);

Result:

[NULL, NULL]

However, it’s possible to use the flatten() function to flatten lists if required:

SELECT list_any_value( 
    flatten([ 
        [ NULL, NULL], 
        [ NULL, 'Red', 'Green', 'Blue' ]
        ]) 
    );

Result:

Red

We can also use the unnest() function, depending on our requirements:

SELECT list_any_value( 
    unnest([ 
        [ NULL, NULL], 
        [ NULL, 'Bird'], 
        [ NULL, 'Red', 'Green', 'Blue' ]
        ]) 
    ) AS unnested;

Result:

+----------+
| unnested |
+----------+
| null     |
| Bird     |
| Red      |
+----------+