Multiple sorting in aggregate with mongo

Question

When I use a multiple sorting in aggregate method with mongo, results aren't sorting in the right way. This is my query :

db.MyCollection.aggregate(
{
    "$unwind": "$objects"
},
{
    "$lookup": {
        "from": "CollectionA",
        "localField": "objects.itemId",
        "foreignField": "_id",
        "as": "itemOne"
    }
},
{
    "$lookup": {
        "from": "CollectionB",
        "localField": "user_id",
        "foreignField": "id",
        "as": "users"
    }
},
{
    "$lookup": {
        "from": "CollectionC",
        "localField": "objects.itemName",
        "foreignField": "name",
        "as": "itemTwo"
    }
},
{
    "$addFields": {
        "item": {
            "$arrayElemAt": [
                "$itemOne",
                0
            ]
        },
        "user": {
            "$arrayElemAt": [
                "$users",
                0
            ]
        },
        "itemP": {
            "$arrayElemAt": [
                "$itemTwo",
                0
            ]
        }
    }
},
{
    "$addFields": {
        "itemName": {
            "$ifNull": [
                "$item.name",
                "$objects.itemName"
            ]
        },
        "userName": {
            "$concat": [
                "$user.firstname",
                " ",
                "$user.lastname"
            ]
        }
    }
},
{
    "$match": {
        "client_id": 2
    }
},
{
    "$skip": 1
},
{
    "$limit": 10
},
{
    "$project": {
       "date": "$objects.date",
       "state": "$objects.state"
    }
},
{
    "$sort": { 
        "objects.state": 1,
        "objects.date": 1,
    }
}

)

To precise: "date" field is Date type and "state" field is number type. If I use only one sort : result order is correct. But if I use 2 sorts, results are not order correctly. Have you got any ideas, why ?

What do you mean by "results are not ordered correctly" ? Please add the current result of your query, and the expected output — felix
– felix, Commented Nov 7, 2017 at 10:17
They don't sort correctly because you renamed the fields in $project. So it should be { $sort: { state: 1, date: 1 } }. Not really sure that you realize that only those fields are being returned though. It frankly does not make any sense to have that $project after all the $addFields stages to "add new fields" and then just discard them. So you do seem to be missing a few concepts here. — Neil Lunn
– Neil Lunn, Commented Nov 7, 2017 at 10:20
@NeilLunn Perfect ! I forgot the rename field with $project... Thanks you ! — Kael
– Kael, Commented Nov 7, 2017 at 10:33

Kael · Accepted Answer · 2017-11-07 10:35:26Z

1

As @Neil Lunn says : They don't sort correctly because you renamed the fields in $project. So it should be { $sort: { state: 1, date: 1 } }

answered Nov 7, 2017 at 10:35

Kael

93810 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Multiple sorting in aggregate with mongo

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related