json - 在 SQL Server 中使用子项创建 JSON(Web 树的结构)

Question

请帮我创建一个查询，或者建议阅读什么。我在 SQL Server 表中有一个树结构。

当所有后代节点都变成嵌套的 JSON 对象时，我需要将表数据转换为 JSON 格式的网络树 - 有 child 。

我有这个数据表:

DROP TABLE IF EXISTS #tTree;

CREATE TABLE #tTree
(
     id INTEGER IDENTITY(1,1),
     text VARCHAR(256),
     parentId INTEGER,
     path VARCHAR(256),
     depth TINYINT,
     leaf TINYINT,
     expanded TINYINT
);

INSERT INTO #tTree (text, parentId, path, depth, leaf, expanded)
VALUES ('Category 1', null, '1', 1, null, 1),
       ('SubCategory 1', 1, '1,2', 2, null, 1),
       ('Element 1', 2, '1,2,3', 3, 1, null),
       ('Category 2', null, '4', 1, null, 1),
       ('SubCategory 2', 4, '4,5', 2, 1, null),
       ('SubCategory 3', 4, '4,6', 2, 1, null),
       ('Element 2', 4, '4,7', 2, null, 1),
       ('SubElement 1', 5, '4,5,8', 3, 1, null),
       ('SubSubCategory 1', 2, '1,2,9', 3, 1, null),
       ('Category 3', null, '10', 1, 1, null)

我需要获取带有 child 的 JSON:

[
   {
     "id":1,
     "text":"Category 1",
     "path":"1",
     "depth":1, 
     "expanded":1,
     "children":[{
        "id":2,
        "text":"SubCategory 1",
        "parentId":1,
        "path":"1,2",
        "depth":2,
        "expanded":1,
        "children":[
            {"id":3,"text":"Element 1","parentId":2,"path":"1,2,3","depth":3,"leaf":1},
            {"id":9,"text":"SubSubCategory 1","parentId":2,"path":"1,2,9","depth":3,"leaf":1}
        ]
     }]
    },
    {"id":10,"text":"Category 3","path":"10","depth":1,"leaf":1},
    {"id":4,
     "text":"Category 2",
     "path":"4",
     "depth":1,
     "expanded":1,
     "children":[
        {"id":5,
         "text":"SubCategory 2",
         "parentId":4,
         "path":"4,5",
         "depth":2,
         "expanded":1,
         "children":[
            {"id":8,"text":"SubElement 1","parentId":5,"path":"4,5,8","depth":3,"leaf":1}
         ]
        },
        {"id":6,"text":"SubCategory 3","parentId":4,"path":"4,6","depth":2,"leaf":1},
        {"id":7,"text":"Element 2","parentId":4,"path":"4,7","depth":2,"leaf":1}    
     ]
    }
]

也许这个查询可以以某种方式修改，但现在它没有“ child ”

;WITH cteTree AS
(
    SELECT
         tree.id
        ,tree.text
        ,tree.parentId
        ,tree.path
        ,tree.depth
        ,tree.leaf
        ,tree.expanded
    FROM 
        #tTree AS tree
    WHERE 
        parentId IS NULL

    UNION ALL

    SELECT
         tree.id
        ,tree.text
        ,tree.parentId
        ,tree.path
        ,tree.depth
        ,tree.leaf
        ,tree.expanded
    FROM 
        #tTree AS tree
    INNER JOIN 
        cteTree ON tree.parentId = cteTree.id
)
SELECT * 
FROM cteTree
ORDER BY path ASC
FOR JSON AUTO

Answer 1

不幸的是，很难在递归 CTE 中进行任何类型的循环聚合。这适用于 GROUP BY 和 FOR JSON。

我为此找到的唯一直接方法是(哦，太恐怖了!)标量 UDF，它会递归自身。

CREATE FUNCTION dbo.GetJson(@parentId int, @path nvarchar(1000), @depth int)
RETURNS nvarchar(max)
AS BEGIN

RETURN (
    SELECT
      t.id,
      t.text,
      t.parentId,
      path = CONCAT(@path + ',', t.id),
      depth = @depth + 1,
      t.leaf,
      t.expanded,
      children = JSON_QUERY(dbo.GetJson(t.id, CONCAT(@path + ',', t.id), @depth + 1))
    FROM tTree t
    WHERE EXISTS (SELECT t.parentId INTERSECT SELECT @parentId)  -- nullable compare
    FOR JSON PATH
);

END;

然后您可以这样做以获得预期的结果

SELECT dbo.GetJson(NULL, NULL, 0);

db<>fiddle