Merge rows on join queries on one-to-many-CodePudding

I'm quite new to SQL and have two Postgresql tables :

CREATE TABLE project (
    id uuid DEFAULT uuid_generate_v4 (),
    name VARCHAR(100) NOT NULL,
    creator_id uuid NOT NULL
);

CREATE TABLE task (
    id uuid DEFAULT uuid_generate_v4 (),
    name VARCHAR(100) NOT NULL,
    project_id uuid NOT NULL,
);

I'm running a pretty simple join on it :

SELECT project.*, task.name as task_name 
FROM project 
INNER JOIN task ON task.project_id = $1
WHERE project.id =

Result is :

[
  {
    id: '5936d843-aca0-4453-ad24-a7b3a6b90393',
    name: 'Test project',
    creator_id: '2e0e73af-e824-46a2-89ee-c08cf9c5de7a',
    task_name: 'Test task'
  },
  {
    id: '5936d843-aca0-4453-ad24-a7b3a6b90393',
    name: 'Test project',
    creator_id: '2e0e73af-e824-46a2-89ee-c08cf9c5de7a',
    task_name: 'Test task 2'
  }
]

My question is, is that possible to merge those rows on id to have a result looking more like this :

[
  {
    id: '5936d843-aca0-4453-ad24-a7b3a6b90393',
    name: 'Test project',
    creator_id: '2e0e73af-e824-46a2-89ee-c08cf9c5de7a',
    tasks: [
      {
        task_name: 'Test task'
      },
      {
        task_name: 'Test task 2'
      }
  }
]

I know there is a few thing that can help me achieve that, like using COALESCE, json_build_object or json_agg. But this makes me build "complex" queries for something that looks pretty simple, so do you know if there's a simpler way to do this, or should I just take the first result and process it with my language of choice (here javascript) to merge as needed ?

CodePudding user response：

You need to group by project and aggregate project tasks.

SELECT p.*, 
       jsonb_agg(jsonb_build_object('task_name', t.name)) tasks
FROM project p 
INNER JOIN task t ON t.project_id = p.id
WHERE p.id = $1 -- or whatever your selection condition is
group by p.id, p.name, p.creator_id;

If project.id is primary key then

group by p.id, p.name, p.creator_id

can be simplified as

group by p.id;

I assume that the expected JSON array result is shaped in the logic tier or by something like an ORM. If you wish that (btw much better) the query itself returns the result as a JSON array, then

select jsonb_agg(to_josnb(t.*)) from
(
 ... the query above ...
) t;