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:
[{
    &quot;id&quot;: 1,
    &quot;name&quot;: &quot;Josian&quot;,
    &quot;LuckyNumbers&quot;: [581, 777]
}, {
    &quot;id&quot;: 2,
    &quot;name&quot;: &quot;Paul&quot;,
    &quot;LuckyNumbers&quot;: [123551, 5, 646464, 1345, 75, 76]
}, {
    &quot;id&quot;: 3,
    &quot;name&quot;: &quot;Seasonique&quot;,
    &quot;LuckyNumbers&quot;: [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))) &quot;PersonsNumbers&quot;
from (
    select
      p.id,p.name,json_agg(ln.LuckyNumber) &quot;LuckyNumbers&quot;
    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 ?
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}
            ]
    }
]