SQLize Online / PHPize Online  /  SQLtest Online

create table users ( id int not null, username varchar(255) not null, primary key (id) ); create table projects ( id int not null, projectname varchar(255) not null, primary key (id) ); insert into users values (1, "Smith"); insert into projects values (1, "Matrix"); create table user_projects ( user_id int not null, project_id int not null, role varchar(255) not null ); insert into user_projects values (1, 1, "Manager"), (1, 1, "Worker"); -- { id: 1, "username": "some", "project_id": 1, "roles": ['Вышибала', 'Кто-то'] } select user_id, project_id, json_arrayagg( role ) roles from user_projects group by user_id, project_id; select json_object( 'id', user_id, 'username', username, 'project_id', project_id, 'roles', json_arrayagg(role) ) roles from user_projects join users on users.id = user_id group by user_id, project_id;