Mongodb aggregation skip and limit with sorting bring duplicate records in pagination

Question

I have the following query that first sort the documents then skip and limit 10 records, following is my query:

db.getCollection('jobpostings').aggregate([
{"$match":{
    "expireDate":{"$gte": ISODate("2018-08-12T00:00:00.000Z")},
    "publishDate":{"$lt": ISODate("2018-08-13T00:00:00.000Z")},
    "isPublished":true,
    "isDrafted":false,
    "deletedAt":{"$eq":null},
    "deleted":false,
    "blocked":{"$exists":false}
}},
{"$lookup":{"from":"companies","localField":"company.id","foreignField":"_id","as":"companyDetails"}},
{"$match":{"companyDetails":{"$ne":[]}}},
{"$sort":{
    "isFeatured":-1,
    "refreshes.refreshAt":-1,
    "publishDate":-1
}},
{"$skip":0},
{"$limit":10},
{"$project":{
    "position":1,"summary":1,"company":1,"publishDate":1,
    "expireDate":{"$dateToString":{"format":"%Y-%m-%d","date":"$expireDate"}},
    "locations":1,"minimumEducation":1,"workType":1,"skills":1,"contractType":1,
    "isExtensible":1,"salary":1,"gender":1,"yearsOfExperience":1,"canApplyOnline":1,"number":1,
    "isFeatured":1,"viewsCount":1,
    "status":{"$cond":{
        "if":{"$and":[
            {"$lt":["$publishDate", ISODate("2018-08-13T00:00:00.000Z")]},
            {"$gt":["$publishDate", ISODate("2018-08-11T00:00:00.000Z")]}]},"then":"New",
        "else":{"$cond":{
            "if":{"$lt":["$publishDate",ISODate("2018-08-12T00:00:00.000Z")]},"then":"Old","else":"Future"}}}},
            "companyDetails.profilePic":1,"companyDetails.businessUnits":1,"companyDetails.totalRatingAverage":1,
            "expiringDuration":{"$floor":{"$divide":[{"$subtract":["$expireDate",ISODate("2018-08-12T00:00:00.000Z")]},
            86400000]}},
            "companyDetails.totalReviews":{"$size":{"$ifNull":[{"$let":{"vars":{
                "companyDetailOne":{"$arrayElemAt":["$companyDetails",0]}},"in":"$$companyDetailOne.reviews"}},[]]}}}}

])

And if I comment skip and limit following is my result:

But following is my result with skip = 0, limit = 10:

Now compare above results with following for skip=10, limit=10: highlighted documents are duplicate in second page (skip=10, limit=10):

And the same thing existed in other pages, for other documents.

Answer 1

It looks like the three fields you're sorting by are not unique and therefore the order can be different in subsequent executions. To fix that you can add additional field to your $sort. Since _id is always unique it can be a good candidate. Try:

{"$sort":{
    "isFeatured":-1,
    "refreshes.refreshAt":-1,
    "publishDate":-1,
    "_id": -1
}}