首页 / 技术问答 / Sql问答列表 / 问答详情

来自 SQL Server 的树层次 struct 图的 JSON

我的目标是为使用SQL服务器的D3.js树形图准备必要的json(2019).我使用下面的SQL来允许多达六个 node 级别.它构建了一个json字符串，但输出需要编辑，因为D3.js图要求将所有子 node 命名为"Child".

declare @t table (nodeName varchar(50), name varchar(50), parentname varchar(50), type varchar(50), code varchar(50), label varchar(50), version varchar(50));
insert into @t(nodeName,name,parentname,type,code,label,version)
values

('NODE NAME 1','NODE NAME 1','','type3','N1','Node name 1','v1.0'),
('NODE NAME 2.1','NODE NAME 2.1','NODE NAME 1','type1','N2.1','Node name 2.1','v1.0'),
('NODE NAME 2.2','NODE NAME 2.2','NODE NAME 1','type1','N2.2','Node name 2.2','v1.0'),
('NODE NAME 2.3','NODE NAME 2.3','NODE NAME 1','type1','N2.3','Node name 2.3','v1.0'),
('NODE NAME 2.4','NODE NAME 2.4','NODE NAME 1','type1','N2.4','Node name 2.4','v1.0'),
('NODE NAME 2.5','NODE NAME 2.5','NODE NAME 1','type1','N2.5','Node name 2.5','v1.0'),
('NODE NAME 3.1','NODE NAME 3.1','NODE NAME 2.1','type2','N3.1','Node name 3.1','v1.0'),
('NODE NAME 3.2','NODE NAME 3.2','NODE NAME 2.1','type2','N3.2','Node name 3.2','v1.0'),
('NODE NAME 3.3','NODE NAME 3.3','NODE NAME 2.3','type1','N3.3','Node name 3.3','v1.0'),
('NODE NAME 3.4','NODE NAME 3.4','NODE NAME 2.3','type1','N3.4','Node name 3.4','v1.0'),
('NODE NAME 3.5','NODE NAME 3.5','NODE NAME 2.4','type2','N3.5','Node name 3.5','v1.0'),
('NODE NAME 3.6','NODE NAME 3.6','NODE NAME 2.5','type2','N3.6','Node name 3.6','v1.0'),
('NODE NAME 4.1','NODE NAME 4.1','NODE NAME 3.3','type4','N4.1','Node name 4.1','v1.0'),
('NODE NAME 4.2','NODE NAME 4.2','NODE NAME 3.4','type4','N4.2','Node name 4.2','v1.0'),
('NODE NAME 5.1','NODE NAME 5.1','NODE NAME 4.1','type4','N5.1','Node name 5.1','v1.0'),
('NODE NAME 5.2','NODE NAME 5.2','NODE NAME 4.1','type4','N5.2','Node name 5.2','v1.0'),
('NODE NAME 6.1','NODE NAME 6.1','NODE NAME 5.2','type4','N6.1','Node name 6.1','v1.0'),
('NODE NAME 6.2','NODE NAME 6.2','NODE NAME 5.2','type4','N6.2','Node name 6.2','v1.0');

with l1 as (select * from @t where nodename = 'NODE NAME 1')
,l2 as (select * from @t where parentname in (select nodename from l1))
,l3 as (select * from @t where parentname in (select nodename from l2))
,l4 as (select * from @t where parentname in (select nodename from l3))
,l5 as (select * from @t where parentname in (select nodename from l4))
,l6 as (select * from @t where parentname in (select nodename from l5))

select l1.*, l2.*, l3.*, l4.*, l5.*, l6.*
from l1 
left join l2 on l2.parentname = l1.nodename
left join l3 on l3.parentname = l2.nodename
left join l4 on l4.parentname = l3.nodename
left join l5 on l5.parentname = l4.nodename
left join l6 on l6.parentname = l5.nodename

for json auto

产生的JSON:

"nodeName": "NODE NAME 1",
"name": "NODE NAME 1",
"parentname": "",
"type": "type3",
"code": "N1",
"label": "Node name 1",
"version": "v1.0",
"l2": [
    {
        "nodeName": "NODE NAME 2.1",
        "name": "NODE NAME 2.1",
        "parentname": "NODE NAME 1",
        "type": "type1",
        "code": "N2.1",
        "label": "Node name 2.1",
        "version": "v1.0",
        "l3": [
            {
                "nodeName": "NODE NAME 3.1",
                "name": "NODE NAME 3.1",
                "parentname": "NODE NAME 2.1",
                "type": "type2",
                "code": "N3.1",
                "label": "Node name 3.1",
                "version": "v1.0",
                "l4": [
                    {
                        "l5": [
                            {
                                "l6": [
                                    {}
                                ]
                            }
                        ]
                    }
                ]
            },

编辑后的作品:

"nodeName": "NODE NAME 1",
"name": "NODE NAME 1",
"parentname": "",
"type": "type3",
"code": "N1",
"label": "Node name 1",
"version": "v1.0",
"children": [
    {
        "nodeName": "NODE NAME 2.1",
        "name": "NODE NAME 2.1",
        "parentname": "NODE NAME 1",
        "type": "type1",
        "code": "N2.1",
        "label": "Node name 2.1",
        "version": "v1.0",
        "children": [
            {
                "nodeName": "NODE NAME 3.1",
                "name": "NODE NAME 3.1",
                "parentname": "NODE NAME 2.1",
                "type": "type2",
                "code": "N3.1",
                "label": "Node name 3.1",
                "version": "v1.0",
                "children": []
            },

我想知道是否有一种方法可以编写查询来避免这种编辑.

推荐答案

实现这一点的唯一方法(而不是求助于迭代游标式解决方案)是使用一个标量用户定义函数，该函数向下递归以获取JSON的所有级别

CREATE OR ALTER FUNCTION dbo.GetJson ( @name varchar(50) )
RETURNS nvarchar(max)
AS
BEGIN
  RETURN (
    SELECT
      nodeName,
      name,
      parentname,
      type,
      code,
      label,
      version,
      JSON_QUERY(dbo.GetJson(name)) AS children
    FROM dbo.t
    WHERE t.parentname = @name
    FOR JSON PATH
  );
END;

db<>fiddle个

Result个

[
   {
      "nodeName":"NODE NAME 1",
      "name":"NODE NAME 1",
      "parentname":"",
      "type":"type3",
      "code":"N1",
      "label":"Node name 1",
      "version":"v1.0",
      "children":[
         {
            "nodeName":"NODE NAME 2.1",
            "name":"NODE NAME 2.1",
            "parentname":"NODE NAME 1",
            "type":"type1",
            "code":"N2.1",
            "label":"Node name 2.1",
            "version":"v1.0",
            "children":[
               {
                  "nodeName":"NODE NAME 3.1",
                  "name":"NODE NAME 3.1",
                  "parentname":"NODE NAME 2.1",
                  "type":"type2",
                  "code":"N3.1",
                  "label":"Node name 3.1",
                  "version":"v1.0"
               },
               {
                  "nodeName":"NODE NAME 3.2",
                  "name":"NODE NAME 3.2",
                  "parentname":"NODE NAME 2.1",
                  "type":"type2",
                  "code":"N3.2",
                  "label":"Node name 3.2",
                  "version":"v1.0"
               }
            ]
         },
         {
            "nodeName":"NODE NAME 2.2",
            "name":"NODE NAME 2.2",
            "parentname":"NODE NAME 1",
            "type":"type1",
            "code":"N2.2",
            "label":"Node name 2.2",
            "version":"v1.0"
         },
         {
            "nodeName":"NODE NAME 2.3",
            "name":"NODE NAME 2.3",
            "parentname":"NODE NAME 1",
            "type":"type1",
            "code":"N2.3",
            "label":"Node name 2.3",
            "version":"v1.0",
            "children":[
               {
                  "nodeName":"NODE NAME 3.3",
                  "name":"NODE NAME 3.3",
                  "parentname":"NODE NAME 2.3",
                  "type":"type1",
                  "code":"N3.3",
                  "label":"Node name 3.3",
                  "version":"v1.0",
                  "children":[
                     {
                        "nodeName":"NODE NAME 4.1",
                        "name":"NODE NAME 4.1",
                        "parentname":"NODE NAME 3.3",
                        "type":"type4",
                        "code":"N4.1",
                        "label":"Node name 4.1",
                        "version":"v1.0",
                        "children":[
                           {
                              "nodeName":"NODE NAME 5.1",
                              "name":"NODE NAME 5.1",
                              "parentname":"NODE NAME 4.1",
                              "type":"type4",
                              "code":"N5.1",
                              "label":"Node name 5.1",
                              "version":"v1.0"
                           },
                           {
                              "nodeName":"NODE NAME 5.2",
                              "name":"NODE NAME 5.2",
                              "parentname":"NODE NAME 4.1",
                              "type":"type4",
                              "code":"N5.2",
                              "label":"Node name 5.2",
                              "version":"v1.0",
                              "children":[
                                 {
                                    "nodeName":"NODE NAME 6.1",
                                    "name":"NODE NAME 6.1",
                                    "parentname":"NODE NAME 5.2",
                                    "type":"type4",
                                    "code":"N6.1",
                                    "label":"Node name 6.1",
                                    "version":"v1.0"
                                 },
                                 {
                                    "nodeName":"NODE NAME 6.2",
                                    "name":"NODE NAME 6.2",
                                    "parentname":"NODE NAME 5.2",
                                    "type":"type4",
                                    "code":"N6.2",
                                    "label":"Node name 6.2",
                                    "version":"v1.0"
                                 }
                              ]
                           }
                        ]
                     }
                  ]
               },
               {
                  "nodeName":"NODE NAME 3.4",
                  "name":"NODE NAME 3.4",
                  "parentname":"NODE NAME 2.3",
                  "type":"type1",
                  "code":"N3.4",
                  "label":"Node name 3.4",
                  "version":"v1.0",
                  "children":[
                     {
                        "nodeName":"NODE NAME 4.2",
                        "name":"NODE NAME 4.2",
                        "parentname":"NODE NAME 3.4",
                        "type":"type4",
                        "code":"N4.2",
                        "label":"Node name 4.2",
                        "version":"v1.0"
                     }
                  ]
               }
            ]
         },
         {
            "nodeName":"NODE NAME 2.4",
            "name":"NODE NAME 2.4",
            "parentname":"NODE NAME 1",
            "type":"type1",
            "code":"N2.4",
            "label":"Node name 2.4",
            "version":"v1.0",
            "children":[
               {
                  "nodeName":"NODE NAME 3.5",
                  "name":"NODE NAME 3.5",
                  "parentname":"NODE NAME 2.4",
                  "type":"type2",
                  "code":"N3.5",
                  "label":"Node name 3.5",
                  "version":"v1.0"
               }
            ]
         },
         {
            "nodeName":"NODE NAME 2.5",
            "name":"NODE NAME 2.5",
            "parentname":"NODE NAME 1",
            "type":"type1",
            "code":"N2.5",
            "label":"Node name 2.5",
            "version":"v1.0",
            "children":[
               {
                  "nodeName":"NODE NAME 3.6",
                  "name":"NODE NAME 3.6",
                  "parentname":"NODE NAME 2.5",
                  "type":"type2",
                  "code":"N3.6",
                  "label":"Node name 3.6",
                  "version":"v1.0"
               }
            ]
         }
      ]
   }
]

Sql相关问答推荐

使用交叉应用透视表在SQL中转换分段时间段&

SQL更新，在2个额外的表上使用内部连接

使用WHERE子句进行筛选时，SQL SELECT查询返回总计数

如果元素包含通过SQL指定的字符串，则过滤掉数组元素

将所有XML文件导入到SQL Server中

根据最大值为字母数字大小写分配数值

动态组/转置

SQL查询正在工作，但返回空结果

不存在记录的国外关键点

按二维数组的第一个元素排序

日期逻辑(查找过go 90 天内的第一个匹配行)

在 postgres 中插入或更新 jsonb 数组的对象

确定小数中使用的精度位数

BigQuery导航函数计算ID

SQL 函数 DIFFERENCE 返回有趣的分数

如何找到特定时间间隔内的最大和最小日期？

交叉应用 OPENJSON / PIVOT - 错误的顺序

连续日期的SQL

REGEXP 用于字符串格式化以对用空格分隔的字符和数字进行分组

在 AWS athena 的视图之上创建视图时，如何消除此错误：列别名列表有 1 个条目但t有 4 列可用？

实用课程推荐
相关教程推荐
休闲君评测 |  TOOLFK工具网 |  古诗文网 |  良许Linux教程网 |  Python之禅 |  红色石头 |  入党申请书 | 

© 2020 Hi LearnFk 意见反馈 免责声明 关于我们 桂ICP备11002319号-3 友链&广告位+QQ: 1963612630