Last Updated: February 25, 2016
· dangaytan

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, array_agg(
from users
  join user_tags on =
  join tags on = user_tags.tag_id
group by
having '{1, 2}'::int[] <@ array_agg(;

Here is the result:

    email     |    array_agg
--------------+----------------- | {rails,ruby} | {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.