我正在使用 Microsoft SQL server v14.0.1000 我正在使用以下查詢將我的查詢結果轉換為 JSON：
SELECT TOP(5) dn.id AS ID,
dn.EventTimeStamp AS EventTimeStamp,
dn.ControllerId AS ControllerID,
JSON_QUERY((SELECT ControllerName
FROM Controllers
WHERE dn.ControllerId = Controllers.ControllerID
FOR JSON PATH, without_array_wrapper)) AS ControllerName
FROM DashboardNotifications dn
ORDER BY dn.ID DESC
FOR JSON PATH
結果得到以下JSON：
[
{
"ID": 354,
"EventTimeStamp": "2022-05-17T05:35:25",
"ControllerId": 24,
"ControllerName": {
"ControllerName": "P25-SC-0233"
}
},
{
"ID": 353,
"EventTimeStamp": "2022-05-17T05:34:20",
"ControllerId": 17,
"ControllerName": {
"ControllerName": "P25-SC-0226"
}
},
{
"ID": 352,
"EventTimeStamp": "2022-05-17T05:33:50",
"ControllerId": 16,
"ControllerName": {
"ControllerName": "P25-SC-0225"
}
}
]
我已經能夠從
"ControllerName":[{"ControllerName":"P25-SC-0233"}],
雖然我想要的輸出是：
[
{
"ID": 354,
"EventTimeStamp": "2022-05-17T05:35:25",
"ControllerId": 24,
"ControllerName": "P25-SC-0233"
},
{
"ID": 353,
"EventTimeStamp": "2022-05-17T05:34:20",
"ControllerId": 17,
"ControllerName": "P25-SC-0226"
},
{
"ID": 352,
"EventTimeStamp": "2022-05-17T05:33:50",
"ControllerId": 16,
"ControllerName": "P25-SC-0225"
}
]
我需要進行哪些更改才能洗掉嵌套的 ControllerName 鍵？
uj5u.com熱心網友回復：
我認為你只想要沒有子查詢FOR JSON（然后你不需要JSON_QUERY逃避它）
SELECT TOP(5) dn.id AS ID,
dn.EventTimeStamp AS EventTimeStamp,
dn.ControllerId AS ControllerID,
(
SELECT c.ControllerName
FROM Controllers c
WHERE dn.ControllerId = c.ControllerID
) AS ControllerName
FROM DashboardNotifications dn
ORDER BY dn.ID DESC
FOR JSON PATH;
確保您的子查詢保證最多回傳一行
你也可以只使用一個連接
SELECT TOP(5) dn.id AS ID,
dn.EventTimeStamp,
dn.ControllerId,
c.ControllerName
FROM DashboardNotifications dn
LEFT JOIN
Controllers c ON dn.ControllerId = c.ControllerID
ORDER BY dn.ID DESC
FOR JSON PATH;
uj5u.com熱心網友回復：
絕對沒有必要json_query。將查詢更改為：
SELECT TOP(5) dn.id AS ID, dn.EventTimeStamp AS EventTimeStamp, dn.ControllerId AS ControllerID, (
SELECT ControllerName FROM Controllers WHERE dn.ControllerId = Controllers.ControllerID
) AS ControllerName
FROM DashboardNotifications dn
ORDER BY dn.ID DESC
FOR JSON PATH
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/476567.html