How to make a selection with a many-to-many relationship?

Question

PostgreSQL

Good afternoon!
I used Laravel Eloquent ORM for a long time and already forgot SQL, now, due to events, I need to write a query in pure SQL, and I had a problem with this.
The database is used by PostgreSQL 9.5
There are the following tables:
user :
- id
- name
-------------
role
- id
- name
-------------
user_role
- id
- user_id
- role_id
-------------
Relationships between tables are properly established.
It is required to receive the list of users and their roles. That is, the output should be the following:

[
  {
     id: 1,
     name: "user_name_1"
     roles: [
        {
           id: 1,
           name: "role_name_1"
        },
        {
           id: 2,
           name: "role_name_2"
        }
     ]
  },
  {
     id: 2,
     name: "user_name_2"
     roles: [
        {
           id: 2,
           name: "role_name_2"
        },
        {
           id: 3,
           name: "role_name_3"
        }
     ]
  },
]

Now I wrote this query:

SELECT user.id, user.name, array_agg(user_role.role_id) as role_id FROM user JOIN user_role ON user_role.user_id=user.id WHERE users.id IN (1,2) GROUP BY users.id

At the output I get the following, which is not quite what I need:

[
  {
    id: 1,
    name: "user_name_1",
    role_id: "1,2"
  },
  {
    id: 2,
    name: "user_name_2",
    role_id: "2,3"
  }
]

Please tell me how to write the correct request.

Answer 1

The desired result is not obtained at the output of the RAW query. When you build a query through the ORM, eloquent can make multiple queries, then loop through the results and collect everything into one result object. That is, obtaining the object structure you need lies on the server side.