I have a table as shown below and need to return data in JSON format to Azure Logic App.
Adding table scripts below.
CREATE TABLE [dbo].[TempStudentJsonData]
(
[StudentID] [int] NULL,
[subject] [varchar](500) NULL,
[result] [varchar](10) NULL
)
GO
And following is the test data.
INSERT INTO [dbo].[TempStudentJsonData] ([StudentID], [subject], [result])
VALUES (506995, N'PHYSICS', N'Pass')
GO
INSERT INTO [dbo].[TempStudentJsonData] ([StudentID], [subject], [result])
VALUES (506995, N'CHEMISTRY', N'Fail')
GO
INSERT INTO [dbo].[TempStudentJsonData] ([StudentID], [subject], [result])
VALUES (506996, N'PHYSICS', N'Pass')
GO
INSERT INTO [dbo].[TempStudentJsonData] ([StudentID], [subject], [result])
VALUES (506996, N'BIOLOGY', N'Pass' )
GO
INSERT INTO [dbo].[TempStudentJsonData] ([StudentID], [subject], [result])
VALUES (506997, N'MATH', N'Pass')
GO
From this table, I want to generate a JSON as shown below
[
{
"506995": [
{
"subject": "Physics",
"result": "Pass"
},
{
"subject": "Chemistry",
"result": "Fail"
}
]
},
{
"506996" : [
{
"subject": "Physics",
"reason": "Pass"
},
{
"subject": "Biology",
"reason": "Pass"
}
]
},
{
"506997" : [
{
"subject": "Math",
"reason": "Pass"
}
]
}
]
How can we achieve this?
Thanks
CodePudding user response:
Unfortunately, SQL Server does not support dynamic keys, nor does it support JSON_AGG
(creating a bare JSON array). So you need to build some of it yourself using STRING_AGG
SELECT '[' STRING_AGG(sjd.json, ',') ']'
FROM (
SELECT
json =
CONCAT(
'{"',
sjd.StudentID,
'":[',
STRING_AGG(j.json, ','),
']}'
)
FROM TempStudentJsonData sjd
CROSS APPLY (
SELECT
sjd.subject,
sjd.result
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
) j(json)
GROUP BY
sjd.StudentID
) sjd;
Note that the benefit of this version over correlated self-joins is that you only scan the table once
Output:
[
{
"506995": [
{
"subject": "PHYSICS",
"result": "Pass"
},
{
"subject": "CHEMISTRY",
"result": "Fail"
}
]
},
{
"506996": [
{
"subject": "PHYSICS",
"result": "Pass"
},
{
"subject": "BIOLOGY",
"result": "Pass"
}
]
},
{
"506997": [
{
"subject": "MATH",
"result": "Pass"
}
]
}
]