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, May 28 '16 at 03:06
creat a view /resultset using for permID and then get result done http://stackoverflow.com/questions/10461874/sql-server-concatenate-group-by — sandeep rawat, May 28 '16 at 05: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: https://stackoverflow.com/a/48245381/419761 — l33t, Jan 15 '18 at 00:29

score 11 · Answer 1 · answered Jul 18 '16 at 21:36

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

score 2 · Answer 2 · edited Apr 23 '21 at 13:25

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 '21 at 13:25

np_6

514
1
6
19

answered May 28 '16 at 08:19

gofr1

15,741
11
42
52

Close, though I'm wanting an array of numbers, like this: "Permissions":[1,2,3] – Jens Frandsen May 30 '16 at 03:02
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. – Jens Frandsen May 30 '16 at 18:30
1

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. :) – gofr1 May 30 '16 at 18:34
6

Thanks gofr1. I'm finding it hard to accept, after waiting so long for Microsoft to support JSON, that I have already reached limitations. – Jens Frandsen May 30 '16 at 21:50
2

@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. – ADJenks Jun 01 '18 at 01:43
1

Any news on this? For the time being I'm just removing the keys right after parsing the JSON output in the application – Jose Oct 21 '18 at 12:20

G Clovs · Accepted Answer · 2022-02-07T21:30:16.053

2

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 07 '22 at 21:30

answered Jan 26 '22 at 15:54

G Clovs

2,442
3
19
24

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 Jan 27 '22 at 17:10
I couldn't edit your answer to include the full query, so I'll add at the end of my question. – Jens Frandsen Jan 27 '22 at 17:10
I edited the answer with the full query. After 6 years you can maybe get this topic checked ✓. regards ;) – G Clovs Feb 07 '22 at 21:06

SQL Server 2016 for JSON output integer array

3 Answers3

Linked

Related