SQL Server 2016 - Build logical Hierarchy into JSON structure from single table

Question

I have a table that contains information pertaining to a very large document. The table looks something as follows:

ID      | Title         | Parent_ID     | <other columns>
--------+---------------+---------------+-------------------
0       | Root          | null          | ...
1       | Introduction  | 0             | ...
2       | Glossary      | 1             | ...
3       | Audience      | 1             | ...
4       | "A"           | 2             | ...
5       | "B"           | 2             | ...
6       | "C"           | 2             | ...

The result JSON should look like (the <other columns> part is omitted for clarity purposes):

{"ID"        : 0        ,
 "Title"     : "Root"   ,
 "Contents"  : [{"ID"        : 1             ,
                 "Title"     : "Introduction",
                 "Contents"  : [{"ID"          : 2           ,
                                 "Title"       : "Glossary"  ,
                                 "Contents"    : [{"ID"       : 4       ,
                                                   "Title"    : "A"     ,
                                                   "Contents" : []       },
                                                  {"ID"       : 5       ,
                                                   "Title"    : "B"     ,
                                                   "Contents" : []       },
                                                  {"ID"       : 6       ,
                                                   "Title"    : "C"     ,
                                                   "Contents" : []       }]
                                },
                                {"ID"       : 3          ,
                                 "Title"    : "Audience" ,
                                 "Contents" : []
                                }
                               ]
                },
                ....
               ]
}

I do have a simple (recursive) procedure that would handle this, but was hoping there is a simpler way using the JSON capabilities of the DBMS (perhaps using CTE?).

LukStorms · Accepted Answer · 2018-12-18 15:04:42Z

1

If the maximum depth of the parent/child relationships is known?
Then you could pull that off like in this example :

A test on db<>fiddle here

Test Data:

CREATE TABLE documentdetails 
(
   ID INT PRIMARY KEY NOT NULL,
   Title VARCHAR(30) NOT NULL,
   Parent_ID INT,
   FOREIGN KEY (Parent_ID) REFERENCES documentdetails (ID) 
);

INSERT INTO documentdetails (ID, Title, Parent_ID) VALUES 
(1, 'Root', null), (2, 'Introduction', 1), (3, 'Glossary', 1),
(4, 'Audience', 1), (5, 'A', 2), (6,'B', 2), (7, 'C', 2), 
(8, 'Foo', null), (9, 'Bar Intro', 8), (10, 'Glossy stuff', 8), (11, 'What The Fook', 8),
(12, 'Yo', 9), (13, 'Ai', 10), (14, 'Potato', 11);

Query:

SELECT 
root.ID, 
root.Title,
(  
   SELECT lvl0.ID, lvl0.Title, 0 as Depth,
   (  
      SELECT lvl1.ID, lvl1.Title, 1 as Depth,
      ( 
         SELECT lvl2.ID, lvl2.Title, 2 as Depth,
         ( 
            SELECT lvl3.ID, lvl3.Title, 3 as Depth
            FROM documentdetails lvl3
            WHERE lvl3.Parent_ID = lvl2.ID
            FOR JSON PATH
         ) AS Contents
         FROM documentdetails lvl2
         WHERE lvl2.Parent_ID = lvl1.ID
         FOR JSON PATH
      ) AS Contents
      FROM documentdetails lvl1
      WHERE lvl1.Parent_ID = lvl0.ID
      FOR JSON PATH
   ) AS Contents
   FROM documentdetails lvl0
   WHERE lvl0.ID = root.ID
   FOR JSON PATH
) AS Contents
FROM documentdetails root
WHERE root.Parent_ID IS NULL;

Result:

ID  Title   Contents
--  -----   --------
1   Root    [{"ID":1,"Title":"Root","Depth":0,"Contents":[{"ID":2,"Title":"Introduction","Depth":1,"Contents":[{"ID":5,"Title":"A","Depth":2},{"ID":6,"Title":"B","Depth":2},{"ID":7,"Title":"C","Depth":2}]},{"ID":3,"Title":"Glossary","Depth":1},{"ID":4,"Title":"Audience","Depth":1}]}]
8   Foo     [{"ID":8,"Title":"Foo","Depth":0,"Contents":[{"ID":9,"Title":"Bar Intro","Depth":1,"Contents":[{"ID":12,"Title":"Yo","Depth":2}]},{"ID":10,"Title":"Glossy stuff","Depth":1,"Contents":[{"ID":13,"Title":"Ai","Depth":2}]},{"ID":11,"Title":"What The Fook","Depth":1,"Contents":[{"ID":14,"Title":"Potato","Depth":2}]}]}]

If you don't know the maximum depth in the table?
Here's SQL that uses a Recursive CTE to get an idea about that.

WITH RCTE AS
(
   SELECT ID as rootID, 0 as lvl, ID, Parent_ID
   FROM documentdetails
   WHERE Parent_ID IS NULL

   UNION ALL

   SELECT r.rootID, lvl + 1, t.ID, t.Parent_ID
   FROM RCTE r
   JOIN documentdetails t ON t.Parent_ID = r.ID
)
SELECT rootID, MAX(lvl) as Depth, COUNT(*) as Nodes
FROM RCTE
GROUP BY rootID
ORDER BY MAX(lvl) DESC, COUNT(*) DESC;

Or the reverse of that, seeding from the children.
(If ID is the PRIMARY KEY then this could be faster because of the JOIN on ID)

WITH RCTE AS
(
   SELECT ID as baseID, 0 as lvl, ID, Parent_ID
   FROM documentdetails
   WHERE Parent_ID IS NOT NULL

   UNION ALL

   SELECT r.baseID, lvl + 1, t.ID, t.Parent_ID
   FROM RCTE r
   JOIN documentdetails t ON t.ID = r.Parent_ID
)
SELECT ID as rootID, MAX(lvl) as Depth
FROM RCTE 
WHERE Parent_ID IS NULL
GROUP BY ID
ORDER BY MAX(lvl) DESC, COUNT(*) DESC;

edited Dec 18, 2018 at 15:04

answered Dec 16, 2018 at 14:36

LukStorms

29.8k5 gold badges36 silver badges49 bronze badges

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

3 Comments

FDavidov Over a year ago

Thank you LukStorms for your suggestion. Unfortunately, the base assumption is wrong, meaning, I cannot assert that there is a maximal to the level of nesting, and hence the solution cannot be based on a hard-coded approach.

LukStorms Over a year ago

@FDavidov Then I'm sorry. Not sure how to do that via pure SQL. But I've added another query that can be used to assert what the maximum level of nesting in the table is.

FDavidov Over a year ago

Thank you LukStorms for your effort. I thought there was an elegant and simple way of doing this keeping the required flexibility and without using dynamic SQL (which would be the case after I use your additional query to establish the depth of the nesting). If I come across such a solution (elegant), will post it as the answer along with a comment here so that you get an alert and see what I came out with. Cheers!!!

Collectives™ on Stack Overflow

SQL Server 2016 - Build logical Hierarchy into JSON structure from single table

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related