I have a collection like this:
[
{"userId": "0000", "algorithm": "algo1", "status": "Running", "waitingTime": 0},
{"userId": "0001", "algorithm": "algo1", "status": "Received", "waitingTime": 0},
{"userId": "0000", "algorithm": "algo2", "status": "Completed", "waitingTime": 123},
{"userId": "0000", "algorithm": "algo2", "status": "Error", "waitingTime": 134},
{"userId": "0001", "algorithm": "algo2", "status": "Error", "waitingTime": 150},
{"userId": "0001", "algorithm": "algo3", "status": "Completed", "waitingTime": 100},
{"userId": "0000", "algorithm": "algo3", "status": "Completed", "waitingTime": 120},
{"userId": "0001", "algorithm": "algo1", "status": "Received", "waitingTime": 0}
]
I need to find each user's maximum waiting time but only with status "Completed". The output should have 2 props for each document.
For this example the output should be something like this:
[
{"_id": "0000", "maxWaitingTime": 123},
{"_id": "0001", "maxWaitingTime": 100}
]
I tried to combine the some filtering queries together but it didn't worked out well. I can combine one or two statements but for this one I'm strugle.
I tried to combine the distinct method with find to get unique userId's but it didn't worked:
db.tasks.distinct("userId").find();
Also I tried to do the logic inside of the find fucntion:
printjsononeline(db.tasks.distinct(userId).find({
userId: "0001"
}).map(doc => doc));
But unfortunetly it doesn't worked eather.
You'll need to use an aggregation (see https://docs.mongodb.com/manual/aggregation/ ). The match operator will allow you to filter on the status, and the group operator will allow you to get the maximum value for the wait time for a given user ID.
db.collection.aggregate([
{
"$match": {//Filter completed
"status": "Completed"
}
},
{
$group: {//Group by user id and find max
"_id": "$userId",
"waitingTime": {
"$max": "$waitingTime"
}
}
}
])
Note: Mongo is case sensitive. And people do use waiting_time than waitingTime.