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

Can you please share the working code, it will helps to give more answers — Arulkumar
– Arulkumar, Commented May 28, 2016 at 3:06
creat a view /resultset using for permID and then get result done stackoverflow.com/questions/10461874/… — sandeep rawat
– sandeep rawat, Commented May 28, 2016 at 5:23
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: stackoverflow.com/a/48245381/419761 — l33t
– l33t, Commented Jan 15, 2018 at 0:29

Jovan MSFT · Accepted Answer · 2016-07-18 21:36:28Z

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

G Clovs · Accepted Answer · 2022-02-07 21:30:16Z

4

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

edited Feb 7, 2022 at 21:30

answered Jan 26, 2022 at 15:54

G Clovs

3,2213 gold badges25 silver badges27 bronze badges

3 Comments

Jens Frandsen Over a year ago

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

Jens Frandsen Over a year ago

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

G Clovs Over a year ago

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

np_6 · Accepted Answer · 2021-04-23 13:25:14Z

2

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

edited Apr 23, 2021 at 13:25

np_6

5081 gold badge7 silver badges20 bronze badges

answered May 28, 2016 at 8:19

gofr1

16k11 gold badges47 silver badges56 bronze badges

6 Comments

Jens Frandsen Over a year ago

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

gofr1 Over a year ago

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

Jens Frandsen Over a year ago

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

ADJenks Over a year ago

@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.

Jose Over a year ago

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

|

Collectives™ on Stack Overflow

SQL Server 2016 for JSON output integer array

3 Answers 3

Comments

3 Comments

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related