用于JSON输出整型数组的SQL Server 2016

问题描述：

我想使用SQL Server 2016的For JSON功能获取带整数数组的JSON。我被困在整数数组中。用于JSON输出整型数组的SQL Server 2016

数据库表结构：

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'), 
(2, 'Comment'), 
(3, 'Edit'), 
(4, 'Delete') 

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

期望的结果：

{"EmployeePermissions": [ 
    {"Employee":"Bob", "Permissions":[1,2]}, 
    {"Employee":"Randy", "Permissions":[1,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}]}, 
    {"Employee":"Randy", "Permissions":[{"permID":1},{"permID":2},{"permID":3}]} 
}

能否请你分享工作代码，它将有助于使用permID给予更多的答案 – Arulkumar

创造一个视图/结果集，然后得到的结果进行http://stackoverflow.com/questions/10461874/sql-server-concatenate-group-by –

@Arulkumar - 添加了我最近的代码。 –

答

您可以使用FOR XML PATH和STUFF使PermissionID每个Employee分隔的一个字符串逗号，使用它QUOTENANE，然后把所有的变量，并与[和]"更换"[与]：

DECLARE @json NVARCHAR(max) 

SELECT @json = REPLACE(REPLACE((
    SELECT e.Name as [Employee], 
      QUOTENAME(STUFF((SELECT ','+CAST(ep.PermissionID as nvarchar(10)) 
      FROM EmployeePermissions ep 
      WHERE e.ID = ep.EmployeeID 
      FOR XML PATH('')),1,1,'')) 
      as [Permissions] 
    FROM Employees e 
    FOR JSON AUTO, ROOT('EmployeePermissions') 
),'"[','['),']"',']') 

SELECT @json

输出：

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

编辑：

另一种方式：

SELECT '{"EmployeePermissions":[' + STUFF((
SELECT ',{"Employee":"' + e.Name + '","Permissions":[' + 
     STUFF((SELECT ',' + CAST(PermissionID as nvarchar(10)) 
     FROM EmployeePermissions ep 
     WHERE ep.EmployeeID = e.ID 
     FOR XML PATH('')),1,1,'') +']}' 
FROM Employees e 
FOR XML PATH('')),1,1,'') + ']}'

输出：

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

关闭，虽然我想要一个数组的数组，如：“权限”： [1,2,3] –

这个怎么样（检查编辑我的答案） – gofr1

这是正确的输出，但我不想严格字符串连接技术。数组部分可以使用字符串技巧，但外部查询（在这种情况下简化）必须使用“For JSON”功能。 –

答

AdventureWorks中的2016 CTP3 JSON样品，你可以找到一个函数，可以清除key：value pa的数组国税局和创建数组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')

用于JSON输出整型数组的SQL Server 2016

相关推荐