在没有递归函数的情况下在sql server中生成分层json [英] Generate Hierarchical json in sql server without recursive function

问题描述

我必须像下面这样的链接一样构建一棵树 http://bl.ocks.org/robschmuecker/7880033,

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

我需要在 sqlserver 数据库中生成分层 json 数据，我曾尝试使用递归函数，但由于递归函数在 sql server 中具有 max limit32，我无法继续使用该函数，其中我有大量与上述相同的数据URL，树是完全动态的，下面是我的表结构

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/

但以上是在 postgre sql 中，我必须在 sql server 中实现相同的

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

预期产出

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

推荐答案

递归方法是解决您的需求的最佳实践；但是，我在下面分享的这个查询仅适用于三级层次结构(基于示例数据的性质).虽然，如果您的树有超过三个嵌套对象，您需要采用自动递归的方法:

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.

这篇关于在没有递归函数的情况下在sql server中生成分层json的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！