How to sort a MongoDB query for a ratio of two fields in every document?

Question

Suppose I have a lot of documents like {'a' : x , 'b' : y}. Suppose x and y are integers. How can I do something like find().sort({'a'/'b'}) ?

Answer 1

You can add third field, result of a/b and sort by it.

You document will looks like:

{'a' : x , 'b' : y, c : z} // z = x/y

And you will sort by 'c':

find().sort({c : 1})

Answer 2

I don't believe this is possible, as you also can't run queries that compare 2 fields (without using $where to specify a javascript function which would be slow). Instead, I suspect you need to also store the ratio separately within the document and then sort on that new field.

Answer 3

Just like Bugai13 said, you need a need a third property in your collection in order to perform the sort. You can add the ratio property with a call to mapReduce (as follows), but this won't be terribly fast on large collections - and will lock up your database while it is running. You really should manually keep the ratio property up to date - it should't be very hard.

db.data.insert({a: 1, b: 1});
db.data.insert({a: 2, b: 2});
db.data.insert({a: 3, b: 3});
db.data.insert({a: 1, b: 4});
db.data.insert({a: 2, b: 1});
db.data.insert({a: 3, b: 2});
db.data.insert({a: 1, b: 3});
db.data.insert({a: 2, b: 4});
db.data.insert({a: 3, b: 1});
db.data.insert({a: 1, b: 2});
db.data.insert({a: 2, b: 3});
db.data.insert({a: 3, b: 4});

db.data.mapReduce(
    function(){
        emit(this._id, this);
    },
    function(k, vs){
        v = vs[0];
        v.c = v.a / v.b;
        return v;
    },
    {out : 'data'}
);

db.data.find().sort({c:1});

Answer 4

db.collection.aggregate([
    { $addFields: { newField: { $divide: [ "$a", "$b" ] } } },  // Prefix fields with '$'
    { $sort: { newField: -1 } }
]);

Answer 5

Since this question was asked in 2011, MongoDB has released the aggregation framework. It lets you sort by the combinations of fields, and you don't need to store (denormalize) any extra fields. Here's how it's done:

db.collection.aggregate([
{
    $match: {
        // Optional criteria to select only some documents to process, such as...
        deleted: null
    }
},
{
    $project: {
        // Need to prefix fields with '$'
        ratio: { $divide: [ "$a", "$b" ] },
    }
},
{
    $sort: { ratio: -1 },
}
]);

That's it. We use the $divide operator of the aggregation framework.