用于 JSON 输出整数数组的 SQL Server 2016
我想使用 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 示例中，您可以找到一个可以清理键值对数组并创建数组 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')
相关文章