Multi-level table aggregation in Postgres

Question

I have a 4-level hierarchy that I would like to retrieve rolled up to the top level.

Lets say you have Class -> Order -> Family -> Species.

I am looking for the following output:

{ 
  classes: [{
    id: 1,
    name: "Class A",
    orders: [{
      id: 1,
      name: "Class A - Order 1",
      families: [{
        id: 1,
        name: "Class A - Order 1 - Family I"
        species: [{
          id: 1,
          name: "Class A - Order 1 - Family I - Species 1"
        }]
      }]
    }]
  }]
}

It's easy enough to get the data using a massive join aka

SELECT classes.id as class_id, orders.id as order_id, families.id as family_id, species.id as species_id
FROM species
JOIN families ON families.id = species.family_id
JOIN orders ON orders.id = families.order_id
JOIN classes ON classes.id = orders.class_id

But that gives flat table structure, not the rolled up one I'm looking for.

class_id order_id family_id species_id
1        1        1         1
1        1        1         2
1        1        1         3

I tried using LATERAL joins which are sub-queries evaluated in context. So something along the lines:

SELECT classes.id, array_agg(orders_sub.id) as orders
FROM classes,
LATERAL (
  SELECT orders.id
  FROM orders
  WHERE classes.id = orders.class_id
) AS orders_sub
group by classes.id;

which produces:

id  orders
1   {1,2}

But I'm having trouble getting down multiple levels and rolling up whole records.

Bonus: If we can eliminate elements with empty relations, e.g. families without any species that'd be great.

Background: This is a reporting API and so far we have been serializing Rails ActiveRecord objects, which is obviously very slow for large amounts of data (100k-1M range I think usually). So I'd love leverage the JSON functionality that Postgres offers

Answer 1

Json is the only reasonable format for the desired structured output. You have to build a hierarchical query to get a hierarchical structure as a result.

select
    jsonb_agg(jsonb_build_object(
        'id', id, 
        'class', name, 
        'orders', orders)
        order by id
    ) as classes
from classes
join (
    select
        class_id,
        jsonb_agg(jsonb_build_object(
            'id', id, 
            'order', name, 
            'families', families)
            order by id
        ) as orders
    from orders
    join (
        select 
            order_id, 
            jsonb_agg(jsonb_build_object(
                'id', id, 
                'family', name,
                'species', species)
                order by id
            ) as families
        from families
        join (
            select 
                family_id, 
                jsonb_agg(jsonb_build_object(
                    'id', id, 
                    'species', name)
                    order by id
                ) as species
            from species
            group by family_id
            ) s on id = family_id
        group by order_id
        ) f on id = order_id
    group by class_id
    ) o on id = class_id

See the demo on exemplary data: DbFiddle.