PostgreSQL材料化路径/Ltree转换为分层JSON对象

Question

我使用PostgreSQL的ltree模块构建了这个材料化路径树结构。

• id1
• id1.id2
• id1.id2.id3
• id1.id2.id5
• id1.id2.id3.id4 ... 等等

我可以使用ltree轻松获取整个树或特定路径/子路径中的所有节点，但这样做时，自然而然地会得到许多行（最终相当于一个节点数组/切片.. Golang/无论您使用哪种编程语言）。

我的目标是作为分层JSON树对象获取树 - 最好从某个起始和结束路径/点开始。

{
  "id": 1,
  "path": "1",
  "name": "root",
  "children": [
    {
      "id": 2,
      "path": "1.2",
      "name": "Node 2",
      "children": [
        {
          "id": 3,
          "path": "1.2.3",
          "name": "Node 3",
          "children": [
            {
              "id": 4,
              "path": "1.2.3.4",
              "name": "Node 4",
              "children": [

              ]
            }
          ]
        },
        {
          "id": 5,
          "path": "1.2.5",
          "name": "Node 5",
          "children": [

          ]
        }
      ]
    }
  ]
}

我知道在Golang中，对于线性（非分层）的行/数组/切片结果集，我当然可以解析路径并在那里进行必要的业务逻辑以创建此JSON。但如果有一种方便的方法可以直接在PostgreSQL中实现这一点，那肯定会更好。

那么，在PostgreSQL中如何将ltree树结构输出为json - 可能是从起始到结束路径？

如果您不了解ltree，则可能需要将问题更加概括为“材料化路径树到分层json”

此外，我正在考虑在所有节点上添加parent_id以及ltree路径，因为至少那样我就可以使用该id进行递归调用来获取JSON。同时我也想在parent_id上放置触发器来管理路径（保持更新），基于当父ID发生更改时-我知道这又是另一个问题，但或许你也可以告诉我你的意见？

我希望有些天才可以帮助我解决这个问题。:)

为了您的方便，这里提供了一个示例创建脚本，您可以使用它来节省时间：

CREATE TABLE node
(
  id bigserial NOT NULL,
  path ltree NOT NULL,
  name character varying(255),
  CONSTRAINT node_pkey PRIMARY KEY (id)
);

INSERT INTO node (path,name) 
VALUES ('1','root');

INSERT INTO node (path,name) 
VALUES ('1.2','Node 1');

INSERT INTO node (path,name) 
VALUES ('1.2.3','Node 3');

INSERT INTO node (path,name) 
VALUES ('1.2.3.4','Node 4');

INSERT INTO node (path,name) 
VALUES ('1.2.5','Node 5');

- Dac0d3r

Answer 1

我能够找到并稍作更改，使其适用于ltree的材料化路径，而不是像邻接树结构一样通常使用的父ID。虽然我仍希望有更好的解决方案，但我想这个方法可以完成任务。我感觉我必须添加parent_id以外的ltree路径，因为这当然不如引用parent id快。好吧，这个solution的人功不可没，这里是我的稍微修改了的代码，使用ltree的子路径、ltree2text和nlevel来实现完全相同的功能：

WITH RECURSIVE c AS (
    SELECT *, 1 as lvl
    FROM node
    WHERE id=1
  UNION ALL
    SELECT node.*, c.lvl + 1 as lvl
    FROM node
    JOIN c ON ltree2text(subpath(node.path,nlevel(node.path)-2 ,nlevel(node.path))) = CONCAT(subpath(c.path,nlevel(c.path)-1,nlevel(c.path)),'.',node.id)
),
maxlvl AS (
  SELECT max(lvl) maxlvl FROM c
),
j AS (
    SELECT c.*, json '[]' children
    FROM c, maxlvl
    WHERE lvl = maxlvl
  UNION ALL
    SELECT (c).*, json_agg(j) children FROM (
      SELECT c, j
      FROM j
      JOIN c ON ltree2text(subpath(j.path,nlevel(j.path)-2,nlevel(j.path))) = CONCAT(subpath(c.path,nlevel(c.path)-1,nlevel(c.path)),'.',j.id)
    ) v
    GROUP BY v.c
)
SELECT row_to_json(j)::text json_tree
FROM j
WHERE lvl = 1;

这个解决方案存在一个很大的问题，就是目前为止...请查看下面的图片以获取错误信息（节点5缺失）：

- Dac0d3r

---

你知道已经有一段时间了，但你是否想出了正确的查询吗？ - a_b

你解决了 Node 5 的问题吗？ - mbadawi23

Answer 2

节点5没有显示的原因是它是一个叶子节点，不在最大层级上，并且随后的连接条件将其排除。

递归遍历树的真正基本情况是一个叶子节点。通过从最大层级开始，这隐含地选择了所有叶子节点，但错过了出现在较低层级的叶子节点。以下是我们想要用伪代码实现的内容：

for each node:
   if node is leaf, then return empty array
   else return the aggregated children

虽然我发现用SQL表达这个很难。但我使用了相同的策略，从最大级别开始逐级向上移动。不过，当我在最大级别之上时，我添加了一些代码来处理叶节点基本情况。

这是我想到的：

WITH RECURSIVE c AS (
  SELECT
    name,
    path,
    nlevel(path) AS lvl
  FROM node
),
maxlvl AS (
  SELECT max(lvl) maxlvl FROM c
),
j AS (
  SELECT
    c.*,
    json '[]' AS children
  FROM c, maxlvl
  WHERE lvl = maxlvl
  UNION ALL
  SELECT
    (c).*,
    CASE
      WHEN COUNT(j) > 0 -- check if returned record is null
        THEN json_agg(j) -- if not null, aggregate
      ELSE json '[]' -- if null, then we are a leaf, so return empty array
    END AS children
  FROM (
    SELECT
      c,
      CASE
        WHEN c.path = subpath(j.path, 0, nlevel(j.path) - 1) -- c is a parent of the child
          THEN j
        ELSE NULL -- if c is not a parent, return NULL to trigger base case
      END AS j
    FROM j
    JOIN c ON c.lvl = j.lvl - 1
  ) AS v
  GROUP BY v.c
)
SELECT row_to_json(j)::text AS json_tree
FROM j
WHERE lvl = 1;

我提供的解决方案仅使用路径path（以及从路径派生的级别）。它不需要name或id来正确递归。

以下是我的结果（我包含了一个节点6，以确保我处理了同一级别上的多个叶节点）:

{
  "name": "root",
  "path": "1",
  "lvl": 1,
  "children": [
    {
      "name": "Node 1",
      "path": "1.2",
      "lvl": 2,
      "children": [
        {
          "name": "Node 5",
          "path": "1.2.5",
          "lvl": 3,
          "children": []
        },
        {
          "name": "Node 3",
          "path": "1.2.3",
          "lvl": 3,
          "children": [
            {
              "name": "Node 6",
              "path": "1.2.3.4",
              "lvl": 4,
              "children": []
            },
            {
              "name": "Node 4",
              "path": "1.2.3.4",
              "lvl": 4,
              "children": []
            }
          ]
        }
      ]
    }
  ]
}

- Herman J. Radtke III