我有一個如下所示的表，需要將 JSON 格式的資料回傳到 Azure Logic App。
在下面添加表格腳本。
CREATE TABLE [dbo].[TempStudentJsonData]
(
[StudentID] [int] NULL,
[subject] [varchar](500) NULL,
[result] [varchar](10) NULL
)
GO
以下是測驗資料。
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
從這個表中，我想生成一個如下所示的 JSON
[
{
"506995": [
{
"subject": "Physics",
"result": "Pass"
},
{
"subject": "Chemistry",
"result": "Fail"
}
]
},
{
"506996" : [
{
"subject": "Physics",
"reason": "Pass"
},
{
"subject": "Biology",
"reason": "Pass"
}
]
},
{
"506997" : [
{
"subject": "Math",
"reason": "Pass"
}
]
}
]
我們怎樣才能做到這一點？
謝謝
uj5u.com熱心網友回復：
不幸的是，SQL Server 不支持動態鍵，也不支持JSON_AGG（創建一個裸 JSON 陣列）。所以你需要自己構建一些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;
請注意，此版本相對于相關自聯接的好處是您只掃描表一次
db<>小提琴
輸出：
[
{
"506995": [
{
"subject": "PHYSICS",
"result": "Pass"
},
{
"subject": "CHEMISTRY",
"result": "Fail"
}
]
},
{
"506996": [
{
"subject": "PHYSICS",
"result": "Pass"
},
{
"subject": "BIOLOGY",
"result": "Pass"
}
]
},
{
"506997": [
{
"subject": "MATH",
"result": "Pass"
}
]
}
]
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/495820.html