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

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 : OPE

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

StackExchange Database Administrators Q#259163, answer score: 5