In DuckDB, how do I SELECT rows with a certain value in an array?

Question

I've got a table with a field my_array VARCHAR[]. I'd like to run a SELECT query that returns rows where the value ('My Term') I'm searching for is in "my_array" one or more times.

These (and a bunch more I tried) don't work:

SELECT * FROM my_table WHERE my_array='My Term';
SELECT * FROM my_table WHERE 'My Term' IN my_array;

Answer 1

.list_contains() or one of its aliases:

https://duckdb.org/docs/sql/functions/nested#list-functions

duckdb.sql("""
   with my_table as (
      select unnest([
         ['foo', 'bar', 'baz', 'one'], 
         ['omg', 'hello', 'hi'],
         ['hi', 'hello', 'foo', 'two'], 
      ]) my_array
   )
   from my_table
   where 
      list_contains(my_array, 'foo')
""")

┌───────────────────────┐
│       my_array        │
│       varchar[]       │
├───────────────────────┤
│ [foo, bar, baz, one]  │
│ [hi, hello, foo, two] │
└───────────────────────┘