Two array_agg at once

Question

I'm trying to get all likes/dislikes of multiple post. The problem can be summarized as:

• posts table: id, content, user_id (fk)
• users table: id, name
• likes table: post_id (fk), user_id (fk), is_liked, is_disliked

To fit my use case, I need to get the list of all likes/dislikes so I've first tried this:

SELECT 
 posts.id, posts.content, users.name as author
 ARRAY(SELECT user_id FROM likes WHERE is_liked and post_id = posts.id) as likes,
 ARRAY(SELECT user_id FROM likes WHERE is_disliked and post_id = posts.id) as dislikes
FROM 
 posts
 INNER JOIN users ON posts.user_id = users.id

This is clearly not optimized. That's why I've tried something with another join on likes table and the array_agg function but I can't figure out how to get two different arrays for likes and dislikes.

Answer 1

That should be simple with array_agg and additional FILTER clauses:

SELECT 
 posts.id, posts.content, users.name as author
 array_agg(likes.user_id) FILTER (WHERE likes.is_liked) as likes,
 array_agg(likes.user_id) FILTER (WHERE likes.is_disliked) as dislikes
FROM posts
 INNER JOIN users ON posts.user_id = users.id
 INNER JOIN likes ON likes.post_id = posts.id
GROUP BY posts.id, posts.content, users.name;

• Colin 't Hart: Available since Postgres 9.4, not enough people know about the filter clause.
• RobinFrcd: Indeed, I wasn't aware of it, thank you very much!