Sql Server - Build a json array of integer from an aggregate

Question

I want to use an aggregate to build a simple single-dimensional JSON array of scalar values, like the LuckyNumbers array in the example below:

[{
    "id": 1,
    "name": "Josian",
    "LuckyNumbers": [581, 777]
}, {
    "id": 2,
    "name": "Paul",
    "LuckyNumbers": [123551, 5, 646464, 1345, 75, 76]
}, {
    "id": 3,
    "name": "Seasonique",
    "LuckyNumbers": [1]
}]

With Postgresql 12, you can do it using native aggregate functions ( fiddle )

--Postgresql 12 schema
create table person (   Id int primary key,   name varchar(20) ); 
insert into person values (1,'Josian'), (2,'Paul'), (3,'Seasonique');  
create table LuckyNumbers (PersonId int references person(Id), LuckyNumber int);
insert into LuckyNumbers (PersonId, LuckyNumber) values (1,581), (1,777), (2,123551), (2,5), (2,646464), (2,1345), (2,75), (2,76), (3,1);

--Query to JSON
select
  array_to_json(array_agg(row_to_json(r))) "PersonsNumbers"
from (
    select
      p.id,p.name,json_agg(ln.LuckyNumber) "LuckyNumbers"
    from person as p
      inner join
        LuckyNumbers as ln
        on
          p.id=ln.PersonId
    group by p.id,p.name
  )
  r

This will output the desired result, exact as above.

The SqlServer2019 example below however requires the use of string_agg which is a string function and then manually concatenating array brackets. ( fiddle )

--Sqlserver 2019 schema creation
create table person (   Id int primary key,   name nvarchar(20) );
insert person values (1,'Josian'), (2,'Paul'), (3,'Seasonique');
create table LuckyNumbers (PersonId int foreign key references person(Id), LuckyNumber int);
insert LuckyNumbers (PersonId, LuckyNumber) values (1,581), (1,777), (2,123551), (2,5), (2,646464), (2,1345), (2,75), (2,76), (3,1); 

--Query to JSON
SELECT p.id
    ,p.NAME
    ,Json_Query('[' + string_agg(ln.LuckyNumber, ',') + ']') 'LuckyNumbers'
FROM person p
INNER JOIN LuckyNumbers ln ON p.id = ln.PersonId
GROUP BY p.id
    ,p.NAME
FOR json path

--JSON to Query : OPENJSON and out apply allow you to read back the array !
DECLARE @JsonOutput NVARCHAR(max) = (
        SELECT p.id
            ,p.NAME
            ,Json_Query('[' + string_agg(ln.LuckyNumber, ',') + ']') 'LuckyNumbers'
        FROM person p
        INNER JOIN LuckyNumbers ln ON p.id = ln.PersonId
        GROUP BY p.id
            ,p.NAME
        FOR json path
        );

SELECT id
    ,NAME
    ,luckynumber
FROM openjson(@JsonOutput) WITH (
        id INT 'strict $.id'
        ,NAME NVARCHAR(50) '$.name'
        ,LuckyNumbers NVARCHAR(MAX) '$.LuckyNumbers' AS JSON
        )
OUTER APPLY OPENJSON(LuckyNumbers) WITH (LuckyNumber NVARCHAR(8) '$');

Is there a better way to do this with Sqlserver ? Did I miss a native aggregation function similar to Postgre's json_agg ?

Answer 1

You could approximate the desired output like this:

SELECT p.id
    , p.[name]
    , LuckNumbers = (
            SELECT ln.LuckyNumber 
            FROM #LuckyNumbers ln 
            WHERE ln.PersonId = p.Id 
            FOR JSON PATH
        )
FROM #person p
FOR JSON PATH;

Essentially, creating a JSON array of int values for each person's lucky numbers. I'm not sure how to remove the "LuckyNumber" tag from each value; this is my first time with JSON on SQL Server.

The output looks like:

[
    {
        "id":1
        ,"name":"Josian"
        ,"LuckNumbers":[
             {"LuckyNumber":581}
            ,{"LuckyNumber":777}
            ]
    }
    ,{
        "id":2
        ,"name":"Paul"
        ,"LuckNumbers":[
             {"LuckyNumber":123551}
            ,{"LuckyNumber":5}
            ,{"LuckyNumber":646464}
            ,{"LuckyNumber":1345}
            ,{"LuckyNumber":75}
            ,{"LuckyNumber":76}
            ]
    }
    ,{
        "id":3
        ,"name":"Seasonique"
        ,"LuckNumbers":[
            {"LuckyNumber":1}
            ]
    }
]

Answer 2

Your string_agg approach is the best I've been able to come up with. It does not seem to be possible to select just an array of scalars. What I've done is to first make a temp table of my data and then select it out as JSON afterward, using JSON_QUERY to parse it back from a string.

SELECT
  [id],
  [name],
  [luckyNumbers] = CONCAT('[',
     SELECT STRING_AGG([LuckyNumber], ',')
     FROM [YourTable]
     GROUP BY [id]
  ']')
INTO [#Temp]
FROM [YourSourceTables]

-- Then select it out as one JSON string

SELECT 
  [id],
  [name],
  [luckyNumbers] = JSON_QUERY([luckyNumbers])
FROM [#Temp]
FOR JSON PATH

You might not need the temp table/select approach. This example is a simplified version of a more complex query I had to assemble and was able to get it to work this way so I had an array of INTs.