When working with DuckDB, we may need to determine whether a specific value exists within a list or array. Fortunately, DuckDB offers four synonymous functions that enable us to accomplish this.

The Functions

DuckDB provides the following four functions for checking for the existence of a value in a list:

• list_contains()
• list_has()
• array_contains()
• array_has()

These are all synonymous, and return true if the given list contains the element.

The syntax goes like this:

list_contains(list, element)

Where element is the value that we’re testing the existence of in the list. The alias functions all accept the same arguments and work the same way.

Example

To start simple, here’s the list_contains() function:

SELECT list_contains(['Cat', 'Dog', 'Bird'], 'Cat');

Result:

true

It returned true because the list does in fact contain Cat, which is the value I was testing for.

Let’s try to find a value that doesn’t exist in the list:

SELECT list_contains(['Cat', 'Dog', 'Bird'], 'Horse');

Result:

false

As expected, it returned false.

Example with All Functions

Here it is again, along with its aliases:

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

SELECT 
    list_contains(getvariable('animals'), 'Cat') AS list_contains,
    list_has(getvariable('animals'), 'Cat') AS list_has,
    array_contains(getvariable('animals'), 'Cat') AS array_contains,
    array_has(getvariable('animals'), 'Cat') AS array_has;

Result:

+---------------+----------+----------------+-----------+
| list_contains | list_has | array_contains | array_has |
+---------------+----------+----------------+-----------+
| true          | true     | true           | true      |
+---------------+----------+----------------+-----------+

As expected, they all returned true.

Nested Lists

We can check for a list within a list:

SELECT list_contains([
        ['Dog', 'Wolf'],
        ['Cat', 'Bird']
    ],
    ['Cat', 'Bird']
    );

Output:

true

But we can’t just provide a string and expect it to search through nested lists:

SELECT list_contains([
        ['Dog', 'Wolf'],
        ['Cat', 'Bird']
    ],
    'Cat'
    );

Output:

Binder Error: list_contains: Cannot match element of type 'VARCHAR' in a list of type 'VARCHAR[][]' - an explicit cast is required

However, there’s nothing to stop us from flattening the list:

SELECT 
    list_contains(
        flatten([
            ['Dog', 'Wolf'],
            ['Cat', 'Bird']
        ]),
    'Cat'
    );

Output:

true

The flatten() function concatenates a list of lists into a single list. So by using this technique, we’re creating one big list, then searching through it. To illustrate what I mean, here’s the output of the flatten() function by itself:

SELECT flatten([
    ['Dog', 'Wolf'],
    ['Cat', 'Bird']
]);

Output:

[Dog, Wolf, Cat, Bird]

So that’s the list that the previous example searched through.

NULL Values

The function works fine if the list contains NULL values:

SELECT list_contains(['Cat', NULL, 'Dog'], 'Cat');

Output:

true

But if our second argument is NULL, then the function returns NULL:

.nullvalue 'null'
SELECT list_contains(['Cat', NULL, 'Dog'], NULL);

Output:

null

This is true, even if the list doesn’t contain any NULL values:

SELECT list_contains(['Cat', 'Dog'], NULL);

Output:

null

In the above example 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 in fact returned.