如何在 tsql 程序中將相關表查詢到 json 中？我正在嘗試使用 STUFF 功能。我需要輸出這樣的json：
{
"Id": "4E7ECBE6-3EF6-4F14-B117-C39FCAC00DD5",
"tasks": [
{
"taskOrders": [
{
"merchantId": "EFEDE945-3A17-487B-919B-406C9DA8B8A9",
"merchantOrderNumber": "1129"
}
],
"totalWeight": 3,
"state": 1
}
]
}
我正在嘗試將此類查詢用于“任務”部分，并將其與上部欄位連接起來：
select '"tasks":[' STUFF((
select ',{
"taskOrders": [
' STUFF((
select ',{
"merchantId": "' cast(o.merchantId as nvarchar(max)) '",
"merchantOrderNumber": "' cast(o.orderNumber as nvarchar(max)) '"
}'
from dbo.Order o
where o.orderNumber = chk.orderNumber
for xml path(''), type
).value('.', 'varchar(max)'), 1, 1, '') '
],
"totalWeight": ' cast(chk.weight as nvarchar(max)) ',
"state": ' cast(chk.state as nvarchar(max)) '"
}'
from dbo.Checkpoints as chk
for xml path(''), type
).value('.', 'varchar(max)'), 1, 1, '') ']'
我想我做錯了什么，因為輸出看起來像這樣：
"tasks":[{
"taskOrders": [ {
"merchantId": "EFEDE945-3A17-487B-919B-406C9DA8B8A9",
"merchantOrderNumber": "111"
}
],
"totalWeight": 1,
"state": 1
}
],
"totalWeight": 1,
"state": 1
}]
我應該使用游標還是可以通過 STUFF 來完成它？
uj5u.com熱心網友回復：
SQL Server 2016 及更高版本具有對 JSON 的內置支持。而不是使用STUFF，FOR XML在這里嘗試使用FOR JSON PATH，例如：
/*
* Example data...
*/
create table dbo.[Order] (
merchantId uniqueidentifier,
orderNumber varchar(10)
);
insert dbo.[Order] (merchantId, orderNumber) values
('EFEDE945-3A17-487B-919B-406C9DA8B8A9', '1129'),
('d79ce36c-5721-414b-a050-0a9105defe67', '1129');
create table dbo.Checkpoints (
Id uniqueidentifier,
orderNumber varchar(10),
state int,
weight int
);
insert dbo.Checkpoints (Id, orderNumber, state, weight) values
('4E7ECBE6-3EF6-4F14-B117-C39FCAC00DD5', '1129', 1, 1);
/*
* Example query..
*/
select
chk.Id,
json_query((
select
o.merchantId,
o.orderNumber as merchantOrderNumber
from dbo.[Order] o
where o.orderNumber = chk.orderNumber
for json path
), '$') as tasks,
weight as totalWeight,
state
from dbo.Checkpoints chk
for json path, without_array_wrapper;
產生輸出：
{
"Id": "4E7ECBE6-3EF6-4F14-B117-C39FCAC00DD5",
"tasks": [
{
"merchantId": "EFEDE945-3A17-487B-919B-406C9DA8B8A9",
"merchantOrderNumber": "1129"
},
{
"merchantId": "D79CE36C-5721-414B-A050-0A9105DEFE67",
"merchantOrderNumber": "1129"
}
],
"totalWeight": 1,
"state": 1
}
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/446849.html