Filter MongoDB Collections with a multiply variations

user_13870464 0
Viewed 56

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.

  • "_id" - which should be "userId" from original collection
  • "maxWaitingTime" - should be equal to the maximum waiting time for status "Completed"

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.

mongodb javascript
2 Answers

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.

user_3523150

Playground

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.

user_26941840
Related