I’m not super experienced with SQL, but I’m trying to build a postgresql function that joins three tables and returns an array of nested json data.

I have been able to get close to what I need by joining the first two tables… but how the heck do you achieve the same thing with all three tables? I’m now going round in circles trying to nest joins, and my lack of experience with SQL is showing. :o)

Here are my (simplified) tables:

CREATE TABLE projects (
    id UUID PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE outputs (
    id UUID PRIMARY KEY,
    name TEXT NOT NULL,
    project_id UUID REFERENCES projects(id)
);

CREATE TABLE components (
    id UUID PRIMARY KEY,
    name TEXT NOT NULL,
    output_id UUID REFERENCES outputs(id)
);

Here’s the SQL I have so far, which lets me join the first two tables and returns json:

SELECT
  json_agg(structure) AS json_object
FROM
  (
    SELECT
      json_build_object(
        'id',
        p.id,
        'name',
        p.name,
        'outputs',
        json_agg(o.oj)
      ) AS structure
    FROM
      projects p
      LEFT JOIN (
        SELECT
          project_id,
          json_build_object('id', id, 'name', name) AS oj
        FROM
          outputs
      ) o ON o.project_id = p.id
    GROUP BY
      p.id
  ) structure;

That SQL gives me an array of results that look like this:

[
  {
    "id": "d47d0a2d-fe9a-4867-b39c-d328627358fa",
    "name": "Customer Service Campaign",
    "outputs": [
      {
        "id": "19c81778-f327-4cc4-bae4-2fc4879ed938",
        "name": "One more test"
      }
    ]
  }
]

But what I really need is an array of results that look like this:

[
  {
    "id": "d47d0a2d-fe9a-4867-b39c-d328627358fa",
    "name": "Test project",
    "outputs": [
      {
        "id": "19c81778-f327-4cc4-bae4-2fc4879ed938",
        "name": "Test output",
        "components": [
          {
            "id": "80f48b3b-53aa-458c-8c90-50afac077eac",
            "name": "Test component"
          }
        ]
      }
    ]
  }
]

Any ideas/suggestions/pointers/help would be amazing. Thanks!