MongoDB collection as a lookup table with range of values

Question

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?

You can run `.find` with sort / limit explained [here](https://stackoverflow.com/questions/4299991/how-to-sort-in-mongoose) — Valijon, Mar 08 '20 at 17:44
I've posted sample data. The csv above is the data set. Each row is a particular grade score. It contains a grade letter, and the numChocolateBars necessary to achieve that grade score. neededChocolateForNextGrade is just extra data that shows how many more chocolate bars the next grade level would be at. — user2896438, Mar 08 '20 at 17:46

whoami - fakeFaceTrueSoul · Accepted Answer · 2020-03-08T18:59:49.537

1

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

edited Mar 08 '20 at 18:59

answered Mar 08 '20 at 17:50

whoami - fakeFaceTrueSoul

17,086
6
32
46

1

Just to add to the answer, neededChocForNextGrade is the delta off of the current grade to get to the next one, so the actual final query is an aggregate query: db.collection.find({ numChocolateBars: { $lte: 150 }, $expr: { $gt: [ { $sum: [ "$numChocolateBars", "$neededChocolateForNextGrade" ] }, 150 ] } }) Since your answer got me started in teh right direction and you introduced me to the beauty of mongoDB-Playground I'll mark it as right but please change the query to the correct one, thanks again – user2896438 Mar 08 '20 at 18:51
1

@user2896438 : Oh ok, I thought **neededChocolateForNextGrade** is a fixed one !! I see that varies upon **numChocolateBars** !! Anyhow that concept remains similar, will update it for future ref !! – whoami - fakeFaceTrueSoul Mar 08 '20 at 18:57

MongoDB collection as a lookup table with range of values

My current solution

1 Answers1