Blender's questions

我们有这个漂亮的 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+ 中生成一棵树？

所以我想限制树深度遍历（作为最简单的递归杀手工具）。将规范化记录转换为树的示例代码：

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) AS (
        SELECT item_id, to_jsonb(items)
        FROM items
        WHERE NOT EXISTS (
                SELECT 1
                FROM joins
                WHERE items.item_id = joins.item_id
        )
        UNION ALL
        SELECT parent.item_id, to_jsonb(parent) || jsonb_build_object( 'children', t.json )
        FROM t
        JOIN joins AS j
                ON t.item_id = j.child_id
        JOIN items AS parent
                ON j.item_id = parent.item_id
)
SELECT item_id, jsonb_pretty(json)
FROM t
WHERE item_id = 1;

由于这个美丽的答案，它起作用了。现在想象我们有

INSERT INTO joins (item_id, child_id) VALUES
  (1,2),
  (2,3),
  (3,1);

所以我们得到了一个循环1->2->3->1，我想使用限制器来限制它的输出——我希望我的所有树都不大于我给这个树构造函数设置的深度 X。如何在 postgres 13+ 中做这样的事情？