How to return nested json from three joined tables in a postgresql function?

Question

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": "Test project",
    "outputs": [
      {
        "id": "19c81778-f327-4cc4-bae4-2fc4879ed938",
        "name": "Test output"
      }
    ]
  }
]

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!

Answer 1

You can extend your current SQL query to include the components table.

try:

SELECT
  json_agg(project_structure) AS json_object
FROM
  (
    SELECT
      json_build_object(
        'id', p.id,
        'name', p.name,
        'outputs', json_agg(output_structure)
      ) AS project_structure
    FROM
      projects p
      LEFT JOIN (
        SELECT
          o.project_id,
          json_build_object(
            'id', o.id,
            'name', o.name,
            'components', json_agg(c.cj)
          ) AS output_structure
        FROM
          outputs o
          LEFT JOIN (
            SELECT
              output_id,
              json_build_object('id', id, 'name', name) AS cj
            FROM
              components
          ) c ON c.output_id = o.id
        GROUP BY
          o.id
      ) o ON o.project_id = p.id
    GROUP BY
      p.id
  ) project_structure;