考慮我有一個這樣的時間表集合：
[
{
_id: 1,
createdBy: "John",
duration: "00:30"
},
{
_id: 2,
createdBy: "John",
duration: "01:30"
},
{
_id: 3,
createdBy: "Mark",
duration: "00:30"
},
]
我的輸入是一組用戶名：
["John", "Mark", "Rikio"]
我想使用 mongodb 聚合來計算用戶名陣列中每個用戶的時間表的總持續時間，如果沒有找到時間表，它應該回傳持續時間：“00:00”。例如，它應該回傳：
[
{createdBy: "John", totalDuration: "02:00"},
{createdBy: "Mark", totalDuration: "00:30"},
{createdBy: "Rikio", totalDuration: "00:00"}
]
但是，當我使用 $match 查詢時，如果沒有時間表，它將不會回傳任何內容，因此我不知道哪個用戶沒有時間表并為他們回傳“00:00”。
uj5u.com熱心網友回復：
我完全同意@turivishal 的觀點，但仍然可以通過 mongo 查詢和一個丑陋的查詢來實作。
db.collection.aggregate([
{
$match: {}
},
{
$set: {
minutes: {
$let: {
vars: {
time: {
$split: [
"$duration",
":"
]
}
},
in: {
"$add": [
{
"$multiply": [
{
$toInt: {
$first: "$$time"
}
},
60
]
},
{
$toInt: {
$last: "$$time"
}
}
]
}
}
}
}
},
{
$group: {
"_id": "$createdBy",
"totalMinutes": {
"$sum": "$minutes"
}
}
},
{
$group: {
"_id": null,
"docs": {
"$push": "$$ROOT"
}
}
},
{
$set: {
"docs": {
$map: {
"input": [
"John",
"Mark",
"Rikio"
],
"as": "name",
"in": {
$let: {
vars: {
findName: {
"$first": {
"$filter": {
"input": "$docs",
"as": "d",
"cond": {
"$eq": [
"$$d._id",
"$$name"
]
}
}
}
}
},
in: {
"$cond": {
"if": "$$findName",
"then": "$$findName",
"else": {
_id: "$$name",
totalMinutes: 0
}
}
}
}
}
}
}
}
},
{
$unwind: "$docs"
},
{
$replaceRoot: {
"newRoot": "$docs"
}
},
{
$set: {
"hours": {
$floor: {
"$divide": [
"$totalMinutes",
60
]
}
},
"minutes": {
"$mod": [
"$totalMinutes",
60
]
}
}
},
{
$set: {
"hours": {
"$cond": {
"if": {
"$lt": [
"$hours",
10
]
},
"then": {
"$concat": [
"0",
{
"$toString": "$hours"
}
]
},
"else": {
"$toString": "$hours"
}
}
},
"minutes": {
"$cond": {
"if": {
"$lt": [
"$minutes",
10
]
},
"then": {
"$concat": [
"0",
{
"$toString": "$minutes"
}
]
},
"else": {
"$toString": "$minutes"
}
}
}
}
},
{
$project: {
duration: {
"$concat": [
"$hours",
":",
"$minutes"
]
}
}
}
])
mongoplayground
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/410186.html
標籤：
上一篇：具有強制屬性的C#泛型型別