Consider I have a timesheets collection like this:
[
{
_id: 1,
createdBy: "John",
duration: "00:30"
},
{
_id: 2,
createdBy: "John",
duration: "01:30"
},
{
_id: 3,
createdBy: "Mark",
duration: "00:30"
},
]
My input is an array of usernames:
["John", "Mark", "Rikio"]
I want to use mongodb aggregate to calculate the total duration of timesheets for each user in the usernames array and If there are no timesheets found, it should return duration: "00:00". For example, it should return:
[
{createdBy: "John", totalDuration: "02:00"},
{createdBy: "Mark", totalDuration: "00:30"},
{createdBy: "Rikio", totalDuration: "00:00"}
]
However, when I use $match query, if there are no timesheets it will not return anything so I don't know which user does not have the timesheets and return "00:00" for them.
CodePudding user response：
I totally agree with @turivishal , but still can make it through mongo query with an ugly one.
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"
]
}
}
}
])