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.

Thanks! What if the multiple values span multiple things with the same prefix? For example I want to match any value in the array that starts with 'hello'. For example, I would want something like ARRAY['hello'] && ARRAY['helloOTHERSTUFF'] to be true. I'll also edit the original post. — Vincent
– Vincent, Commented Aug 10, 2016 at 21:03
For something that arbitrary you'll need to use unnest() to turn both arrays into recordsets and then JOIN them as appropriate. You can use array() to turn that back into an array. — Jim Nasby
– Jim Nasby, Commented Aug 10, 2016 at 21:37
Ah, I was hoping to do something similar to an field_name ILIKE 'hello%' except for arrays. Is that not possible? — Vincent
– Vincent, Commented Aug 10, 2016 at 21:40
It is possible if you want to compare a single value with array of patterns (similar to any but like instead of =), but not vise versa. The general pattern for complex comparison of two (or more) arrays is exists 1 from unnest(<array1>) as t1(x1) join unnest(<array2>) as t2(x2) on (<join condition>), where <join condition> for your example could be x1 ilike x2. — Abelisto
– Abelisto, Commented Aug 10, 2016 at 21:50

Kamil Gosciminski · Accepted Answer · 2017-09-23 21:59:00Z

12

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);

edited Sep 23, 2017 at 21:59

answered Aug 10, 2016 at 21:56

Kamil Gosciminski

17.3k9 gold badges53 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Rafs Over a year ago

Note: if dealing with text and one of the ARRAYs is coming from an aggregation, you might need to cast the underlying datum to text: ARRAY_AGG(DISTINCT tbl.col::text) && ARRAY['x', 'y',..]

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related