How to query all rows where a given column matches at least all the values in a given array with PostgreSQL?

The request below:

SELECT foos.id,bars.name
FROM foos
JOIN bar_foo ON (bar_foo.foo_id = id )
JOIN bars ON (bars.id = bar_foo.bar_id )

returns a list like this:

id | name
---+-----
 1 | a
 1 | b
 2 | a
 2 | y
 2 | z
 3 | a
 3 | b
 3 | c
 3 | d

How to get the ids for which id must have at least a and b, and more generally the content of a given array ?

From the example above, I would get:

id | name
---+-----
 1 | a
 1 | b
 3 | a
 3 | b
 3 | c
 3 | d

Answer

For two values, you can use windowing boolean aggregation:

select *
from (
    select f.id, b.name, 
        bool_or(b.name = 'a') over(partition by id) has_a,
        bool_or(b.name = 'b') over(partition by id) has_b
    from foos f
    join bar_foo bf on bf.foo_id = f.id
    join bars b on b.id = bf.bar_id
) t
where has_a and has_b

A more generic approach uses array aggregation:

select *
from (
    select f.id, b.name, 
        array_agg(b.name) over(partition by id) arr_names
    from foos f
    join bar_foo bf on bf.foo_id = f.id
    join bars b on b.id = bf.bar_id
) t
where arr_names @> array['a', 'b']