我試圖首先按日期對 mongodb 中的以下資料進行分組，然后按 Task_type 分組并獲取總持續時間。我試圖通過使用 $group 聚合和 $sum 來實作它，但我只得到特定日期的持續時間的總和。我需要通過分離 Task_type 更進一步
{
"name" : "Employee1",
"Mail" : "[email protected]",
"Contact" : 1111111111,
"Department" : "Operations",
"Joining_Date" : "2022-02-02",
"Password" : "Employee1",
"Work" :
[
{
"date" : "2022-10-07",
"Tasks" : {"Task_description" : "Worked on project","Task_type" : "Work","Start_time" : "09:20:00","duration" : 20}
},
{
"date" : "2022-10-07",
"Tasks" : {"Task_description" : "Attended daily meeting","Task_type" : "Meeting","Start_time" : "10:30:00","duration" : 60}
},
{
"date" : "2022-10-06",
"Tasks" : {"Task_description" : "Lunch break","Task_type" : "Break","Start_time" : "13:00:00","duration" : 50}
},
{
"date" : "2022-10-06",
"Tasks" : {"Task_description" : "Tea Break","Task_type" : "Break","Start_time" : "17:30:00","duration" : 10}
},
{
"date" : "2022-10-08",
"Tasks" : {"Task_description" : "Meeting with partners","Task_type" : "Meeting","Start_time" : "13:00:00","duration" : 50}
}
]
}
我想要的輸出是：
{_id:'2022-10-07', WorkDuration:20, MeetingDuration:60, BreakDuration:0},
{_id:'2022-10-06', WorkDuration:0, MeetingDuration:0, BreakDuration:60},
{_id:'2022-10-08', WorkDuration:0, MeetingDuration:50, BreakDuration:0}
uj5u.com熱心網友回復：
一種選擇是$group兩次。首先由Task_type和date，然后僅由date，根據每個 總結持續時間Task_type：
db.collection.aggregate([
{$unwind: "$Work"},
{$project: {
_id: 0,
date: "$Work.date",
Task_type: "$Work.Tasks.Task_type",
duration: "$Work.Tasks.duration"
}},
{$group: {
_id: {Task_type: "$Task_type", "date": "$date"},
duration: {$sum: "$duration"}
}},
{$group: {
_id: "$_id.date",
WorkDuration: {$sum: {$cond: [{$eq: ["$_id.Task_type", "Work"]}, "$duration", 0]}},
MeetingDuration: {$sum: {$cond: [{$eq: ["$_id.Task_type", "Meeting"]}, "$duration", 0]}},
BreakDuration: {$sum: {$cond: [{$eq: ["$_id.Task_type", "Break"]}, "$duration", 0]}}
}},
{$sort: {_id: 1}}
])
看看它在操場上的例子是如何作業的
轉載請註明出處，本文鏈接：https://www.uj5u.com/caozuo/512455.html