SQL Server 2016 for JSON输出整数数组 [英] SQL Server 2016 for JSON output integer array

问题描述

我想使用SQL Server 2016的For JSON功能获取带有整数数组的JSON.我迷上了整数数组.

I'd like to get JSON with an array of integers using SQL Server 2016's For JSON feature. I'm stumped on array of integers.

数据库表结构:

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]}
}

这是我得到的最接近的东西，但不是我想要的.

This is the closest I've gotten, but not quite what I want.

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}]}
}

推荐答案

在AdventureWorks 2016 CTP3 JSON示例中，您可以找到一个可以清除key:value对数组并创建od值的函数:

In AdventureWorks 2016 CTP3 JSON sample you can find a function that can clean array of key:value pairs and create array od values:

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参数，并提供您要删除的键的名称作为第二个参数即可.大概是这样的:

Just provide result of your SELECT FOR JSON expression as @json parameter and name of the key that you want to remove as second parameter. Probably something like:

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')

这篇关于SQL Server 2016 for JSON输出整数数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！