How to perform a value based Order By in MongoDB?

Question

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?

Answer 1

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.