Sorted result in mongoose-aggregate-paginate-v2 is changing

[asmafakhfakh]

I'm sending a query with postman to an apollo server to get a list of users.
This is my options object:

const options = {
            page: parseInt(page),
            limit: 15,
            lean: true,
            pagination: true,
            sort:{ lastUpdate: "descending", creationDateTime:"descending"}
        }

Sometimes when I resend the request with exactly the same parameters, the result changes even though my data is the same.
These are some samples of different results I got:
sample 1:

{
    "listOfUsers": {
        "list": [
            {
                "lastUpdate": "2020-03-27T06:00:47.689Z",
                "creationDateTime": "2019-10-09T11:20:13.004Z"
            },
            {
                "lastUpdate": "2020-03-27T05:54:00.105Z",
                "creationDateTime": "2020-01-06T12:54:16.699Z"
            },
            {
                "lastUpdate": "2020-03-19T18:36:29.163Z",
                "creationDateTime": "2019-10-21T11:16:30.428Z"
            },
            {
                "lastUpdate": "2020-03-19T17:46:36.799Z",
                "creationDateTime": "2020-01-06T09:43:41.844Z"
            },
            {
                "lastUpdate": null,
                "creationDateTime": "2020-01-06T10:55:47.714Z"
            }
        ],
        "totalDocs": 5,
        "limit": 15,
        "totalPages": 1,
        "page": 1,
        "pagingCounter": 1,
        "hasPrevPage": false,
        "hasNextPage": false,
        "prevPage": null,
        "nextPage": null
    }
}

sample 2:

{
    "listOfUsers": {
        "list": [
            {
                "lastUpdate": "2020-03-27T06:00:47.689Z",
                "creationDateTime": "2019-10-09T11:20:13.004Z"
            },
            {
                "lastUpdate": "2020-03-27T05:54:00.105Z",
                "creationDateTime": "2020-01-06T12:54:16.699Z"
            },
            {
                "lastUpdate": "2020-03-19T17:46:36.799Z",
                "creationDateTime": "2020-01-06T09:43:41.844Z"
            },
            {
                "lastUpdate": null,
                "creationDateTime": "2020-01-06T10:55:47.714Z"
            },
            {
                "lastUpdate": "2020-03-19T18:36:29.163Z",
                "creationDateTime": "2019-10-21T11:16:30.428Z"
            }
        ],
        "totalDocs": 5,
        "limit": 15,
        "totalPages": 1,
        "page": 1,
        "pagingCounter": 1,
        "hasPrevPage": false,
        "hasNextPage": false,
        "prevPage": null,
        "nextPage": null
    }
}

sample 3:

{
    "listOfUsers": {
        "list": [
            {
                "lastUpdate": "2020-03-19T18:36:29.163Z",
                "creationDateTime": "2019-10-21T11:16:30.428Z"
            },
            {
                "lastUpdate": "2020-03-27T06:00:47.689Z",
                "creationDateTime": "2019-10-09T11:20:13.004Z"
            },
            {
                "lastUpdate": "2020-03-19T17:46:36.799Z",
                "creationDateTime": "2020-01-06T09:43:41.844Z"
            },
            {
                "lastUpdate": "2020-03-27T05:54:00.105Z",
                "creationDateTime": "2020-01-06T12:54:16.699Z"
            },
            {
                "lastUpdate": null,
                "creationDateTime": "2020-01-06T10:55:47.714Z"
            }
        ],
        "totalDocs": 5,
        "limit": 15,
        "totalPages": 1,
        "page": 1,
        "pagingCounter": 1,
        "hasPrevPage": false,
        "hasNextPage": false,
        "prevPage": null,
        "nextPage": null
    }
}

[aravindnc]

@asmafakhfakh Can you share the query. Also try adding an index for creationDateTime field and try again.

[asmafakhfakh]

@aravindnc I tried indexing both fields i'm using for sort, doesn't resolve the problem.
So this is my query
PS: I tried passing directly -1 , "descending" and "desc" to sort parameter, and also a string like mentioned in documentation, still the problem persists

module.exports.listOfUsers = (root, { page, name, lastname, sort }) => {
    return new Promise((resolve, reject) => {

        const options = {
            page: parseInt(page),
            limit: 15,
            lean: true,
            pagination: true,
            sort: { lastUpdate: sort, creationDateTime: sort }
        }
        var aggUser = user.aggregate([
            {
                $lookup: {
                    from: "profiles",
                    localField: "profile",
                    foreignField: "_id",
                    as: "profile"
                }
            }, {
                $match: {
                    "profile.0.name":name,
                    "profile.0.lastname":lastname,
                }
            }, {
                $lookup: {
                    from: "books",
                    localField: "favoritebook",
                    foreignField: "_id",
                    as: "favoritebook"
                }
            }, {
                $lookup: {
                    from: "movies",
                    localField: "favoritemovie",
                    foreignField: "_id",
                    as: "favoritemovie"
                }
            }
        ]);
        user.aggregatePaginate(aggUser, options).then(
            async res => {
                if (res.totalDocs == 0) {
                    resolve({
                        list: res.docs,
                        totalDocs: res.totalDocs,
                        limit: res.limit,
                        totalPages: res.totalPages,
                        page: res.page,
                        pagingCounter: res.pagingCounter,
                        hasPrevPage: res.hasPrevPage,
                        hasNextPage: res.hasNextPage,
                        prevPage: res.prevPage,
                        nextPage: res.nextPage
                    })
                }
                let FinalTab = []
                await res.docs.map(ell => {
                    axios.get(
                        `${ENDPOINT}/${ell.profile.state}`
                    ).then(async ress => {
                        let Objec = ell
                        Objec.profile.state = ress.data.state
                        await FinalTab.push(Objec)
                        if (FinalTab.length == res.docs.length) {
                            await resolve({
                                list: FinalTab,
                                totalDocs: res.totalDocs,
                                limit: res.limit,
                                totalPages: res.totalPages,
                                page: res.page,
                                pagingCounter: res.pagingCounter,
                                hasPrevPage: res.hasPrevPage,
                                hasNextPage: res.hasNextPage,
                                prevPage: res.prevPage,
                                nextPage: res.nextPage
                            })
                        }
                        return ell
                    }).catch(err => reject(new Error(err)))
                })

            }
        ).catch(
            err => { return err }
        )
    }).catch((e) => { return e });
}

[aravindnc]

Can you try sort inside aggregate itself.

[asmafakhfakh]

@aravindnc I added a sort stage and the problem persists

{ 
   $sort : { lastUpdate : -1 } 
}

[aravindnc]

@asmafakhfakh If you don't mind, can you share your sample dateset with all required collections(minimal) to run the aggregate query, so I can test it at my end.

[asmafakhfakh]

Archive.zip
this is a cropped dataset of the two collections users and profiles, you need to drop the last two stages of lookup for favoritebook and favoritemovie, as well as the query for state at the end

[yacineBR]

has this problem been resolved ?

[czystyl]

I can also confirm that the order is changing even after adding indexes. Let me know how I can help.

[czystyl]

I fixed the problem by adding the _id field to sort statement. The problem can be reproduced even without this plugin using $skip and $limit on aggregate.

@aravindnc I think now issue could be closed.

[asmafakhfakh]

@czystyl I dont think that solves it because the problem is not in documents that has equivalent dates for the sorted field that changes order, I get a totally wrong order, you can see that in the examples I put above.
But I will try your solution anyway and give you feedback

[ChavaMS]

@aravindnc im having the same issue without sorting, i receive different responses. As you can se on the examples bellow, every array is a different request result, and they are all different:

[
  { _id: 5fa04d3480595823a06288cf },
  { _id: 5fc27783e415b54f64729eca },
  { _id: 5fa04dfb80595823a06288d1 },
  { _id: 5fc277bce415b54f64729ecb },
  { _id: 5fc27834e415b54f64729ecd }
]
[
  { _id: 5fc277f5e415b54f64729ecc },
  { _id: 5fc277bce415b54f64729ecb },
  { _id: 5fc27834e415b54f64729ecd },
  { _id: 5fa04dfb80595823a06288d1 },
  { _id: 5fc27867e415b54f64729ece }
]
[
  { _id: 5fc277bce415b54f64729ecb },
  { _id: 5fa04dfb80595823a06288d1 },
  { _id: 5fc27783e415b54f64729eca },
  { _id: 5fc27834e415b54f64729ecd },
  { _id: 5fa04d3480595823a06288cf }
]

This is the code of my method:

var myAggregate = UserProducts.aggregate([{ $group: { _id: "$user" } }]);

        const options = {
            page: page,
            limit: itemsPerPage
        };

        //Pagina lo anterior
        UserProducts.aggregatePaginate(myAggregate, options).then(async function (results) {

            console.log(results.docs);
            //retorna los productos por usuario y su usuario
            for (let i = 0; i < results.docs.length; i++) {
                await getUser(results.docs[i]).then((value) => {
                    userProductsArray[i] = value;

                    //console.log(userProductsArray);
                });
    
            }

            return res.status(200).send({
                userProductsArray,
                total: results.totalPages
            });

[KristinaHus]

Hi, is there any solution to this or a workaround? Cuz I'm having the same problem.

My aggregation with the $sort stage works just fine, but when I use aggregatePaginate I'm getting a different result.

I've tried to add a sort to options, still no luck.