SQLize Online / PHPize Online  /  SQLtest Online

create table tags ( id serial, title varchar, color varchar ); insert into tags(title, color) values ('Бытовуха', '#4287f5'), ('Еденичка', '#444'), ('По выходным', '#f542cb'), ('Спорт', '#f5bc42'), ('Привычка', '#f57842'); create table tasks ( id serial, title varchar, score int ); insert into tasks(title, score) values ('Помыть посуду', 1), ('Помыть пол', 5), ('Сделать упражнения', 3), ('Спорт', 15); select * from tags; select * from tasks; create table tags_tasks ( tag_id int, task_id int ); insert into tags_tasks(tag_id, task_id) values (1, 1), (1, 2), (2, 1), (3, 2),(3, 4), (4, 3); select tags.id, tags.title, json_agg(json_build_object('id', tasks.id, 'title', tasks.title)) from tags join tags_tasks on tags_tasks.tag_id = tags.id join tasks on tasks.id = tags_tasks.task_id group by tags.id, tags.title;