Postgresql – Use an array returned from a subquery as argument in WHERE clause with ANY function in outer query

arraypostgresqlsubquery

A query like this:

SELECT * 
FROM person
WHERE person.favorite_number = ANY( 
    ARRAY[ 2 , 3 ] 
)
;

…runs. The ANY() function tells us if the single value is found within an array of values. So, we find any person with a favorite number of 2, or of 3, but omit people who chose their single favorite number to be 0, 1, 4, 5, or so on.

➥ How to replace that hard-coded array with an array dynamically retrieved?

Imagine a second table event with a column hits of type INTEGER[] (an array of integers). I want to pull the one array from a single row and feed as the argument to ANY( ) of the outer query.

Something like this:

SELECT * 
FROM person
WHERE person.favorite_number = ANY( 
    SELECT hits FROM event WHERE when = DATE '2018-01-23' ;  -- Returns a single array from a table with `hits` column of type `INTEGER[]`.
)
;

That SELECT hits FROM event WHERE when = DATE '2018-01-23' ; is what stumps me. How do I get a single array resulting from one query to be the argument to a function in the WHERE clause of another outer query?

Best Answer

You're pretty close. The only thing you're missing is unnest() to turn the array into a set-valued function, allowing it to work on the right-hand-side of an ANY.

testdb=# create table person(favorite_number bigint);
CREATE TABLE
testdb=# create table event("when" date, hits bigint[]);
CREATE TABLE
testdb=# insert into event select '2018-01-23', '{2,3}';
INSERT 0 1
testdb=# insert into person select 2;
INSERT 0 1
testdb=# select * from person where favorite_number=ANY(select hit from event, unnest(hits) AS hit where "when"='2018-01-23');
 favorite_number 
-----------------
               2
(1 row)
Related Question