Q:

MongoDB collection as a lookup table with range of values

I have an interesting scenario that I can't find a clean solution for.
Let's suppose I have a chocolate factory that gets a Grade[A-Z] score based on how many chocolate bars it produces. The Grades to numChocolateBars mapping is stored in a collection as follows:
Grade, numChocolateBars, neededChocolateForNextGrade
Z, 0, 100
Y, 100, 150
X, 250, 1100
...
B, 11805606, 1228825
A, 13034431,

Examples:

99 numChocolateBars would receive a grade of Z.
100 numChocolateBars would receive a grade of Y.
150 numChocolateBars would receive a grade of Y.
99999999999999999 numChocolateBars would receive a grade of A.

Using a Mongoose or MongoDB query, what's the best way to find the appropriate grade for a given numChocolateBars?
My current solution
I've managed to get things working using:
GradeMap.findOne(
  {
    numChocolateBars: { $gt: thisMonthsNumChocolate}
  }
).Grade-1;

Which essentially finds the first record with numChocolateBars > thisMonthsNumChocolate (one grade higher than I want) and subtracts one from it. This works as long as I have a final dummy record with numChocolteBars higher than any possible value.But this seems very unstable, and it relies on the order in which Mongo searches through the data. I'm worrie this order might change based on indexing or other factors. Is there a better way?

A:

You can try below query :
db.collection.find({
  numChocolateBars: {
    $lte: thisMonthsNumChocolate
  },
  neededChocolateForNextGrade: {
    $gt: thisMonthsNumChocolate
  }
})

Test : MongoDB-Playground
Edit :
As neededChocolateForNextGrade is not a fixed value & if it depends on numChocolateBars :
db.collection.find({
  numChocolateBars: {
    $lte: 149
  },
  $expr: {
    $gt: [
      {
        $sum: [
          "$numChocolateBars",
          "$neededChocolateForNextGrade"
        ]
      },
      149
    ]
  }
})

Test : MongoDB-Playground

