PostgreSQL: Aggregate into array and join

Question

Kind of new to pgsql, what i'm looking for is a bit of common need, I think, but I still wasn't able to find a solution.

I have 3 tables:
users

user_id|username|password
-------|--------|--------
   1   | guest  | blabla
   2   | admin  | blabla

roles

role_id|name | descr
-------|-----|--------
   1   |role1|role one
   2   |role2|role two

user_roles

user_id|role_id
-------|-------
   2   |   1
   2   |   2

I want to display a table of user along with all its' roles, my guess (feel free to correct me), the way to do that would be:

Group user roles into array:
select user_id,array_agg(role_id) from user_roles group by user_id where user_id = 2;
Somehow join the array of array_agg(role_id) into roles to select the role names.

As you can see, I'm a bit confused on how to do #2. Is this the best way to do what I want? Is there something wrong with how I've build my db tables?

Thanks!

JGH · Accepted Answer · 2017-07-11 19:19:21Z

2

To achieve your query, you would first do the join between the tables and at last aggregate on the name column. Note that it may be prettier using string_agg(r.name,',')

select ur.user_id,array_agg(r.name) 
from user_roles ur, 
     roles r
where ur.role_id = r.role_id
 and ur.user_id = 2
group by ur.user_id;

edited Jul 11, 2017 at 19:19

answered Jul 11, 2017 at 19:12

JGH

18.2k5 gold badges40 silver badges57 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PostgreSQL: Aggregate into array and join

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related