CODEWITHSUNDEEP
arrayssql-serverjsonsql-server-2016
Jens Frandsen
Jens Frandsen

Reputation: 862

SQL Server 2016 for JSON output integer array

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

Upvotes: 15

Views: 7144

Answers (3)

G Clovs
G Clovs

Reputation: 3152

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

Upvotes: 4

gofr1
gofr1

Reputation: 15987

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

Upvotes: 2

Jovan MSFT
Jovan MSFT

Reputation: 14630

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

Upvotes: 12

Related Questions