sql - Postgres 中的多级表聚合

Question

我有一个 4 级层次结构，我想将其检索到顶层。

假设您有 Class -> Order -> Family -> Species。

我正在寻找以下输出:

{ 
  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 对象，这对于大量数据(我认为通常是 100k-1M 范围)来说显然非常慢。所以我喜欢利用 Postgres 提供的 JSON 功能

Answer 1

Json 是所需结构化输出的唯一合理格式。您必须构建分层查询才能获得分层结构。

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

请参阅有关示例数据的演示:DbFiddle.