A Quick Look at DuckDB’s LIST_WHERE() Function

DuckDB has a list_where() function that returns a list after a user-supplied mask has been applied. We pass the list as the first argument, and a list of Boolean values as the second. The list of Boolean values is applied as a mask to the list, which determines which values in the list are returned.

Syntax

The syntax goes like this:

list_where(value_list, mask_list)

Where value_list is the list to apply mask_list to.

The accepted and return data types are as follows:

list_where(ANY[], BOOLEAN[]) -> ANY[]

There’s also an array_where() function, which is an alias for list_where(). This means we can use either one to get the same result.

Examples

Here’s a simple example to demonstrate:

SELECT list_where([1,2,3,4], [false, true, false, true]);

Output:

[2, 4]

So, the only list items returned are those that correspond with a true value in the mask list.

Here’s the same list but with a different mask:

SELECT list_where([1,2,3,4], [true, true, false, true]);

Output:

[1, 2, 4]

So the mask list simply determines whether or not the corresponding list item will be returned.

Here’s a list of strings:

SELECT list_where(
    ['Rug', 'Pull', 'Party', 'Buy', 'Sell'], 
    [true, false, true, false, true]
    );

Output:

[Rug, Party, Sell]

When the Mask List is Smaller than the Value List

It’s possible to provide a mask list that’s shorter than the value list without getting an error. Missing values are taken to be false.

Example:

SELECT list_where(
    ['Rug', 'Pull', 'Party', 'Buy', 'Sell'], 
    [true, false, true]
    );

Result:

[Rug, Party]

When the Mask List is Longer than the Value List

It’s also possible to provide a mask list that’s longer than the value list without getting an error. However, if any of the extra Booleans in the mask list are true, then NULL is returned for those values.

Example:

SELECT list_where(
    ['Rug', 'Pull', 'Party'], 
    [true, false, true, true, false, true]
    );

Result:

[Rug, Party, NULL, NULL]

We got two NULL values here because the mask list contains two true values that don’t correspond with a value in the value list.

The false value doesn’t result in NULL. So here’s what happens if all of the extra Booleans are false:

SELECT list_where(
    ['Rug', 'Pull', 'Party'], 
    [true, false, true, false, false, false]
    );

Result:

[Rug, Party]

No NULL values are returned.

Passing Non-Booleans

If the second list contains any value that isn’t a Boolean, then an error is returned:

SELECT list_where([1,2,3,4], [0, 1, 0, 1]);

Result:

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


LINE 1: SELECT list_where([1,2,3,4], [0, 1, 0, 1]);
               ^

The array_where() Function

The array_where() function is an alias for list_where(). Therefore, we can use either one to achieve the same output. For example:

SELECT 
    list_where([1,2,3,4], [false, true, false, true]) AS list_where,
    array_where([1,2,3,4], [false, true, false, true]) AS array_where;

Output:

+------------+-------------+
| list_where | array_where |
+------------+-------------+
| [2, 4]     | [2, 4]      |
+------------+-------------+