MongoDB - How to get the documents based on two column values-CodePudding

My MongoDB collection with sample data is attached with the screenshot below

I want to fetch rows by passing grade value and the resulting records should be returned as follows

case 1: when grade = 12, Row 1,3,6 and 7 should be returned ie given grade 12 should compare with gradeFrom and gradeTo
case 2: when grade = 1, Row 5 should returned

As a summary of above cases, the given grade should be greater than or equal to gradeFrom AND less than or equal to gradeTo.

The mongoose query that I used is given below, but the data returned is empty in my code

let andCondition = []; 
let grade = 12;
andCondition.push({ gradeFrom: {$gte: grade}});
andCondition.push({ gradeTo: {$lte: grade}});
let data = await Course.find({$and :andCondition});

console.log(andCondition) gives the below object

[ 
    { gradeFrom: { '$gte': 12 } }, 
    { gradeTo: { '$lte': 12 } } 
]

Please help me to get this done.

CodePudding user response：

You have reversed the range logic.

In Math, it should be:

gradeFrom <= grade <= gradeTo

While in MongoDB query:

{ gradeFrom: { $lte: grade } }
{ gradeTo: { $gte: grade } }