SQL Server 2016 for integer JSON output array

Question

I would like to get JSON with an array of integers using the SQL Server 2016 For JSON function. I have a limit on an array of integers.

Database table structures:

 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) 

Desired Results:

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

This is the closest I got, 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') 

returns:

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

Answer 1

In the CTP3 JSON example from AdventureWorks 2016, you can find a function that can clear an array of key: value pairs and create od array 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 

Just specify the result of the SELECT FOR JSON expression as the @json parameter and the name of the key you want to remove as the second parameter. Perhaps 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') 

Answer 2

You can use FOR XML PATH and STUFF to make PermissionID one lowercase comma for each Employee , use QUOTENANE on it, then put everything in a variable and replace "[ with [ and ]" with ] :

 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 

Output:

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

EDIT:

Another way:

 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,'') + ']}' 

Output:

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