Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE sample (
    id       serial,
    value    varchar[],
    PRIMARY KEY(id)
);
INSERT INTO sample (value) VALUES 
    (ARRAY['friend', 'apple', 'cat']),
    (ARRAY['cat', 'friend', 'dog']),
    (ARRAY['pasta', 'best-friend', 'lizard']),
    (ARRAY['wildcat', 'potato', 'alices-friend']);
4 rows affected

SELECT DISTINCT(id), value FROM sample WHERE value @> (ARRAY['friend', 'cat']::VARCHAR[]);


id
value


1
{friend,apple,cat}

2
{cat,friend,dog}