Postgres contains in array function

The other day I was asked to build an SQL query to load all the users that has all of the given tags.

Let say we have the following tables:

users: email
tags: id, name
user_tags: email, tag_id

After thinking about it on SQL, I began digging around aggregation functions, group by, having, and all that stuff until I finally found the candle in the cave in PostgreSQL. Here is the answer:

select users.email, array_agg(tags.name)
from users
  join user_tags on users.email = user_tags.email
  join tags on tags.id = user_tags.tag_id
group by users.email
having '{1, 2}'::int[] <@ array_agg(tags.id);

Here is the result:

    email     |    array_agg
--------------+-----------------
 a@sample.com | {rails,ruby}
 b@sample.com | {rails,js,ruby}
(2 rows)

Look at the <@ method. That's the trick here. It first groups all the tags, then filters the rows where the array [1, 2] is contained by the grouping . There is another great function that is the way back: @>: the first parameter contains the second parameter.

Cheers!!!