Create JSON with children in SQL Server (structure for web tree)

Question

I have a tree-structure in a SQL Server table.

I need to convert table data into JSON format for web-tree - with children, when all descendant nodes become nested JSON-objects.

I have this data table:

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)

I need to get JSON with children:

[
   {
     "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}    
     ]
    }
]

Maybe this query can be modified somehow, but now it's without "childrens"

;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

Unfortunately, it is very difficult to do any kind of looped aggregation in a recursive CTE. This applies to both GROUP BY and FOR JSON.

The only straight-forward method I have found for this is (oh the horror!) a scalar UDF, which recurses on itself.

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;

You can then do this to get your intended result

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

db<>fiddle

Answer 2

An alternative using a work table and nested iteration:

DROP TABLE IF EXISTS #Work;

SELECT 
    TT.*, 
    jsn = CONVERT(nvarchar(max), NULL) 
INTO #Work 
FROM #tTree AS TT;

CREATE UNIQUE CLUSTERED INDEX cuq ON #Work (depth, parentId, id);

DECLARE @depth integer = (SELECT MAX(TT.depth) FROM #tTree AS TT);

WHILE @depth >= 1
BEGIN
    UPDATE W
    SET W.jsn =
        (
            SELECT 
                W.*, 
                children = 
                    JSON_QUERY
                    (
                        (
                            SELECT 
                                N'[' + 
                                STRING_AGG(W2.jsn, N',') 
                                    WITHIN GROUP (ORDER BY W2.id) + 
                                N']'
                            FROM #Work AS W2
                            WHERE 
                                W2.depth = @depth + 1
                                AND W2.parentId = W.id
                        )
                    )
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        )
    FROM #Work AS W
    WHERE 
        W.depth = @depth
        AND W.jsn IS NULL;

    SET @depth -= 1;    
END;

SELECT 
    N'[' + 
    STRING_AGG(W.jsn, N',') 
        WITHIN GROUP (ORDER BY W.id) + 
    N']'
FROM #Work AS W
WHERE 
    W.depth = 1;

db<>fiddle