Mongo Aggregate $match after ($sort->$limit)

1
votes

Consider the following mongo collection "events":

{ "_id" : ObjectId("512bc95fe835e68f199c8686"), userId: 1, type: "music" eventNum: 1 }
{ "_id" : ObjectId("512bc962e835e68f199c8687"), userId: 1, type: "music" eventNum: 2 }
{ "_id" : ObjectId("55f5a192d4bede9ac365b257"), userId: 2, type: "music" eventNum: 3 }
{ "_id" : ObjectId("55f5a192d4bede9ac365b258"), userId: 2, type: "music" eventNum: 4 }
{ "_id" : ObjectId("55f5a1d3d4bede9ac365b259"), userId: 1, type: "music" eventNum: 5 }
{ "_id" : ObjectId("55f5a1d3d4bede9ac365b25a"), userId: 1, type: "athletic" eventNum: 6 }
{ "_id" : ObjectId("55f5a1d3d4bede9ac365b25b"), userId: 2, type: "athletic" eventNum: 7 }

Each event is created with a userId, a type and an eventNum. I need to find the top 3 events of userId: 1. So I run this query:

db.getCollection('events').aggregate([
  {
    "$match": {
      "userId": 1
    }
  },
  {
    "$sort": { "eventNum": 1 }
  },
  {
    "$limit": 3
  }
])

Which returns the dataset (NOTE there are no "athletic" events returned):

{ "_id" : ObjectId("512bc95fe835e68f199c8686"), userId: 1, type: "music" eventNum: 1 }
{ "_id" : ObjectId("512bc962e835e68f199c8687"), userId: 1, type: "music" eventNum: 2 }
{ "_id" : ObjectId("55f5a1d3d4bede9ac365b259"), userId: 1, type: "music" eventNum: 5 }

Now, however, I want all "athletic" events of userId: 1, but ONLY if they are in the top 3. Since there are no "athletic" events in the top 3, we would expect the following query to return no documents:

db.getCollection('events').aggregate([
  {
    "$match": {
      "userId": 1
    }
  },
  {
    "$sort": { "eventTime": 1 }
  },
  {
    "$limit": 3
  },
  {
    "$match": {
      "type": "athletic"
    }
  }
])

HOWEVER, this query actually returns this dataset:

{ "_id" : ObjectId("55f5a1d3d4bede9ac365b25a"), userId: 1, type: "athletic" eventNum: 6 }

Can someone explain what is going on here? It seems that the sort/limit is happening after the second match. Is there anyway to get around this without making multiple queries?

4
mongodbaggregation-framework
This is in need of an example really, but it generally points to that you are mistaken. Show the documents returned before your $match condition if you think otherwise. - Neil Lunn
I have added a better example to illustrate the issue. - M1Reeder
What can I say other than Pipeline Sequence Optimization has always been an ill conceived idea. I do not like things happening "behind my back" for this very reason. - Neil Lunn

4 Answers

1
votes

It seems that you're hit by something like this bug.

I tried your exact example, and while I have the same weird behavior than you on MongoDB 3.4.4, on MongoDB 3.0.9 your second query doesn't return anything.

Try to downgrade MongoDB.

1
votes

@ramnes Is correct. This is a bug in mongo 3.4.4. However there is a way to get around this issue by using a $replaceRoot to reassign variables and trick the subsequent matches to stay after the sort/limit. The following query works around the issue and returns what you would expect:

db.getCollection('events').aggregate([
  {
    "$match": {
      "userId": 1
    }
  },
  {
    "$sort": { "eventTime": 1 }
  },
  {
    "$limit": 3
  },
  {
    "$replaceRoot": {
       "newRoot": "$$ROOT"
    }
  },
  {
    "$match": {
      "type": "athletic"
    }
  }
])
0
votes

Here is the solution.

db.getCollection('events').aggregate([
{
  "$match": {
     "$and": [
         {"userId": 1},
         {"type": "athletic"}
     ]
  }
 },
 {
    "$sort": {"eventNum": 1}
 },
 {
   "$limit": 3
 },
 {
   "$group":{
     "_id": null,
     "doc_count": {$sum: 1}, 
     "eventNums": {"$push":"$eventNum"}}
 },
 {
   "$match": {
      "doc_count": 3
 }
},
])

in this approach, it will return top 3 event numbers or empty when there is no enough events.

0
votes

Change "$sort" to following code:

 "$sort": { "eventNum": 1 }  !=   "$sort": { "eventTime": 1 }