My dataset looks 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 am seeking the maximum waiting time for each user, only when the status is set to "Completed". The desired output should include 2 properties per entry.
- "_id" - matching the "userId" field from the original collection
- "maxWaitingTime" - indicating the highest waiting time among "Completed" entries
In this case, the expected output should be:
[
{"_id": "0000", "maxWaitingTime": 123},
{"_id": "0001", "maxWaitingTime": 100}
]
I attempted to combine multiple filtering queries but encountered challenges. I managed to combine one or two statements, but struggling with this particular task.
I experimented with using the distinct method along with find to obtain unique userId's, but without success:
db.tasks.distinct("userId").find();
Additionally, I tried incorporating the logic within the find function:
printjsononeline(db.tasks.distinct(userId).find({
userId: "0001"
}).map(doc => doc));
Unfortunately, neither approach yielded the desired results.