我有一个4级层次结构，我想检索卷起到最高级别。
假设你有类->秩序->家庭->物种。
我正在寻找以下输出：

{ 
  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"
        }]
      }]
    }]
  }]
}

字符串
使用大规模连接（也称为

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

型
但这给了平板结构，而不是我正在寻找的卷起的一个。

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

型
我尝试使用LATERAL连接，这是在上下文中评估的子查询。所以大致沿着这样的：

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;

型
其产生：

id  orders
1   {1,2}

型
但我在下多个层和卷起整个记录时遇到了麻烦。
奖励：如果我们可以消除具有空关系的元素，例如没有任何物种的家族就好了
背景：这是一个报告API，到目前为止，我们一直在序列化Rails ActiveRecord对象，这对于大量数据（我认为通常是100 k-1 M范围）显然非常慢。所以我喜欢利用Postgres提供的JSON功能