CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Pratik Kalathiya
Pratik Kalathiya

Reputation: 186

How to perform a value based Order By in MongoDB?

select * from users ORDER BY FIELD(status, 'A', 'B', 'C', 'D') ASC;

This will sort all the users according to their statuses such that all the users with status 'A' will come first then 'B' and so on. What would be an equivalent in MongoDB?

Upvotes: 6

Views: 524

Answers (2)

Blakes Seven
Blakes Seven

Reputation: 50406

You need to $project a "weight" for each value in order in MongoDB terms, and that means the .aggregate() method:

db.users.aggregate([
    { "$project": {
        "status": 1,
        "a_field": 1,
        "another_field": 1,
        "pretty_much_every_field": 1,
        "weight": {
            "$cond": [
                { "$eq": [ "$status", "A" ] },
                10,
                { "$cond": [ 
                    { "$eq": [ "$status", "B" ] },
                    8,
                    { "$cond": [
                        { "$eq": [ "$status", "C" ] },
                        6,
                        { "$cond": [
                            { "$eq": [ "$status", "D" ] },
                            4,
                            0
                        ]}
                    ]}
                ]}
            ] 
        }
    }},
    { "$sort": { "weight": -1 } }
])

The nested use of the ternary $cond allows each item for "status" to be considered as an ordered "weight" value in the order of the arguments given.

This in turn is fed to $sort, where the projected value ( "weight" ) is used to sort the results as scored by the weighted matching.

So in this way the preference is given to the order of "status" matches as to which appears first in the sorted results.

Upvotes: 5

Max Pro
Max Pro

Reputation: 58

to sort ASC -> status: 1

db.users.find().sort( { status: 1 } )

to sort DESC -> status: -1

db.users.find().sort( { status: -1 } )

Upvotes: 0

Related Questions