SQL Server 2016 for JSON output integer array

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.ufnToRawJsonArrayGOCREATE 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 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:

selecte.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 efor json path, root('EmployeePermissions')

You can use FOR XML PATH and STUFF to make PermissionID one string comma separated for each Employee, use QUOTENANE on it, then put all in 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 eFOR XML PATH('')),1,1,'') + ']}'

Output:

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