Generate Hierarchical json in sql server without recursive function

Question

I have to build a tree as is in the followed link like this http://bl.ocks.org/robschmuecker/7880033,

I need to generate hierarchical json data in sqlserver database, i have tried using recursive function but as recursive function has max limit32 in sql server i cant contunue with the function, where in i have a very huge amount of data same as above URL, the tree is complete dynamic and below is my table structure

CREATE TABLE #dndclasses
(
   id         INT  IDENTITY PRIMARY KEY,
   parent_id  INT,
   name       TEXT
);



INSERT [dbo].[dndclasses] ([id], [parent_id], [name]) VALUES (1, 0, N'Tom')
INSERT [dbo].[dndclasses] ([id], [parent_id], [name]) VALUES (2, 0, N'Josh')
INSERT [dbo].[dndclasses] ([id], [parent_id], [name]) VALUES (3, 1, N'Mike')
INSERT [dbo].[dndclasses] ([id], [parent_id], [name]) VALUES (4, 1, N'John')
INSERT [dbo].[dndclasses] ([id], [parent_id], [name]) VALUES (5, 2, N'Pam')
INSERT [dbo].[dndclasses] ([id], [parent_id], [name]) VALUES (6, 2, N'Mary')
INSERT [dbo].[dndclasses] ([id], [parent_id], [name]) VALUES (7, 3, N'James')
INSERT [dbo].[dndclasses] ([id], [parent_id], [name]) VALUES (8, 3, N'Sam')
INSERT [dbo].[dndclasses] ([id], [parent_id], [name]) VALUES (9, 4, N'Simon')
INSERT [dbo].[dndclasses] ([id], [parent_id], [name]) VALUES (10, 4, N'QQom')
INSERT [dbo].[dndclasses] ([id], [parent_id], [name]) VALUES (11, 4, N'QQosh')
INSERT [dbo].[dndclasses] ([id], [parent_id], [name]) VALUES (12, 6, N'QQike')
INSERT [dbo].[dndclasses] ([id], [parent_id], [name]) VALUES (13, 6, N'QQohn')
INSERT [dbo].[dndclasses] ([id], [parent_id], [name]) VALUES (14, 7, N'QQam')
INSERT [dbo].[dndclasses] ([id], [parent_id], [name]) VALUES (15, 7, N'QQary')
INSERT [dbo].[dndclasses] ([id], [parent_id], [name]) VALUES (16, 8, N'QQames')
INSERT [dbo].[dndclasses] ([id], [parent_id], [name]) VALUES (17, 8, N'QQam')
INSERT [dbo].[dndclasses] ([id], [parent_id], [name]) VALUES (18, 4, N'QQimon')

Any help is really appreciated and thanks in advance

Basically i almost need the hierarchical query like in

https://tapoueh.org/blog/2018/01/exporting-a-hierarchy-in-json-with-recursive-queries/

But the above is in postgre sql, i have to achieve the same in sql server

Expected Output

[{
  "id": 1,
  "parent_id": 0,
  "name": "Tom",
  "Children": [{
    "id": 3,
    "parent_id": 1,
    "name": "Mike",
    "Children": [{
      "id": 7,
      "parent_id": 3,
      "name": "James",
      "Children": [{
        "id": 14,
        "parent_id": 7,
        "name": "QQam"
      }, {
        "id": 15,
        "parent_id": 7,
        "name": "QQary"
      }]
    }, {
      "id": 8,
      "parent_id": 3,
      "name": "Sam",
      "Children": [{
        "id": 16,
        "parent_id": 8,
        "name": "QQames"
      }, {
        "id": 17,
        "parent_id": 8,
        "name": "QQam"
      }]
    }]
  }, {
    "id": 4,
    "parent_id": 1,
    "name": "John",
    "Children": [{
      "id": 9,
      "parent_id": 4,
      "name": "Simon"
    }, {
      "id": 10,
      "parent_id": 4,
      "name": "QQom"
    }, {
      "id": 11,
      "parent_id": 4,
      "name": "QQosh"
    }, {
      "id": 18,
      "parent_id": 4,
      "name": "QQimon"
    }]
  }]
}, {
  "id": 2,
  "parent_id": 0,
  "name": "Josh",
  "Children": [{
    "id": 5,
    "parent_id": 2,
    "name": "Pam"
  }, {
    "id": 6,
    "parent_id": 2,
    "name": "Mary",
    "Children": [{
      "id": 12,
      "parent_id": 6,
      "name": "QQike"
    }, {
      "id": 13,
      "parent_id": 6,
      "name": "QQohn"
    }]
  }]
}]

Check this out! https://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database — n8wrl, Jul 01 '21 at 11:40
Asking us to visit an off-site resource to tell us *how* you want your data presented doesn't help us help you. All the information should be contained in the question itself, with links used as citations or when the information *really* can't be provided in the context of the question (such as pastebins for very large log files, or Paste the Plan for query plans). Include the JSON you want as your results in your question, not somewhere else. — Thom A, Jul 01 '21 at 11:44
FWIW, just because you have "big data" doesn't preclude your using a recursive query/algorithm so long as your hierarchy doesn't go too deep. Also, the I wouldn't consider the data at the link you shared to even be particularly large - maybe 100 nodes and a hierarchy depth of 5? That is trivial for this sort of thing. — Ben Thul, Jul 01 '21 at 15:49
In my requirement hierarchy depth is not static it can be more than 100 also — Mounika Vadeghar, Jul 02 '21 at 07:41

Antonio Leonardo · Answer 1 · 2021-07-01T22:59:09.087

0

The recursive approach is the best practice to resolve your requirements; BUT, this query that I share bellow works only with three leves of hierarchy (based on nature of your exampled data). Although, if your tree have more than three nested objects, you need to resort an automatically recursive approach:

SELECT Parent.id, Parent.name,
    (SELECT c.id, c.name,
        (SELECT cc.id, cc.name
         FROM #dndclasses cc
         WHERE cc.parent_id = c.id FOR JSON PATH) as ChildOfChilds
     FROM #dndclasses c
     WHERE c.parent_id = Parent.id FOR JSON PATH) as Childs
FROM #dndclasses Parent
WHERE Parent.parent_id = 0 FOR JSON PATH

Please, if possible, give us a feed-back.

edited Jul 01 '21 at 22:59

answered Jul 01 '21 at 19:46

Antonio Leonardo

1,805
1
8
18

Thanks for your answer, but this doesn't, as hierarchy level is static but for me hierarchy level is dynamic and can be more than 100 also, so any other solution ? – Mounika Vadeghar Jul 02 '21 at 07:45
But, your question and requeriment in title of this topic ends with `without recursive function`. – Antonio Leonardo Jul 02 '21 at 17:06
And you add in your question an example of output that you want... Why do you change your requirement??? – Antonio Leonardo Jul 02 '21 at 17:11
I have asked for without recursion because i have already done using recursion and sql has limit of recursion levels as 32 which created a problem for huge data , i dint change my requirement ... i want to generate hierarchial json in sqlserver – Mounika Vadeghar Jul 03 '21 at 16:21

Generate Hierarchical json in sql server without recursive function

1 Answers1