postgres select where id in array selected from a different table

Question

Question from a postgres noob. I have two tables in postgres. See below for schematic.

strains_snps::table
    name::str
    variants_id::int array

variants::table
    id::int
    ref_base::str

I want to

select variants_id 
from strains_snps 
where strains_snps.name = 'foo' 

and then use that variants_id (which is an int array) in a follow up query.

select * 
from variants 
where id in the_output_from_previous_query

Coming from python, I would assign the output of the first query to a variable, and then check for membership of that variable in the second query. But that probably isn't the best way here, and I hope there is some way to get this working as a single query?

EDIT

@Sumit suggested using a sub-query. I tried this, but without success.

select * from variants 
where id in 
(select variants_id from strains_snps where name = 'BMD1964')

The error pgadmin returned was

ERROR:  operator does not exist: integer = integer[]
LINE 2: where id in 
             ^
HINT:  No operator matches the given name and argument type(s). You  
might need to add explicit type casts.

********** Error **********

ERROR: operator does not exist: integer = integer[]
SQL state: 42883
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Character: 34

Answer 1

try this:

select * 
from variants 
where id in (
    select unnest(variants_id) 
    from strains_snps 
    where strains_snps.name = 'foo' 
)