mongodb - Find document with closest integer value

Question

Let's assume I have a collection with documents with a ratio attribute that is a floating point number.

{'ratio':1.437}

How do I write a query to find the single document with the closest value to a given integer without loading them all into memory using a driver and finding one with the smallest value of abs(x-ratio)?

Answer 1

Interesting problem. I don't know if you can do it in a single query, but you can do it in two:

var x = 1; // given integer
closestBelow = db.test.find({ratio: {$lte: x}}).sort({ratio: -1}).limit(1);
closestAbove = db.test.find({ratio: {$gt: x}}).sort({ratio: 1}).limit(1);

Then you just check which of the two docs has the ratio closest to the target integer.

MongoDB 3.2 Update

The 3.2 release adds support for the $abs absolute value aggregation operator which now allows this to be done in a single aggregate query:

var x = 1;
db.test.aggregate([
    // Project a diff field that's the absolute difference along with the original doc.
    {$project: {diff: {$abs: {$subtract: [x, '$ratio']}}, doc: '$$ROOT'}},
    // Order the docs by diff
    {$sort: {diff: 1}},
    // Take the first one
    {$limit: 1}
])