In PostgreSQL, we have many ways to retrieve data from arrays. One way is to specifically reference its subscript or a range of subscripts. Another way is to search through the array’s contents for the value we want.
By doing a search, I mean we could do things like filter the query with a WHERE clause, so that we only return rows that contain an array that has an element with a certain value.
Sample Data
Suppose we have the following table:
SELECT * FROM t1;Result:
c1 | c2 | c3 | c4
----+------------+-------------------------------------------------------+-------------------------------------
1 | Animals | {Zebra,Bird,Mouse,Cow,Pig} | {{1,2,3},{4,5,6},{7,8,9}}
2 | Music | {Rock,Jazz,Blues,Electronica,Metal} | {{32,78,14},{102,99,37},{18,65,29}}
3 | Pets | {Cat,Dog,Zebra,Buffalo} | {{8,9,7},{4,5,3},{9,8,9}}
4 | Sports | {Rugby,Hockey,Soccer,Golf,Cricket,Baseball,Wrestling} | {{1,2,1},{9,10,3},{7,8,5}}
5 | Activities | {Golf,Walking,Running,Climbing} | {{4,32,56},{45,1,56},{37,5,9}}
Below are three options for searching through this data.
The ANY() Construct
We can use the ANY() construct combined with an operator to search through the elements in an array:
SELECT c1, c2, c3 FROM t1
WHERE 'Zebra' = ANY(c3);Result:
c1 | c2 | c3
----+---------+----------------------------
1 | Animals | {Zebra,Bird,Mouse,Cow,Pig}
3 | Pets | {Cat,Dog,Zebra,Buffalo}
Here, we used the ANY() construct to narrow the results down to just those arrays containing Zebra. With the ANY() construct, the left-hand expression is evaluated and compared to each element of the array using the given operator, which must yield a Boolean result. In this case Zebra is evaluated and compared to each element in the arrays in the c3 column. When a match is found, the query returns the columns in the SELECT list.
We can also use SOME(), which is a synonym for ANY():
SELECT c1, c2, c3 FROM t1
WHERE 'Zebra' = SOME(c3);Here’s the result that our ANY() construct returns against each row:
SELECT
c3,
'Zebra' = ANY(c3) AS "True/False"
FROM t1;Result:
c3 | True/False
-------------------------------------------------------+------------
{Zebra,Bird,Mouse,Cow,Pig} | t
{Rock,Jazz,Blues,Electronica,Metal} | f
{Cat,Dog,Zebra,Buffalo} | t
{Rugby,Hockey,Soccer,Golf,Cricket,Baseball,Wrestling} | f
{Golf,Walking,Running,Climbing} | f
So when we included it in our WHERE clause (in the first example), our query only returned the rows where the condition was true (t).
Here’s an example against the multi dimensional arrays in the c4 column:
SELECT c1, c2, c4 FROM t1
WHERE 3 = ANY(c4);Result:
c1 | c2 | c4
----+---------+----------------------------
1 | Animals | {{1,2,3},{4,5,6},{7,8,9}}
3 | Pets | {{8,9,7},{4,5,3},{9,8,9}}
4 | Sports | {{1,2,1},{9,10,3},{7,8,5}}
We can negate the outcome with the NOT operator:
SELECT c1, c2, c4 FROM t1
WHERE NOT 3 = ANY(c4);Result:
c1 | c2 | c4
----+------------+-------------------------------------
2 | Music | {{32,78,14},{102,99,37},{18,65,29}}
5 | Activities | {{4,32,56},{45,1,56},{37,5,9}}
Here’s what happens when we swap the equals sign (=) for a not equal to sign (<>):
SELECT c1, c2, c4 FROM t1
WHERE 3 <> ANY(c4);Result:
c1 | c2 | c4
----+------------+-------------------------------------
1 | Animals | {{1,2,3},{4,5,6},{7,8,9}}
2 | Music | {{32,78,14},{102,99,37},{18,65,29}}
3 | Pets | {{8,9,7},{4,5,3},{9,8,9}}
4 | Sports | {{1,2,1},{9,10,3},{7,8,5}}
5 | Activities | {{4,32,56},{45,1,56},{37,5,9}}
The ALL() Construct
Another way to search through an array is with the ALL() construct:
SELECT c1, c2, c4 FROM t1
WHERE 3 <> ALL(c4);Result:
c1 | c2 | c4
----+------------+-------------------------------------
2 | Music | {{32,78,14},{102,99,37},{18,65,29}}
5 | Activities | {{4,32,56},{45,1,56},{37,5,9}}
This example is exactly the same as the previous one, except that I swapped ANY() with ALL().
Here’s another one:
SELECT c1, c2, c4 FROM t1
WHERE 10 > ALL(c4);Result:
c1 | c2 | c4
----+---------+---------------------------
1 | Animals | {{1,2,3},{4,5,6},{7,8,9}}
3 | Pets | {{8,9,7},{4,5,3},{9,8,9}}
In this case, 10 is greater than all elements in the arrays in c4 . In other words, all elements are less than ten.
The && Operator
Another tool we have for searching arrays is the && operator:
SELECT c1, c2, c3 FROM t1
WHERE c3 && ARRAY['Zebra'];Result:
c1 | c2 | c3
----+---------+----------------------------
1 | Animals | {Zebra,Bird,Mouse,Cow,Pig}
3 | Pets | {Cat,Dog,Zebra,Buffalo}
The && operator looks for an overlap between two arrays, so that’s why I passed an array on the right side of the operator. Basically, this operator returns true whenever two arrays have one or more elements in common:
SELECT
c4,
c4 && ARRAY[3] AS "True/False"
FROM t1;Result:
c4 | True/False
-------------------------------------+------------
{{1,2,3},{4,5,6},{7,8,9}} | t
{{32,78,14},{102,99,37},{18,65,29}} | f
{{8,9,7},{4,5,3},{9,8,9}} | t
{{1,2,1},{9,10,3},{7,8,5}} | t
{{4,32,56},{45,1,56},{37,5,9}} | f
The <@ and @> Operators
The <@ and @> operators check to see whether one array contains all elements in the other. Specifically, the <@ operator checks that the array to the right of the operator contains all elements in the array to the left, and the @> operator checks that the left array contains all elements in the array to the right.
Example:
SELECT
ARRAY[1,3] <@ ARRAY[1,2,3] AS "<@",
ARRAY[1,3] @> ARRAY[1,2,3] AS "@>";Result:
<@ | @>
----+----
t | f
Note that all elements of the other array must be present for it to return true.
Here’s a simple database example:
SELECT
*,
c2 <@ c3 AS "True/False"
FROM t2
ORDER BY c1;Result:
c1 | c2 | c3 | True/False
----+------------+-------------------------------+------------
1 | {1,2,3} | {{1,2,3},{4,5,6}} | t
2 | {4,5,6} | {{7,8,9},{10,11,12}} | f
3 | {7,15,14} | {{7,13,14},{15,16,17}} | t
4 | {10,11,12} | {{100,200,300},{400,500,600}} | f
So we could add the operator to a WHERE clause to return something like this:
SELECT * FROM t2
WHERE c2 <@ c3
ORDER BY c1;Result:
c1 | c2 | c3
----+-----------+------------------------
1 | {1,2,3} | {{1,2,3},{4,5,6}}
3 | {7,15,14} | {{7,13,14},{15,16,17}}