Check if any of multiple values exist in an array in Postgres

Question

Checking if one value exists in an array is pretty trivial. For example, the following will return true.

SELECT 'hello' = ANY(ARRAY['hello', 'bees'])

But what if I wanted to check if any of multiple values exist in an array? For example, I want to return true if 'hello' OR 'bye' exists in an array. I want to do something like

SELECT ['hello', 'bye'] = ANY(ARRAY['hello', 'bees'])

but that doesn't seem to work.

Edit:

I'm also looking to figure out how I can check if any of multiple values exist in an array where the multiple values have common prefixes.

For example, if I want to return true if the array contains any element with the prefix of 'hello'. So I basically want something like

SELECT ARRAY['hello%'] && ARRAY['helloOTHERSTUFF']

to be true.

Answer 1

For checking if any of the array elements exists in another array use overlap && operator like this:

SELECT ARRAY[1,4,3] && ARRAY[2,1] -- true

A check if every array element matches a specific pattern you could use unnest(anyarray) (to extract array elements) function combined with LIKE or POSIX Regular Expressions (to apply pattern matching) and an aggregate bool_and(expression) - to perform the bitwise AND operator and return one row of output.

Test case:

I have put array elements in separate lines to clarify which comparison yields true and which false.

SELECT bool_and(array_elements)
FROM (
  SELECT unnest(
    ARRAY[
     'hello', -- compared with LIKE 'hello%' yields true
     'helloSOMething', -- true
     'helloXX', -- true
     'hell', -- false
     'nothing' -- false
    ]) ~~ 'hello%'
  ) foo(array_elements); 

So if any of the comparison yields false then the bool_and(array_elements) will return false.

Note: If you need to compare your array against multiple patterns, you could go with POSIX comparison and use | which stands for alternative. As an example let's say we want to find out if every element of an array starts with either hello or not words:

SELECT bool_and(array_elements)
FROM (
  SELECT unnest(
    ARRAY[
     'hello', -- true
     'helloSOMething', -- true
     'helloXX', -- true
     'hell', -- false (doesn't start with neither "hello" nor "not")
     'nothing' -- true (starts with not)
    ]) ~ '^hello|not' -- note the use of ~ instead of ~~ as earlier (POSIX vs LIKE)
  ) foo(array_elements);