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

Does this answer your question? "How to generate hierarchical JSON data with Microsoft SQL Server 2016?", "T-SQL Hierarchical Json Result for Employee Org Chart", "Generalized way to extract JSON from a relational database?", … — outis
– outis, Commented Jun 21, 2022 at 20:27

Charlieface · Accepted Answer · 2022-06-21 20:28:14Z

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

answered Jun 21, 2022 at 20:28

Charlieface

78.8k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Paul White · Accepted Answer · 2022-07-19 18:29:01Z

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

Paul White · Accepted Answer · 2022-07-24 17:08:15Z

This is a recursive solution. It constructs per-row json objects in path order, adding 'children' elements as it goes, and closing arrays and objects when necessary. The final result is a concatenation of the individual objects, wrapped as an array:

WITH 
    Recursion AS
    (
        -- Anchor part
        SELECT 
            TT.id, 
            depth = 1,
            rpath = CONVERT(nvarchar(4000), TT.id),
            has_children = 
                IIF
                (
                    EXISTS
                    (
                        SELECT C.* 
                        FROM #tTree AS C 
                        WHERE C.parentId = TT.id
                    ), 
                    1, 0
                ),
            element = 
                (
                    SELECT 
                        TT.id,
                        TT.[text], 
                        TT.[path], 
                        TT.depth,
                        TT.expanded
                    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
                )
        FROM #tTree AS TT
        WHERE TT.parentID IS NULL

        UNION ALL 

        -- Recursive part
        SELECT
            TT.id,
            depth = R.depth + 1,
            rpath = CONCAT_WS(N'.', R.rpath, TT.id),
            has_children = 
                IIF
                (
                    EXISTS
                    (
                        SELECT C.* 
                        FROM #tTree AS C 
                        WHERE C.parentId = TT.id
                    ), 
                    1, 0
                ),
            element = 
                (
                    SELECT
                        TT.id,
                        TT.[text], 
                        TT.[path], 
                        TT.depth,
                        TT.expanded
                    FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
                )
        FROM Recursion AS R
        JOIN #tTree AS TT
            WITH (FORCESEEK)
            ON TT.parentID = R.id
    ),
    AddNextDepth AS
    (
        SELECT
            R.*,
            NextDepth = 
                LEAD(R.depth) OVER (
                    ORDER BY R.rpath)
        FROM Recursion AS R
    ),
    ModifiedTree AS
    (
        SELECT 
            ND.rpath,
            element =
                CONCAT
                (
                    IIF
                    (
                        ND.has_children = 0,
                        ND.element,
                        -- Insert "children" element
                        STUFF
                        (
                            ND.element,
                            LEN(ND.element),
                            1,
                            N',"children":['
                        )
                    ),
                    -- Close previously opened array(s) if necessary
                    REPLICATE
                    (
                        N']}', 
                        -- Number of closures needed
                        ND.depth - ISNULL(ND.NextDepth, 1)
                    ),
                    -- Add comma if no children and not the last line
                    IIF
                    (
                        ND.has_children = 0 AND ND.NextDepth IS NOT NULL, 
                        N',', 
                        N''
                    )
                )
        FROM AddNextDepth AS ND
    )
-- Concatenate objects in path order and add array wrapper
SELECT
    CONCAT
    (
        N'[',
        STRING_AGG(MT.element, N'') 
            WITHIN GROUP (ORDER BY MT.rpath),
        N']'
    )
FROM ModifiedTree AS MT
OPTION (MAXRECURSION 0);

Output:

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

db<>fiddle

Paul White · Accepted Answer · 2022-07-25 15:12:23Z

A second recursive solution.

This one works by starting at the root nodes, constructing a list of any children. It's not possible to resolve recursive child references immediately, but we can do it a bit at time.

Each recursive step resolves the children available at that level, and may add new unresolved children if the new row also has children. Eventually, all chld references are resolved.

WITH R AS
(
    -- Anchor: root elements (parent is null)
    SELECT
        Roots.id,
        Roots.[path],
        Roots.children,
        element = 
        (
            SELECT Roots.*
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
        )
    FROM 
    (
        SELECT 
            TT.id, TT.[text], TT.[path],
            TT.depth, TT.leaf, TT.expanded,
            -- List of child IDs to resolve during recursion
            children = 
            (
                SELECT STRING_AGG(TT2.id, N',') 
                FROM #tTree AS TT2 WITH (FORCESEEK)
                WHERE TT2.parentId = TT.id
            )
        FROM #tTree AS TT 
        WHERE TT.parentId IS NULL
    ) AS Roots
    
    UNION ALL

    SELECT
        Nodes.id,
        Nodes.[path],
        Nodes.children,
        element =
        (
            -- Resolve children
            REPLACE
            (
                Nodes.element,
                -- Placeholder
                CONCAT(N'"children":"', Nodes.resolving, N'"'),
                -- Construct array of children
                CONCAT
                (
                    N'"children":',
                    (
                        SELECT 
                            TT.id, TT.[text], TT.[path], 
                            TT.depth, TT.leaf, TT.expanded,
                            Nodes.children
                        -- Split the ID list and join to child records
                        FROM STRING_SPLIT(Nodes.resolving, N',') AS SS
                        JOIN #tTree AS TT
                            WITH (FORCESEEK)
                            ON TT.id = CONVERT(integer, SS.[value])
                        FOR JSON PATH
                    )
                )
            )
        )
    FROM 
    (
        SELECT
            TT.*,
            -- Children we are resolving now
            resolving = R.children,
            R.element,
            -- New list of children to resolve later
            children = 
                STUFF
                (
                    (
                        -- Cannot use aggregates in the recurive part
                        SELECT CONCAT(N',', TT2.id) 
                        FROM #tTree AS TT2
                            WITH (FORCESEEK)
                        WHERE TT2.parentId = TT.id
                        FOR XML PATH (''), TYPE
                    ).value('(./text())[1]', 'nvarchar(4000)'),
                    1, 1, N''
                ),
            -- Any node at the same parent level will do
            -- Avoids duplicated work (see WHERE clause)
            rn = ROW_NUMBER() OVER (ORDER BY TT.parentId)
        FROM R
        JOIN #tTree AS TT
            ON TT.parentId = R.id
    ) AS Nodes
    WHERE Nodes.rn = 1
)
-- Finished nodes are those with no children
-- Concatenate and wrap as a JSON array
SELECT 
    CONCAT
    (
        N'[',
        STRING_AGG(R.element, N',')
            WITHIN GROUP (ORDER BY R.[path]),
        N']'
    )
FROM R
WHERE R.children IS NULL
OPTION (MAXRECURSION 0);

Output:

[
  {
    "id": 1,
    "text": "Category 1",
    "path": "1",
    "depth": 1,
    "expanded": 1,
    "children": [
      {
        "id": 2,
        "text": "SubCategory 1",
        "path": "1,2",
        "depth": 2,
        "expanded": 1,
        "children": [
          {
            "id": 3,
            "text": "Element 1",
            "path": "1,2,3",
            "depth": 3,
            "leaf": 1
          },
          {
            "id": 9,
            "text": "SubSubCategory 1",
            "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",
        "path": "4,5",
        "depth": 2,
        "leaf": 1,
        "children": [
          {
            "id": 8,
            "text": "SubElement 1",
            "path": "4,5,8",
            "depth": 3,
            "leaf": 1
          }
        ]
      },
      {
        "id": 6,
        "text": "SubCategory 3",
        "path": "4,6",
        "depth": 2,
        "leaf": 1,
        "children": [
          {
            "id": 8,
            "text": "SubElement 1",
            "path": "4,5,8",
            "depth": 3,
            "leaf": 1
          }
        ]
      },
      {
        "id": 7,
        "text": "Element 2",
        "path": "4,7",
        "depth": 2,
        "expanded": 1,
        "children": [
          {
            "id": 8,
            "text": "SubElement 1",
            "path": "4,5,8",
            "depth": 3,
            "leaf": 1
          }
        ]
      }
    ]
  }
]

db<>fiddle

Collectives™ on Stack Overflow

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

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related