检查Postgres中的数组中是否存在多个值中的任何一个

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

检查数组中是否存在一个值非常简单。例如,以下内容将返回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

但是,如果我想检查数组中是否存在多个值,该怎么办?例如,如果数组中存在“hello”或“bye”,我想返回true。我想做点什么

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

例如,如果我想在数组包含前缀为'hello'的任何元素时返回true。所以我基本上想要类似的东西

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

to be true.

是真实的。

1 个解决方案

#1

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

要检查另一个数组中是否存在任何数组元素,请使用overlap &&运算符,如下所示:

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.

检查每个数组元素是否与您可以使用的特定模式匹配(anyarray)(提取数组元素)函数与LIKE或POSIX正则表达式(应用模式匹配)和聚合bool_and(表达式)相结合 - 执行按位AND运算符并返回一行输出。

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.

因此,如果任何比较产生错误,则bool_and(array_elements)将返回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:

注意:如果需要将数组与多个模式进行比较,可以使用POSIX比较并使用|代表另类。举个例子,假设我们想知道数组的每个元素是以hello还是不以单词开头的:

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

#1