Nesting PostgreSQL relational query results as JSON objects

Question

I have 3 tables in my database: parent, children and grandchildren. Since parent-children and children-grandchildren are related many-to-many, there are also 2 relational tables.

I have a query which I'd like to result in all parent elements with nested children and grandchildren rows (if any) as JSON objects:

SELECT
  p.*,
  COALESCE(json_agg(json_build_object(
    'child_id', c.child_id,
    'child_name', c.child_name
    -- * GET ALL grandchildren FOR THIS child HERE!
  )) FILTER (WHERE c.child_id IS NOT NULL), '[]')
  AS children
FROM parent p
LEFT JOIN parent_children pc
ON pc.parent_id = p.parent_id
LEFT JOIN children c
ON c.child_id = pc.child_id
GROUP BY p.parent_id;

This query, later on in my JS code, ends up as a nice JSON object:

[
  {
    "parent_id": 1,
    "parent_name": "whatever",
    "children": [
      {
        "child_id": 1,
        "child_name": "le child"
      },
      {
        "child_id": 2,
        "child_name": "le second child"
      }
    ]
  },
  {
    "parent_id": 2,
    "parent_name": "second",
    "children": []
  }
]

As commented in my query, I'd like to add all grandchildren rows for each children element. I have a query which is the same like the one above, except it is using different tables in order to fetch that relation:

SELECT
  c.*,
  COALESCE(json_agg(json_build_object(
    'grandchild_id', gc.grandchild_id,
    'grandchild_name', gc.grandchild_name
  )) FILTER (WHERE gc.grandchild_id IS NOT NULL), '[]')
  AS grandchildren
FROM children c
LEFT JOIN children_grandchildren cg
ON cg.child_id = c.child_id
LEFT JOIN grandchildren g
ON g.grandchild_id = gc.grandchild_id
GROUP BY c.child_id;

I'm not sure is it possible and if it is, how exactly to nest my queries to get results like:

[
  {
    "parent_id": 1,
    "parent_column": "whatever",
    "children": [
      {
        "child_id": 1,
        "child_name": "le child",
        "grandchildren": [
          {
            "grandchild_id": 1,
            "grandchild_name": "foo"
          },
          {
            "grandchild_id": 2,
            "grandchild_name": "bar"
          }
        ]
      },
      {
        "child_id": 2,
        "child_name": "le second child",
        "grandchildren": []
      }
    ]
  },
  {
    "parent_id": 2,
    "parent_name": "second",
    "children": []
  }
]

Answer 1

I managed to solve this by simply LEFT JOINing the grandchildren table:

SELECT
  p.*,
  COALESCE(json_agg(json_build_object(
    'child_id', c.child_id,
    'child_name', c.child_name
    -- * GET ALL grandchildren FOR THIS child HERE!
  )) FILTER (WHERE c.child_id IS NOT NULL), '[]')
  AS children
FROM parent p
LEFT JOIN parent_children pc
ON pc.parent_id = p.parent_id
LEFT JOIN children c
ON c.child_id = pc.child_id
LEFT JOIN (
  SELECT
    c.*,
    COALESCE(json_agg(json_build_object(
      'grandchild_id', g.grandchild_id,
      'grandchild_name', g.grandchild_name
    ))
    FILTER (WHERE g.grandchild_id IS NOT NULL), '[]') 
    AS grandchildren
  FROM children c
  LEFT JOIN children_grandchildren cg
  ON c.child_id = cg.child_id
  LEFT JOIN grandchildren g
  ON g.grandchild_id = cg.grandchild_id
  GROUP BY c.child_id
) grandchildren ON grandchildren.child_id = c.child_id
GROUP BY p.parent_id;

This produces the JSON object with relations like I needed it:

[
  {
    "parent_id": 1,
    "parent_column": "whatever",
    "children": [
      {
        "child_id": 1,
        "child_name": "le child",
        "grandchildren": [
          {
            "grandchild_id": 1,
            "grandchild_name": "foo"
          },
          {
            "grandchild_id": 2,
            "grandchild_name": "bar"
          }
        ]
      },
      {
        "child_id": 2,
        "child_name": "le second child",
        "grandchildren": []
      }
    ]
  },
  {
    "parent_id": 2,
    "parent_name": "second",
    "children": []
  }
]