如何获取包含多层嵌套子记录的所有父级记录？

需求是查询所有根动物记录，同时把所有层级的子记录以嵌套JSON格式返回，表结构和测试数据如下：

CREATE TABLE animal (
  id SERIAL PRIMARY KEY,
  animal_name VARCHAR(25),
  subanimal BIGINT[]
);

INSERT INTO animal VALUES
    (1, 'Mammal', '{2,3}'),
    (2, 'Person', '{}'),
    (3, 'Dog', '{}'),
    (4, 'Reptile', '{5}'),
    (5, 'Lizard', '{6,7}'),
    (6, 'Gecko', '{}'),
    (7, 'Chameleon', '{}'),
    (8, 'Birds', '{}');

你之前的代码逻辑有误，没有正确构建嵌套的JSON结构，关联方向和递归处理方式都不对。以下是正确的实现：

WITH RECURSIVE animal_hierarchy AS (
    -- 先处理叶子节点（没有子节点的记录）
    SELECT 
        id,
        animal_name,
        '[]'::jsonb AS subanimals
    FROM animal
    WHERE array_length(subanimal, 1) IS NULL OR array_length(subanimal, 1) = 0

    UNION ALL

    -- 递归向上构建父节点的嵌套子结构
    SELECT 
        parent.id,
        parent.animal_name,
        jsonb_agg(jsonb_build_object(
            'id', child.id,
            'animal_name', child.animal_name,
            'subanimals', child.subanimals
        )) AS subanimals
    FROM animal parent
    JOIN animal_hierarchy child ON child.id = ANY(parent.subanimal)
    GROUP BY parent.id, parent.animal_name
)
-- 筛选根节点：未出现在任何其他节点subanimal数组中的记录
SELECT 
    id AS parent_id,
    animal_name,
    subanimals
FROM animal_hierarchy
WHERE id NOT IN (SELECT unnest(subanimal) FROM animal WHERE array_length(subanimal, 1) > 0)
ORDER BY parent_id;

代码逻辑说明 #

1. 基础查询：先定位所有没有子节点的叶子记录，为它们生成空的subanimals JSON数组。
2. 递归处理：从父节点关联已经处理好的子节点，用jsonb_build_object组装子节点的完整结构，再通过jsonb_agg将多个子节点聚合为数组，作为父节点的subanimals。
3. 筛选根节点：根节点是那些没有被任何其他节点包含在subanimal数组里的记录，最后按parent_id排序输出。

执行后会得到你期望的结果：

parent_id | animal_name |                                                                     subanimals                                                                     
-----------+-------------+---------------------------------------------------------------------------------------------------------------------------------------------------
         1 | Mammal      | [{"id": 2, "animal_name": "Person", "subanimals": []}, {"id": 3, "animal_name": "Dog", "subanimals": []}]
         4 | Reptile     | [{"id": 5, "animal_name": "Lizard", "subanimals": [{"id": 6, "animal_name": "Gecko", "subanimals": []}, {"id": 7, "animal_name": "Chameleon", "subanimals": []}]}]
         8 | Birds       | []

内容的提问来源于stack exchange，提问作者Mike K