如何將一組扁平的樹變成一棵有多個葉子的樹？
我們有這個漂亮的 Postgres 樹生成器。然而，它會同時產生一棵樹的切割，而不是一棵整棵樹：
item_id jsonb_pretty 1 { "title": "PARENT", "item_id": 1, "children": { "title": "LEVEL 2", "item_id": 2, "children": { "title": "LEVEL 3.2", "item_id": 6 } } } 1 { "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 } } } } 1 { "title": "PARENT", "item_id": 1, "children": { "title": "LEVEL 2", "item_id": 2, "children": { "title": "LEVEL 3.1", "item_id": 3, "children": { "title": "LEVEL 4.2", "item_id": 5 } } } }我想用這樣的數組從中取出一個樹對象：
1 { "title": "PARENT", "item_id": 1, "children": [{ "title": "LEVEL 2", "item_id": 2, "children": [{ "title": "LEVEL 3.2", "item_id": 6 }, { "title": "LEVEL 3.1", "item_id": 3, "children": [{ "title": "LEVEL 4.1", "item_id": 4 }, { "title": "LEVEL 4.2", "item_id": 5 }] }] }] }這是生成器：
CREATE TABLE items ( item_id serial PRIMARY KEY, title text ); CREATE TABLE joins ( id serial PRIMARY KEY, item_id int, child_id int ); INSERT INTO items (item_id,title) VALUES (1,'PARENT'), (2,'LEVEL 2'), (3,'LEVEL 3.1'), (4,'LEVEL 4.1'), (5,'LEVEL 4.2'), (6,'LEVEL 3.2'); INSERT INTO joins (item_id, child_id) VALUES (1,2), (2,3), (3,4), (3,5), (2,6); WITH RECURSIVE t(item_id, json, level) AS ( SELECT item_id, to_jsonb(items), 1 FROM items WHERE NOT EXISTS ( SELECT 2 FROM joins WHERE items.item_id = joins.item_id ) UNION ALL SELECT parent.item_id, to_jsonb(parent) || jsonb_build_object( 'children', t.json ), level + 1 FROM t JOIN joins AS j ON t.item_id = j.child_id JOIN items AS parent ON j.item_id = parent.item_id WHERE level < 7 ) SELECT item_id, jsonb_pretty(json) FROM t WHERE item_id = 1;我將如何更改這樣的生成器以在 Postgres 13+ 中生成一棵樹？
遞歸 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)有關的：
純 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<>在這裡擺弄