Mongo DB duplication issue while using sorting with limit and skip in aggregation

Question

Facing an issue of duplicate records while fetching record by sorting with skip and limit:

Collection Data:

{ 
    "_id" : ObjectId("594b507c9b9469ec9da6a78b"), 
    "name" : "F", 
    "percentage" : 60.0, 
    "weightedFilter" : 2.0, 
    "like" : 1.0, 
    "attraction" : 1.0
}
{ 
    "_id" : ObjectId("594b507c9b9469ec9da6a78c"), 
    "name" : "I", 
    "percentage" : 80.0, 
    "weightedFilter" : 0.0, 
    "like" : 1.0, 
    "attraction" : 1.0
}
{ 
    "_id" : ObjectId("594b507c9b9469ec9da6a78d"), 
    "name" : "J", 
    "percentage" : 80.0, 
    "weightedFilter" : 1.0, 
    "like" : 1.0, 
    "attraction" : 1.0
}
{ 
    "_id" : ObjectId("594b507c9b9469ec9da6a78e"), 
    "name" : "A", 
    "percentage" : 100.0, 
    "weightedFilter" : 0.0, 
    "like" : 1.0, 
    "attraction" : 1.0
}
{ 
    "_id" : ObjectId("594b507c9b9469ec9da6a78f"), 
    "name" : "K", 
    "percentage" : 80.0, 
    "weightedFilter" : 1.0, 
    "like" : 1.0, 
    "attraction" : 1.0
}
{ 
    "_id" : ObjectId("594b507c9b9469ec9da6a790"), 
    "name" : "G", 
    "percentage" : 60.0, 
    "weightedFilter" : 2.0, 
    "like" : 1.0, 
    "attraction" : 1.0
}
{ 
    "_id" : ObjectId("594b507c9b9469ec9da6a791"), 
    "name" : "H", 
    "percentage" : 80.0, 
    "weightedFilter" : 0.0, 
    "like" : 1.0, 
    "attraction" : 1.0
}
{ 
    "_id" : ObjectId("594b507c9b9469ec9da6a792"), 
    "name" : "B", 
    "percentage" : 100.0, 
    "weightedFilter" : 0.0, 
    "like" : 1.0, 
    "attraction" : 1.0

}

Aggregation Query 1:

db.testing.aggregate([{$sort : { "like": -1 }},{$skip : 0},{$limit:4}]);

Output:

{ 
    "_id" : ObjectId("594b507c9b9469ec9da6a78d"), 
    "name" : "J", 
    "percentage" : 80.0, 
    "weightedFilter" : 1.0, 
    "like" : 1.0, 
    "attraction" : 1.0
}
{ 
    "_id" : ObjectId("594b507c9b9469ec9da6a78b"), 
    "name" : "F", 
    "percentage" : 60.0, 
    "weightedFilter" : 2.0, 
    "like" : 1.0, 
    "attraction" : 1.0
}
{ 
    "_id" : ObjectId("594b507c9b9469ec9da6a78c"), 
    "name" : "I", 
    "percentage" : 80.0, 
    "weightedFilter" : 0.0, 
    "like" : 1.0, 
    "attraction" : 1.0
}
{ 
    "_id" : ObjectId("594b507c9b9469ec9da6a78e"), 
    "name" : "A", 
    "percentage" : 100.0, 
    "weightedFilter" : 0.0, 
    "like" : 1.0, 
    "attraction" : 1.0
}

Aggregation Query 2:

db.testing.aggregate([{$sort : { "like": -1 }},{$skip : 4},{$limit:4}]);

{ 
    "_id" : ObjectId("594b507c9b9469ec9da6a78b"), 
    "name" : "F", 
    "percentage" : 60.0, 
    "weightedFilter" : 2.0, 
    "like" : 1.0, 
    "attraction" : 1.0
}
{ 
    "_id" : ObjectId("594b507c9b9469ec9da6a78c"), 
    "name" : "I", 
    "percentage" : 80.0, 
    "weightedFilter" : 0.0, 
    "like" : 1.0, 
    "attraction" : 1.0
}
{ 
    "_id" : ObjectId("594b507c9b9469ec9da6a78e"), 
    "name" : "A", 
    "percentage" : 100.0, 
    "weightedFilter" : 0.0, 
    "like" : 1.0, 
    "attraction" : 1.0
}
{ 
    "_id" : ObjectId("594b507c9b9469ec9da6a792"), 
    "name" : "B", 
    "percentage" : 100.0, 
    "weightedFilter" : 0.0, 
    "like" : 1.0, 
    "attraction" : 1.0
}

Conclusion:

When changing skip 0->4, got duplicate record having name F,I,A

Not getting why this happen?

Catch here is that everything has the same value of `"like"`. You kind of really want to implement "exluding" the "previous page", where you can. As a basic, sort on both "like" and "_id". For more detail, look at something like [Implementing pagination in mongodb](https://stackoverflow.com/a/28105442/2313887) — Neil Lunn, Jun 22 '17 at 06:53
Yeah, basically. Otherwise we cannot guarantee which order the same value for `"like"` will come out in. `_id` seems the obvious choice as the second condition on the sort. Should be enough, otherwise follow the example in the linked answer I gave where you can. That's better than `$skip` and `$limit` if you are always going "forward" in paging anyway. — Neil Lunn, Jun 22 '17 at 07:08
do _id will always in ascending order? because while we are using monggose it assign _id to sub document as well and also we are using lookup to fetch data — Yadav Chetan, Jun 22 '17 at 07:14
Yes. It's monotonic, mostly due to the leading bytes as timestamp and an incremented counter value. Read the [ObjectId](https://docs.mongodb.com/manual/reference/method/ObjectId/) docs to geek out. — Neil Lunn, Jun 22 '17 at 07:17
Sorry so `"likes"` descending and `_id` **ascending** is probably best, if I did not make that clear. — Neil Lunn, Jun 22 '17 at 07:19

Shivam Mishra · Accepted Answer · 2017-06-22T09:15:09.003

As per your collection data you are sorting by key having common values.

In first Aggregation aggregation you are using (skip,limit) => (0,4) in this case mongo is sorting the documents in order from all the documents and the result is sorted.

In second Aggregation you are again using (skip,limit) => (4,4) in this case mongo is sorting the documents from all of the document where documents can be duplicates while having same value in key.

So after sorting by your your data you should sort your data with any unique key (either ‘_id’ or ‘name’) as you wish Note : key should be unique

something like below

db.testing.aggregate([
    {
        $sort : { 
          "percentage": -1,
          "_id" : 1
        },
    },
    {
        $skip : 0
    },
    {
        $limit:4
    }
]);

score 2 · Answer 2 · answered Jun 24 '22 at 14:47

When using sort in conjunction with skip and limit in MongoDB, add an extra _id field (current document ObjectId) to uniquely sort each document and avoid key duplications.

Example:

 const response = await testing
 .find({})
 .sort({ percentage: -1, _id: -1 })
 .limit(limitValue).skip(skipValue)

Mongo DB duplication issue while using sorting with limit and skip in aggregation

}

2 Answers2

Linked