CODEWITHSUNDEEP
mongodbsortingaggregation-frameworklimitskip
Yadav Chetan
Yadav Chetan

Reputation: 1894

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

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?

Upvotes: 12

Views: 3908

Answers (2)

Himanshu Shukla
Himanshu Shukla

Reputation: 51

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)

Upvotes: 3

Shivam Mishra
Shivam Mishra

Reputation: 1856

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
    }
]);

Upvotes: 24

Related Questions