数组 – SQL Server 2016 for JSON输出整数数组

发布时间：2020-12-12 16:22:00 所属栏目：MsSql教程来源：网络整理

导读：我想使用SQL Server 2016的For JSON功能获取带有整数数组的JSON.我对整数数组感到困惑. 数据库表结构： declare @Employees table (ID int,Name nvarchar(50))insert into @Employees values(1,'Bob'),(2,'Randy')declare @Permissions table (ID int,Permiss

我想使用SQL Server 2016的For JSON功能获取带有整数数组的JSON.我对整数数组感到困惑.

数据库表结构：

declare @Employees table (ID int,Name nvarchar(50))
insert into @Employees values
(1,'Bob'),(2,'Randy')

declare @Permissions table (ID int,PermissionName nvarchar(50))
insert into @Permissions values
(1,'Post'),'Comment'),(3,'Edit'),(4,'Delete')

declare @EmployeePermissions table (EmployeeID int,PermissionID int)
insert into @EmployeePermissions values
(1,1),(1,2),3)

期望的结果：

{"EmployeePermissions": [
  {"Employee":"Bob","Permissions":[1,2]},{"Employee":"Randy",2,3]}
}

这是我得到的最接近的,但不是我想要的.

select
    e.Name as Employee,(select 
         convert(nvarchar(10),ep.PermissionID) as PermID 
     from @EmployeePermissions ep 
     where ep.EmployeeID=e.ID 
     for json path) as 'Permissions'
from
    @Employees e
for json path,root('EmployeePermissions')

收益：

{"EmployeePermissions": [
  {"Employee":"Bob","Permissions":[{"permID":1},{"permID":2}]},{"permID":2},{"permID":3}]}
}

解决方法

在AdventureWorks 2016 CTP3 JSON示例中,您可以找到一个可以清除键：值对的数组并创建数组od值的函数：

DROP FUNCTION IF EXISTS dbo.ufnToRawJsonArray
GO
CREATE FUNCTION
[dbo].[ufnToRawJsonArray](@json nvarchar(max),@key nvarchar(400)) returns nvarchar(max)
AS BEGIN
       declare @new nvarchar(max) = replace(@json,CONCAT('},{"',@key,'":'),',')
       return '[' + substring(@new,1 + (LEN(@key)+5),LEN(@new) -2 - (LEN(@key)+5)) + ']'
END

只需将SELECT FOR JSON表达式的结果作为@json参数和要作为第二个参数删除的键的名称.可能类似于：

select
e.Name as Employee,JSON_QUERY(dbo.ufnToRawJsonArray(
    (select 
     convert(nvarchar(10),ep.PermissionID) as PermID 
     from @EmployeePermissions ep 
     where ep.EmployeeID=e.ID 
     for json path),'PermID'))
   as 'Permissions'
from
@Employees e
for json path,root('EmployeePermissions')

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!