如何将一组扁平的树变成一棵有多个叶子的树？

递归 CTE ( rCTE ) 是遵循路径并识别树的所有节点的正确工具。但它不允许在递归项中进行聚合。有一个优雅的选择：

递归函数

CREATE OR REPLACE FUNCTION f_item_tree(_item_id int, _level int = 7)
  RETURNS jsonb
  LANGUAGE sql STABLE PARALLEL SAFE AS
$func$
SELECT CASE WHEN _level > 1
            THEN jsonb_agg(sub)
            ELSE CASE WHEN count(*) > 0 THEN to_jsonb(count(*) || ' - stopped recursion at max level') END
       END
FROM  (
   SELECT i.*, CASE WHEN _level > 1 THEN f_item_tree(i.item_id, _level - 1) END AS children
   FROM   joins j
   JOIN   items i ON i.item_id = j.child_id
   WHERE  j.item_id = _item_id
   ORDER  BY i.item_id
   ) sub
$func$;

db<>在这里摆弄

使用默认最大级别 (7) 调用：

SELECT jsonb_pretty(jsonb_strip_nulls(to_jsonb(sub))) AS tree
FROM  (
   SELECT *, f_item_tree(item_id) AS children
   FROM   items
   WHERE  item_id = 1  -- root item_id HERE
   ) sub;

产生您想要的结果：

{
    "title": "PARENT",
    "item_id": 1,
    "children": [
        {
            "title": "LEVEL 2",
            "item_id": 2,
            "children": [
                {
                    "title": "LEVEL 3.1",
                    "item_id": 3,
                    "children": [
                        {
                            "title": "LEVEL 4.1",
                            "item_id": 4
                        },
                        {
                            "title": "LEVEL 4.2",
                            "item_id": 5
                        }
                    ]
                },
                {
                    "title": "LEVEL 3.2",
                    "item_id": 6
                }
            ]
        }
    ]
}

只需 3 个级别即可调用：

SELECT jsonb_pretty(jsonb_strip_nulls(to_jsonb(sub))) AS tree
FROM  (
   SELECT *, f_item_tree(item_id, 3) AS children  -- max level HERE
   FROM   items
   WHERE  item_id = 1  -- root item_id HERE
   ) sub;

请注意递归过早停止时的特殊输出：

{
    "title": "PARENT",
    "item_id": 1,
    "children": [
        {
            "title": "LEVEL 2",
            "item_id": 2,
            "children": [
                {
                    "title": "LEVEL 3.1",
                    "item_id": 3,
                    "children": "2 - stopped recursion at max level"
                },
                {
                    "title": "LEVEL 3.2",
                    "item_id": 6
                }
            ]
        }
    ]
}

当然，外部jsonb_pretty()是可选的。

您可以将外部调用包装到另一个函数中或将其塞进同一个函数中，可能使用 PL/pgSQL 并使用IF. 这是品味和风格的问题。我在这里保持简单。

这是实际的递归，而rCTE 实际上只是iterate。你需要理解这个概念才能理解函数：它调用自己。每次调用减少最大值_level是最终终止递归的原因。

我为最后一个递归级别构建了特殊行为：如果最大级别切断了更多子级，该函数会插入一个计数和一个提示。

（可选！）调用jsonb_strip_nulls()...

从给定的 JSON 值中递归删除所有具有空值的对象字段。

删除空的子字段（带NULL值）。如果您想保留其他具有 NULL 值的字段，请考虑jsonb_set_lax()在函数中代替，就像下面使用纯 SQL 解决方案一样。

理想情况下，您有一个索引joins(item_id, child_id)- 或该索引隐式附带的UNIQUE约束。(item_id, child_id)

有关的：

• 对于每个类别，使用 PostgreSQL 递归 CTE 查找所有子类别中外键项的计数

纯 SQL

虽然只有一堆级别，但您也可以拼出级联子查询或具有聚合的非递归 CTE。

查询变得相当冗长，但与原始查询相比，性能应该仍然不错。很大程度上是因为我恢复了最初的查询方法。由于您对给定的根 ID 感兴趣，因此从根而不是叶子开始应该更有效- 坚持树作为隐喻。这样，我们只选择属于树的行。

按照您的方式，正在处理整个表的所有行，只是为了过滤最后源自给定根的一棵树。这是很多浪费的努力。

此外，我在选择所有相关节点后加入items一次，而不是像你那样每次都在递归术语中。

选择树的所有节点后，需要再次自顶向下聚合，从叶子到根，因为每个较低级别都集成了下一个较高级别的聚合数组。

最多查询4 ​​个级别以覆盖您的测试数据：

WITH RECURSIVE path AS (
   SELECT ARRAY[item_id, child_id] AS path, child_id AS item_id, 2 AS lvl
   FROM   joins
   WHERE  item_id = 1  -- root item_id HERE
   
   UNION ALL
   SELECT p.path || j.child_id, j.child_id, p.lvl + 1
   FROM   path  p
   JOIN   joins j ON j.item_id = p.item_id
   WHERE  p.lvl < 7  -- HARD limit
   )
, tree AS (
   SELECT p.*, to_jsonb(i) AS item
   FROM   path p
   JOIN   items i USING (item_id)
   ORDER  BY lvl, item_id
   )
, lvl4 AS (  -- leaf!
   SELECT path[3] AS item_id, jsonb_agg(item) AS children
   FROM   tree
   WHERE  lvl = 4
   GROUP  BY 1
   )
,  lvl3 AS (
   SELECT path[2] AS item_id
        , jsonb_agg(jsonb_set_lax(item, '{children}', children, true, 'return_target')) AS children
   FROM   tree t
   LEFT   JOIN lvl4 USING (item_id)
   WHERE  lvl = 3
   GROUP  BY 1
   )
,  lvl2 AS ( -- stem!
   SELECT jsonb_agg(jsonb_set_lax(item, '{children}', children, true, 'return_target')) AS children
   FROM   tree t
   LEFT   JOIN lvl3 USING (item_id)
   WHERE  lvl = 2
   )
SELECT i.item_id
     , jsonb_pretty(jsonb_set_lax(to_jsonb(i), '{children}', children, true, 'return_target')) AS tree
FROM   items i
LEFT   JOIN lvl2 ON true
WHERE  item_id = 1;  -- root item_id HERE

产生您想要的结果。也适用于更少的级别。或者（与您的原始版本不同）甚至是根本没有任何子项的裸根项。

如何扩展查询以允许更多级别应该很明显。我添加了7 个级别的版本以适应您WHERE level < 7的小提琴，以防万一：

db<>在这里摆弄