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
?