本文展示了如何在SQL Server中生成JSON格式的数据，特别是在2016及以上版本中利用FOR JSON PATH特性进行控制。同时，给出了2016以前版本利用FOR XML PATH的替代实现方式，通过示例查询展示了如何构造复杂的JSON结构。此外，还介绍了用于转换特殊字符的自定义函数，以确保JSON的正确格式。
实际用到的表结构，在sql server2016以上版中
-
使用
FOR JSON PATH来保持对 JSON 输出格式的完全控制。 你可以创建包装对象并嵌套复杂属性。go DROP TABLE IF EXISTS #tabStudent; DROP TABLE IF EXISTS #tabClass; go CREATE TABLE #tabClass ( ClassGuid uniqueIdentifier not null default newid(), ClassName nvarchar(32) not null ); CREATE TABLE #tabStudent ( StudentGuid uniqueIdentifier not null default newid(), StudentName nvarchar(32) not null, ClassGuid uniqueIdentifier null -- Foreign key. ); go INSERT INTO #tabClass (ClassGuid, ClassName) VALUES ('DE807673-ECFC-4850-930D-A86F921DE438', 'Algebra Math'), ('C55C6819-E744-4797-AC56-FF8A729A7F5C', 'Calculus Math'), ('98509D36-A2C8-4A65-A310-E744F5621C83', 'Art Painting'); INSERT INTO #tabStudent (StudentName, ClassGuid) VALUES ('Alice Apple', 'DE807673-ECFC-4850-930D-A86F921DE438'), ('Alice Apple', 'C55C6819-E744-4797-AC56-FF8A729A7F5C'), ('Betty Boot' , 'C55C6819-E744-4797-AC56-FF8A729A7F5C'), ('Betty Boot' , '98509D36-A2C8-4A65-A310-E744F5621C83'), ('Carla Cap' , null); go SELECT c.ClassName, s.StudentName FROM #tabClass as c RIGHT OUTER JOIN #tabStudent as s ON s.ClassGuid = c.ClassGuid ORDER BY c.ClassName, s.StudentName FOR JSON AUTO -- To include NULL values in the output, uncomment the following line: --, INCLUDE_NULL_VALUES ; GO DROP TABLE IF EXISTS #tabStudent; DROP TABLE IF EXISTS #tabClass; GO输出
JSON_F52E2B61-18A1-11d1-B105-00805F49916B [ {"s":[{"StudentName":"Carla Cap"}]}, {"ClassName":"Algebra Math","s":[{"StudentName":"Alice Apple"}]}, {"ClassName":"Art Painting","s":[{"StudentName":"Betty Boot"}]}, {"ClassName":"Calculus Math","s":[{"StudentName":"Alice Apple"},{"StudentName":"Betty Boot"}]} ] -
如果2016以前的版本该如何实现呢，结合 for xml path 也是可以实现的
select v.TeaNo,u.UserId,u.UserName ,data=('['+STUFF((SELECT ',{"VNo": "' + CONVERT(VARCHAR, v2.VNo) + '","VName": "' + replace(isnull(v2.VName,''),'"','\"')+ '","IP": "' + replace(isnull(v2.IP,''),'"','\"')+ '","Sys": "' + replace(isnull(s.LabName,''),'"','\"') + '"}' FROM KT_TM_VideoUser u2 inner join KT_TM_Video v2 on u2.VNo = v2.VNo left join KT_B_Laboratory s on v2.LabNo=s.LabNo where TeaNo = v.TeaNo FOR XML PATH('')), 1, 1, '')+']') from KT_TM_VideoUser v left join KT_U_User u on v.TeaNo=u.UserNo left join KT_U_Teacher t on u.UserNo=t.UserNo inner join KT_B_Department d on t.DeptNo=d.DeptNo结果：
3999 00000028 胡晓玲 [{"VNo": "1","VName": "大华1","IP": "192.168.0.6","Sys": "语言实验室8"},{"VNo": "2","VName": "海康2","IP": "10.244.27.5","Sys": "语言实验室"},{"VNo": "3","VName": "物理学院","IP": "10.244.103.171","Sys": "经济001"},{"VNo": "4","VName": "建筑工程","IP": "10.244.86.249","Sys": "经济001"},{"VNo": "6","VName": "11","IP": "192.168.1.123","Sys": "空气检测室验室1"},{"VNo": "7","VName": "work1","IP": "192.168.1.22","Sys": "测试实验室3"},{"VNo": "8","VName": "111A","IP": "192.168.11.23","Sys": "测试实验室2"},{"VNo": "9","VName": "111B","IP": "192.168.11.25","Sys": "名称111"},{"VNo": "10","VName": "21232","IP": "129.22.112.22","Sys": "智慧教室"}] 4408 00000040 杜锡彬 [{"VNo": "2","VName": "海康2","IP": "10.244.27.5","Sys": "语言实验室"},{"VNo": "3","VName": "物理学院","IP": "10.244.103.171","Sys": "经济001"},{"VNo": "4","VName": "建筑工程","IP": "10.244.86.249","Sys": "经济001"},{"VNo": "6","VName": "11","IP": "192.168.1.123","Sys": "空气检测室验室1"}] 4598 00000037 张静 [{"VNo": "2","VName": "海康2","IP": "10.244.27.5","Sys": "语言实验室"},{"VNo": "3","VName": "物理学院","IP": "10.244.103.171","Sys": "经济001"},{"VNo": "6","VName": "11","IP": "192.168.1.123","Sys": "空气检测室验室1"}] 4833 00000073 王宏毅 [{"VNo": "1","VName": "大华1","IP": "192.168.0.6","Sys": "语言实验室8"},{"VNo": "2","VName": "海康2","IP": "10.244.27.5","Sys": "语言实验室"},{"VNo": "3","VName": "物理学院","IP": "10.244.103.171","Sys": "经济001"},{"VNo": "4","VName": "建筑工程","IP": "10.244.86.249","Sys": "经济001"},{"VNo": "6","VName": "11","IP": "192.168.1.123","Sys": "空气检测室验室1"}] 4724 00000045 高琳 [{"VNo": "2","VName": "海康2","IP": "10.244.27.5","Sys": "语言实验室"},{"VNo": "3","VName": "物理学院","IP": "10.244.103.171","Sys": "经济001"},{"VNo": "4","VName": "建筑工程","IP": "10.244.86.249","Sys": "经济001"},{"VNo": "6","VName": "11","IP": "192.168.1.123","Sys": "空气检测室验室1"}] 4548 00000110 张红梅 [{"VNo": "1","VName": "大华1","IP": "192.168.0.6","Sys": "语言实验室8"},{"VNo": "2","VName": "海康2","IP": "10.244.27.5","Sys": "语言实验室"},{"VNo": "3","VName": "物理学院","IP": "10.244.103.171","Sys": "经济001"},{"VNo": "4","VName": "建筑工程","IP": "10.244.86.249","Sys": "经济001"},{"VNo": "6","VName": "11","IP": "192.168.1.123","Sys": "空气检测室验室1"}] 4752 00000091 李霞 [{"VNo": "2","VName": "海康2","IP": "10.244.27.5","Sys": "语言实验室"},{"VNo": "4","VName": "建筑工程","IP": "10.244.86.249","Sys": "经济001"},{"VNo": "6","VName": "11","IP": "192.168.1.123","Sys": "空气检测室验室1"}] 4598 00000037 张静 [{"VNo": "2","VName": "海康2","IP": "10.244.27.5","Sys": "语言实验室"},{"VNo": "3","VName": "物理学院","IP": "10.244.103.171","Sys": "经济001"},{"VNo": "6","VName": "11","IP": "192.168.1.123","Sys": "空气检测室验室1"}] 4710 00000020 陈戈 [{"VNo": "3","VName": "物理学院","IP": "10.244.103.171","Sys": "经济001"},{"VNo": "4","VName": "建筑工程","IP": "10.244.86.249","Sys": "经济001"}] 4405 00000022 雷静和 [{"VNo": "1","VName": "大华1","IP": "192.168.0.6","Sys": "语言实验室8"},{"VNo": "2","VName": "海康2","IP": "10.244.27.5","Sys": "语言实验室"},{"VNo": "3","VName": "物理学院","IP": "10.244.103.171","Sys": "经济001"},{"VNo": "4","VName": "建筑工程","IP": "10.244.86.249","Sys": "经济001"},{"VNo": "6","VName": "11","IP": "192.168.1.123","Sys": "空气检测室验室1"},{"VNo": "7","VName": "work1","IP": "192.168.1.22","Sys": "测试实验室3"},{"VNo": "8","VName": "111A","IP": "192.168.11.23","Sys": "测试实验室2"},{"VNo": "9","VName": "111B","IP": "192.168.11.25","Sys": "名称111"},{"VNo": "10","VName": "21232","IP": "129.22.112.22","Sys": "智慧教室"}] 4131 00000119 何强 [{"VNo": "3","VName": "物理学院","IP": "10.244.103.171","Sys": "经济001"}] 4365 00000111 陈元玉 [{"VNo": "2","VName": "海康2","IP": "10.244.27.5","Sys": "语言实验室"},{"VNo": "3","VName": "物理学院","IP": "10.244.103.171","Sys": "经济001"},{"VNo": "6","VName": "11","IP": "192.168.1.123","Sys": "空气检测室验室1"},{"VNo": "7","VName": "work1","IP": "192.168.1.22","Sys": "测试实验室3"}] 4405 00000022 雷静和 [{"VNo": "1","VName": "大华1","IP": "192.168.0.6","Sys": "语言实验室8"},{"VNo": "2","VName": "海康2","IP": "10.244.27.5","Sys": "语言实验室"},{"VNo": "3","VName": "物理学院","IP": "10.244.103.171","Sys": "经济001"},{"VNo": "4","VName": "建筑工程","IP": "10.244.86.249","Sys": "经济001"},{"VNo": "6","VName": "11","IP": "192.168.1.123","Sys": "空气检测室验室1"},{"VNo": "7","VName": "work1","IP": "192.168.1.22","Sys": "测试实验室3"},{"VNo": "8","VName": "111A","IP": "192.168.11.23","Sys": "测试实验室2"},{"VNo": "9","VName": "111B","IP": "192.168.11.25","Sys": "名称111"},{"VNo": "10","VName": "21232","IP": "129.22.112.22","Sys": "智慧教室"}] 4408 00000040 杜锡彬 [{"VNo": "2","VName": "海康2","IP": "10.244.27.5","Sys": "语言实验室"},{"VNo": "3","VName": "物理学院","IP": "10.244.103.171","Sys": "经济001"},{"VNo": "4","VName": "建筑工程","IP": "10.244.86.249","Sys": "经济001"},{"VNo": "6","VName": "11","IP": "192.168.1.123","Sys": "空气检测室验室1"}] 4472 00000043 汪平 [{"VNo": "3","VName": "物理学院","IP": "10.244.103.171","Sys": "经济001"}] -
可简化操作，把它做成函数（直接上实际应用中用到的代码）
现在要实现每个用户所对应的角色权限，在Web中使用Json可以很方便的显示出来,首先实现Json值的几个特殊字符串转换函数[dbo].[KF_Pub_ToJsonVal]
--格式化Json的值
ALTER FUNCTION [dbo].[KF_Pub_ToJsonVal](@val nvarchar(1000))
RETURNS varchar(1000)
--可加入加密码代码:WITH ENCRYPTION
AS
BEGIN
return replace(replace(@val,'"','\"'),'\','\\')
END
接着实现一个用户对应的权限函数[dbo].[KF_Sys_GetUserManagerDeptRightJson]
----获取指定用户的角色单位名称信息格式化Json信息
CREATE FUNCTION [dbo].[KF_Sys_GetUserManagerDeptRightJson](@UserNo int)
RETURNS nvarchar(max)
--可加入加密码代码:WITH ENCRYPTION
AS
BEGIN
return stuff((
select
',{"js": "' + [dbo].[KF_Pub_ToJsonVal](RoleName)
--+ '","bm": "' + [dbo].[KF_Pub_ToJsonVal](Bm)
--+ '","sys": "' + [dbo].[KF_Pub_ToJsonVal](SysMc)
--+ '","fs": "' + [dbo].[KF_Pub_ToJsonVal](FsMc)
+ '","father": "' + case when DeptNo<=0 then '<所有部门>'
when FsMc is not null then [dbo].[KF_Pub_ToJsonVal](Bm)+case when SysMc is not null then '\\'+[dbo].[KF_Pub_ToJsonVal](SysMc) else '' end
when SysMc is not null then [dbo].[KF_Pub_ToJsonVal](Bm)
when parentBm is not null then parentBm
else '' end
+ '","bm": "' + case when FsMc is not null then '★'+[dbo].[KF_Pub_ToJsonVal](FsMc)
when SysMc is not null then '☆'+[dbo].[KF_Pub_ToJsonVal](SysMc)
when Bm is not null then '■'+[dbo].[KF_Pub_ToJsonVal](Bm)
else [dbo].[KF_Pub_ToJsonVal](Bm) end
+ '"}'
from (select urr.DeptNo,
r.RoleName,
parentBm=p.DeptName,
Bm=case when urr.DeptNo<=0 then '<所有部门>'
else
case when d.DeptName is not null then
d.DeptName
else
case when sd1.DeptName is not null then sd1.DeptName else
case when sd2.DeptName is not null then sd2.DeptName else '<未知>' end
end
end
end,
SysMc=case when s1.LabClassify=0 then s1.LabName
when s2.LabClassify=0 then s2.LabName
else null
end,
FsMc=case when s1.LabClassify=1 then s1.LabName
else f2.LabName end
from KT_Sys_UserRoleRange urr inner join KT_Sys_Role r on urr.RoleNo=r.RoleNo
left join KT_B_Department d on urr.DeptNo=d.DeptNo and RangeType=1 --部门权限
left join KT_B_Department p on p.DeptNo=d.ParentDeptNo
left join KT_B_Laboratory s1 on urr.DeptNo=s1.LabNo and s1.DeptNo>0 and RangeType in(2,3) --实验室或分室权限-- and RangeType=2
left join KT_B_Department sd1 on sd1.DeptNo=s1.DeptNo
left join KT_B_Laboratory f2 on urr.DeptNo=f2.LabNo and f2.DeptNo<=0 and RangeType in(2,3) --实验分室权限-- and RangeType=3 and f2.LabClassify=1
left join KT_B_Laboratory s2 on s2.LabNo=f2.ParentLabNo
left join KT_B_Department sd2 on sd2.DeptNo=s2.DeptNo
where urr.UserNo=@UserNo
) db
order by RoleName
FOR XML PATH('')
),1,1,'')
END执行函数：
select [dbo].[KF_Sys_GetUserManagerDeptRightJson](3999)结果显示
{"js": "11c","father": "","bm": "■马克思主义学院"},{"js": "11c","father": "表演学院","bm": "★经济001"},{"js": "11c","father": "化学与生命科学学院","bm": "☆测试实验室1"},{"js": "22","father": "新闻与传播学院","bm": "■地理与旅游学院"},{"js": "asd","father": "","bm": "■马克思主义学院"},{"js": "asd","father": "表演学院","bm": "★经济001"},{"js": "asd","father": "化学与生命科学学院","bm": "☆测试实验室1"}
扫一扫
2726
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
