nesting.sql

macdice=> select * from a;
┌────┬──────┐
│ id │ name │
├────┼──────┤
│  0 │ one  │
└────┴──────┘
(1 row)

macdice=> select * from b;
┌────┬───────┬──────┐
│ id │ name  │ a_id │
├────┼───────┼──────┤
│  1 │ two   │    0 │
│  2 │ three │    0 │
└────┴───────┴──────┘
(2 rows)

macdice=> select * from c;
┌────┬───────┬──────┐
│ id │ name  │ b_id │
├────┼───────┼──────┤
│  3 │ four  │    1 │
│  4 │ five  │    1 │
│  5 │ six   │    1 │
│  6 │ seven │    2 │
│  7 │ eight │    2 │
└────┴───────┴──────┘
(5 rows)

macdice=> WITH cs_per_b AS (
  SELECT b.id AS b_id,
         b.name AS b_name,
         b.a_id AS a_id,
         json_agg(json_build_object('id', c.id, 'name', c.name)) AS cs
    FROM c JOIN b ON c.b_id = b.id
GROUP BY b.a_id, b.id, b_name),
     bs_per_a AS (
  SELECT a.id AS a_id,
         a.name AS a_name,
         json_agg(json_build_object('id', cs_per_b.b_id,
                                    'name', cs_per_b.b_name,
                                    'cs', cs_per_b.cs)) AS bs
    FROM cs_per_b JOIN a ON a.id = cs_per_b.a_id
GROUP BY a.id, a.name)
SELECT json_agg(json_build_object('id', bs_per_a.a_id,
                                  'name', bs_per_a.a_name,
                                  'bs', bs_per_a.bs))
  FROM bs_per_a;
┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│                                                                                                                              json_agg                                                                                                                               │
├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤
│ [{"id" : 0, "name" : "one", "bs" : [{"id" : 1, "name" : "two", "cs" : [{"id" : 3, "name" : "four"}, {"id" : 4, "name" : "five"}, {"id" : 5, "name" : "six"}]}, {"id" : 2, "name" : "three", "cs" : [{"id" : 6, "name" : "seven"}, {"id" : 7, "name" : "eight"}]}]}] │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
(1 row)