How can I find all rows that have at least one element in an array matching a pattern? Consider the following example:

CREATE TEMPORARY TABLE testing (
    names TEXT[]
);

INSERT INTO testing VALUES
    ('{toplevel1}'),
    ('{toplevel1.sub1, toplevel2.sub2}'),
    ('{toplevel1.sub1.sub2, toplevel2.sub2}'),
    ('{toplevel1.sub1.sub3, toplevel2.sub2}'),
    ('{toplevel1.sub1.sub3.sub3, toplevel2.sub2}'),
    ('{toplevel2.sub1}')
;

SELECT * FROM testing WHERE 'toplevel1.%' ILIKE ANY(names);


I want to write a select where "any" element of an array matches a pattern. But I can't figure out a way to do it. In the example I've given, using the ILIKE operator can't work because it checks if the RHS matches the LHS. For this case I would need a similar operator but which checks if the LHS matches the RHS.

update: I'm currently fiddling around with something like this without much success:

SELECT * FROM testing WHERE names ILIKE ANY (ARRAY(SELECT unnest(names) || '%'));
You can use the official ltree extension.

CREATE EXTENSION IF NOT EXISTS ltree;

CREATE TEMP TABLE testing (
    names ltree[]
);

INSERT INTO testing (names)
VALUES
    ('{toplevel1}'),
    ('{toplevel1.sub1, toplevel2.sub2}'),
    ('{toplevel1.sub1.sub2, toplevel2.sub2}'),
    ('{toplevel1.sub1.sub3, toplevel2.sub2}'),
    ('{toplevel1.sub1.sub3.sub3, toplevel2.sub2}'),
    ('{toplevel2.sub1}');

SELECT * FROM testing WHERE names ~ 'toplevel1.*';


It is also doable in "plain" postgresql:

SELECT * FROM testing AS t
WHERE (
    SELECT true 
    FROM unnest(t.names) AS n
    WHERE n ILIKE 'toplevel1.%'
    LIMIT 1
);


The ltree extension is not very widely used, and obviously it has some limitations (including label length &amp; character set), but it's stable and can achieve better performance than unnest-based version. I would really give it a try.