JSON for Tree Hierarchy Diagram from SQL Server

4zcjmb1e  于 2023-04-10  发布在  SQL Server
关注(0)|答案(1)|浏览(115)

My goal is to prepare the necessary json for a d3.js tree diagram using sql server (2019). I'm using the sql below to allow up to six node levels. It builds a json string but the output requires editing because the d3.js diagram requires all the children nodes to be named "children".

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

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

Edited to work:

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

I'm wondering if there is a way to write the query to avoid this editing.

sql-server

1条答案

按热度按时间
u59ebvdq

u59ebvdq1#

The only way to do this (without resorting to an iterative cursor-style solution) is to use a scalar User Defined Function which recurses down to get all levels of the JSON

CREATE OR ALTER FUNCTION dbo.GetJson ( @name varchar(50) )
RETURNS nvarchar(max)
WITH SCHEMABINDING
-- need to create it first if you want to schema-bind with recursion
AS BEGIN RETURN NULL; END;
CREATE OR ALTER FUNCTION dbo.GetJson ( @name varchar(50) )
RETURNS nvarchar(max)
WITH SCHEMABINDING
AS
BEGIN
  RETURN (
    SELECT
      nodeName,
      name,
      parentname,
      type,
      code,
      label,
      version,
      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"
               }
            ]
         }
      ]
   }
]
赞(0）分享  回复(0）举报  2023-04-10
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备2022004421号-2 NULL123 https://www.null123.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com