How to execute an order based on cost in MongoDB?

Question

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

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

Answer 1

You need a $project "weight" for each value in terms of MongoDB, which means .aggregate() :

 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 ternary $cond allows each status element to be considered 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 by weighted match.

Thus, preference is given to the match order “status”, which appears first in the sorted results.

Answer 2

to sort ASC → status: 1

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

to sort DESC → status: -1

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