Filter rows by an array member

Use has(array, value) when a row should match because its array contains a particular immediate member. This preserves one result row per matching table row; it does not expand the array into additional rows.

sql

CREATE TABLE tagged_items(id INTEGER PRIMARY KEY, tags Array(String));
INSERT INTO tagged_items VALUES
    (1, array('sql', 'guide')),
    (2, array('release')),
    (3, array('sql'));

SELECT id
FROM tagged_items
WHERE has(tags, 'sql')
ORDER BY id
FORMAT csv;

The matching IDs are 1 and 3. Array members obey the declared member type. A nested array is an immediate member of its outer array, so do not assume a membership test searches every nested level.

Use array_position(tags, 'sql') when the first matching position is also useful. Positions are one-based, and a missing value returns NULL. array_positions returns every matching position when an array contains duplicates. An empty collection and an SQL NULL array are different inputs; decide whether missing arrays should be excluded or handled explicitly in the predicate.

See array functions and Array.

Filter rows by an array member ​

Filter rows by an array member