JSON的树层次图从SQL Server

Question

我的目标是使用sql server(2019)为d3.js树形图准备必要的json。我使用下面的sql来允许最多六个节点级别。它构建了一个json字符串，但输出需要编辑，因为d3.js图表要求所有的子节点命名为&;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

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

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

Answer 1

要做到这一点(不诉诸迭代游标样式的解决方案)的唯一方法是使用一个标量用户定义函数，该函数递归向下获取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&lt的在小提琴

结果

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