SQL Server 2016 for JSON output integer array

Question

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.

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

returns:

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

SOLUTION - SQL Server 2017 and on

select
    e.Name as Employee,
    (select 
         '[' + STRING_AGG(ep.PermissionID, ',') + ']' 
     from @EmployeePermissions ep 
     where ep.EmployeeID=e.ID) as Permissions 
from
    @Employees e
for json path, root('EmployeePermissions')

Pugilism

Can you please share the working code, it will helps to give more answers – Irrelevant

creat a view /resultset using for permID and then get result done #10462374 – Bravar

@Irrelevant - added my closest code. – Pugilism

If you can live with the problematic output from SQL Server I suggest you let the consumer know about these aliases. If you're using Json.NET, then see this excellent answer: https://mcmap.net/q/830506/-name-array-elements-in-json-net – Nonpayment

Answer 1

This should work on SQL Server 2017 (14.x) and later

SELECT '[' + STRING_AGG(ep.PermissionID, ',') + ']'
FROM @EmployeePermissions ep

You have to cast your value to NVarchar(max) only if your result string exceed 8000bytes

SELECT '[' + STRING_AGG(cast(ep.PermissionID AS NVARCHAR(MAX)), ',') + ']'
FROM @EmployeePermissions ep

FullQuery

SELECT e.Name as Employee,
    (SELECT 
         '[' + STRING_AGG(cast(ep.PermissionID AS NVARCHAR(MAX)), ',') + ']' 
     FROM @EmployeePermissions ep 
     WHERE ep.EmployeeID=e.ID) as Permissions 
FROM
    @Employees e
FOR JSON PATH, root('EmployeePermissions')

Quadrennial

yay. This does it with the STRING_AGG function that came into being in SQL Server 2017. Thanks - I will be using this. – Pugilism

I couldn't edit your answer to include the full query, so I'll add at the end of my question. – Pugilism

I edited the answer with the full query. After 6 years you can maybe get this topic checked ✓. regards ;) – Quadrennial

Answer 2

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

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

Liking

Answer 3

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 e
FOR XML PATH('')),1,1,'') + ']}'

Output:

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

Mandrill

Close, though I'm wanting an array of numbers, like this: "Permissions":[1,2,3] – Pugilism

This is the right output, but I am not wanting strictly string concatenation techniques. The array part can use string tricks, but the outer query (which is simplified in this case) must use the "For JSON" feature. – Pugilism

Well, maybe in next version of SQL Server they add feature were you can call FOR JSON PATH without naming column or create arrays of values else way (like JSON_MODIFY append). But for now it come as it is. Hope you find answer for your question. :) – Mandrill

Thanks gofr1. I'm finding it hard to accept, after waiting so long for Microsoft to support JSON, that I have already reached limitations. – Pugilism

@JensFrandsen Yeah, I'm pretty disappointed I can't seem to figure out how to create a simple array of arrays with my data. I don't want any keys. – Seaport

Any news on this? For the time being I'm just removing the keys right after parsing the JSON output in the application – Sulla