Getting the N documents in MongoDB before a Document ID from a Sorted Result

user_2476944 0
Viewed 263

I have a collection in MongoDB, like the one below. -> Mongo Playground link enter image description here

I have sorted the collection with Overview and ID.

$sort{{ overview: 1,_id:1 }}

which results in a collection like this.

enter image description here

When I filter the collection to show only the documents after "subject 13.", it works as expected.

$match{{
  _id:{$gt:ObjectId('605db89d208db95eb4878556')}
}}

enter image description here

however, when I try to the documents before "subject 13", that is "Subject 6" , with the following query, it doesn't work as I expect.

$match{{
      _id:{$lt:ObjectId('605db89d208db95eb4878556')}
    }}

Instead of getting just "Subject 6" in the result, I get the following.

enter image description here

I suspect this is happening because, mongodb always filters the document before sorting, regardless of the order in aggregate pipeline.

Please suggest me a way to get the documents before a particular "_id" in mongodb.

I have 600 documents in the collection, this is a sample dataset. My Full aggregate query below.

[
  {
    '$sort': {
      'overview': 1, 
      '_id': 1
    }
  }, {
    '$match': {
      '_id': {
        '$lt': new ObjectId('605db89d208db95eb4878556')
      }
    }
  }
]
mongodb-query mongodb pagination
1 Answers

MongoDB optimizes the query performance by moving sort to the end in your case as you've $sort followed by $match

https://docs.mongodb.com/manual/core/aggregation-pipeline-optimization/#sort-match-sequence-optimization

When you have a sequence with $sort followed by a $match, the $match moves before the $sort to minimize the number of objects to sort. For example, if the pipeline consists of the following stages:

[
  { '$sort': { 'overview': 1, '_id': 1 }  },
  { '$match': { '_id': { '$lt': new ObjectId('605db89d208db95eb4878556') } }
]

During the optimization phase, the optimizer transforms the sequence to the following:

[
    { '$match': { '_id': { '$lt': new ObjectId('605db89d208db95eb4878556') } },
    { '$sort': { 'overview': 1, '_id': 1 } }
]

Query planner Result -

We can see the 1st stage is the match query, after that sort is performed.

{
    "stages" : [ 
        {
            "$cursor" : {
                "query" : {
                    "_id" : {
                        "$lt" : ObjectId("605db89d208db95eb4878556")
                    }
                },
                "queryPlanner" : {
                    "plannerVersion" : 1,
                    "namespace" : "video.data3",
                    "indexFilterSet" : false,
                    "parsedQuery" : {
                        "_id" : {
                            "$lt" : ObjectId("605db89d208db95eb4878556")
                        }
                    },
                    "winningPlan" : {
                        "stage" : "FETCH",
                        "inputStage" : {
                            "stage" : "IXSCAN",
                            "keyPattern" : {
                                "_id" : 1
                            },
                            "indexName" : "_id_",
                            "isMultiKey" : false,
                            "multiKeyPaths" : {
                                "_id" : []
                            },
                            "isUnique" : true,
                            "isSparse" : false,
                            "isPartial" : false,
                            "indexVersion" : 2,
                            "direction" : "forward",
                            "indexBounds" : {
                                "_id" : [ 
                                    "[ObjectId('000000000000000000000000'), ObjectId('605db89d208db95eb4878556'))"
                                ]
                            }
                        }
                    },
                    "rejectedPlans" : []
                }
            }
        }, 
        {
            "$sort" : {
                "sortKey" : {
                    "overview" : 1,
                    "_id" : 1
                }
            }
        }
    ],
    "ok" : 1.0
}
user_22242650
Related