CODEWITHSUNDEEP
mongodbsortingpaginationaggregation-framework
jones
jones

Reputation: 1453

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

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: no limit

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

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

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

Upvotes: 4

Views: 3059

Answers (1)

mickl
mickl

Reputation: 49985

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
}}

Upvotes: 16

Related Questions