PostgreSQL has the SOME() construct that we can use to perform searches against arrays. It returns a Boolean result, and so we can incorporate this into our searches to return just those rows that yield true or false.

SOME() is a synonym for ANY(), and so we can use either one to get the same result.

Example

Here’s a query that shows the output of the SOME() construct against an array column for a particular expression:

SELECT 
    c1,
    c2,
    c3, 
    'Zebra' = SOME(c3) AS "True/False"
FROM t1;

Result:

 c1 |     c2     |                          c3                           | True/False 
----+------------+-------------------------------------------------------+------------
  1 | Animals    | {Zebra,Bird,Mouse,Cow,Pig}                            | t
  2 | Music      | {Rock,Jazz,Blues,Electronica,Metal}                   | f
  3 | Pets       | {Cat,Dog,Zebra,Buffalo}                               | t
  4 | Sports     | {Rugby,Hockey,Soccer,Golf,Cricket,Baseball,Wrestling} | f
  5 | Activities | {Golf,Walking,Running,Climbing}                       | f
(5 rows)

The True/False column shows the outcome of the expression. If the array in the c3 column contains a Zebra element, then the result is t (True), otherwise it’s f (False).

We can therefore filter our queries based on the output of such expressions.

For example:

SELECT c1, c2, c3 FROM t1 
WHERE 'Zebra' = SOME(c3);

Result:

 c1 |   c2    |             c3             
----+---------+----------------------------
  1 | Animals | {Zebra,Bird,Mouse,Cow,Pig}
  3 | Pets    | {Cat,Dog,Zebra,Buffalo}
(2 rows)

Here we returned just those rows where the condition is true.

We can negate the result with the NOT operator:

SELECT c1, c2, c3 FROM t1 
WHERE NOT 'Zebra' = SOME(c3);

Result:

 c1 |     c2     |                          c3                           
----+------------+-------------------------------------------------------
  2 | Music      | {Rock,Jazz,Blues,Electronica,Metal}
  4 | Sports     | {Rugby,Hockey,Soccer,Golf,Cricket,Baseball,Wrestling}
  5 | Activities | {Golf,Walking,Running,Climbing}
(3 rows)

This time we got all rows where the condition is not true (i.e false).

Passing a NULL Value

If we pass a NULL value, then NULL is returned:

SELECT 'Zebra' = SOME(null);

Result:

null

Passing a Non Array

Passing a non array results in an error:

SELECT 'Zebra' = SOME(c1) FROM t1;

Result:

ERROR:  op ANY/ALL (array) requires array on right side
LINE 1: SELECT 'Zebra' = SOME(c1) FROM t1;
                       ^

In this case I passed column c1 which is an integer column (not an array column), and so an error was returned.