How to condense returned rows of users with multiple roles

I’m trying to wrap my head around this logic, but simply having a tough time. While trying to keep the database normalized, users can have multiple roles (example shows 2, but could have more than 2, currently have 6 total roles), and I’d like to return data to make it easily viewed on a front end UI.

Tables simplified:

USERS

id	first_name
1	john
2	sarah

ROLES

id	name
1	admin
2	manager
3	scorekeeper

USER_ROLE

user_id	role_id
1	1
2	2
2	3

Running this Postgres query will return 3 rows:

SELECT roles.name, users.first_name, users.id AS uid
FROM users
  INNER JOIN user_role ON user_role.user_id = users.id
  INNER JOIN roles ON roles.id = user_role.role_id
ORDER BY users.id;

CURRENT OUTPUT

[
  { name: ‘admin’, uid: 1, first_name: 'john' },
  { name: ‘manager’, uid: 2, first_name: 'sarah' },
  { name: ‘scorekeeper’, uid: 2, first_name: 'sarah' },
] 

DESIRED OUTPUT

[
  { uid: 1, first_name: 'john’, role_list: [‘admin’] },
  { uid: 2, first_name: 'sarah’, role_list: [‘manager’, ‘scorekeeper’] },
]

I’m looking to have my data formatted a little different – basically merging the rows on the roles.name and then adding a new “column” of the combined roles.names. This begs the question, should I just create a “role_list” column stored as an array of strings? Of course this denormalizes things, but is probably simpler to execute.

I’m also using node.js, and trying to format the CURRENT OUTPUT in javascript gets tricky when trying to loop through and “look ahead” to see if the uid has multple roles.

Any guidance would be appreciated – or even if I need to rethink my logic in the database design.

Answer

You simply want aggregation:

SELECT u.id, u.first_name, ARRAY_AGG(r.name) as roles
FROM users u 
JOIN user_role ur ON u4.user_id = u.id 
JOIN roles r ON r.id = ur.role_id
GROUP BY u.id;

Note the use of table aliases. These make the query easier to write and to read.