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!!!
Written by Daniel Alejandro Gaytán Valencia
Related protips
Have a fresh tip? Share with Coderwall community!
Post
Post a tip
Best
#Sql
Authors
Sponsored by #native_company# — Learn More
#native_title#
#native_desc#