Compare two arrays in PostgreSQL

Question

I have a table in postgres with a value column that contains string arrays. My objective is to find all arrays that contain any of the following strings: {'cat', 'dog'}

id  value
 1  {'dog', 'cat', 'fish'}
 2  {'elephant', 'mouse'}
 3  {'lizard', 'dog', 'parrot'}
 4  {'bear', 'bird', 'cat'}

The following query uses ANY() to check if 'dog' is equal to any of the items in each array and will correctly return rows 1 and 3:

select * from mytable where 'dog'=ANY(value);

I am trying to find a way to search value for any match in an array of strings. For example :

select * from mytable where ANY({'dog', 'cat'})=ANY(value);

Should return rows 1, 3, and 4. However, the above code throws an error. Is there a way to use the ANY() clause on the left side of this equation? If not, what would be the workaround to check if any of the strings in an array are in value?

Answer 1

You can use && operator to find out whether two array has been overlapped or not. It will return true only if at least one element from each array match.

Schema and insert statements:

 create table mytable (id int, value text[]);
 insert into mytable values (1,'{"dog", "cat", "fish"}');
 insert into mytable values (2,'{"elephant", "mouse"}');
 insert into mytable values (3,'{"lizard", "dog", "parrot"}');
 insert into mytable values (4,'{"bear", "bird", "cat"}');

Query:

 select * from mytable where array['dog', 'cat'] && (value);

Output:

id	value
1	{dog,cat,fish}
3	{lizard,dog,parrot}
4	{bear,bird,cat}

db<>fiddle here

Query#2 (match array element using wildcard characters)

To compare array elements using wildcard characters you can use unnest() parse every element of both arrays and compare them using Like operator.

   SELECT    distinct m.*
   FROM  mytable m, unnest(value)b 
   inner join  unnest(array['dog%', 'ca%']) a 
   on b LIKE a 

Output:

id	value
1	{dog,cat,fish}
3	{lizard,dog,parrot}
4	{bear,bird,cat}

db<>fiddle here