CODEWITHSUNDEEP
mongodbmongodb-query
Guy
Guy

Reputation: 67290

MongoDB Query lower/upper ranges with an array as input

In MongoDB I have a collection that looks like:

{ low: 1, high: 5 },
{ low: 6, high: 15 },
{ low: 16, high 412 },
...

I have input that's an array of integers:

[ 4, 16, ...]

I want to find all the documents in the collection which have values included in the range depicted by low and high. In this example it would pick the first and third documents.

I've found lots of Q&A here on how to filter using a single value as the input but could not find one that included an array as input. It could be that my search failed me and that this has been answered.

Update: I should have mentioned that I'm constructing this query in an application and not running this in the CLI. Given that flexibility what if I create a $or query with each of the inputs? Something like:

$or: [{
  high: { $gte: 4 },
  low: { $lte: 4 },
}, {
  high: { $gte: 16 },
  low: { $lte: 16 },
},
...
]

It could be massive and have thousands of elements in the $or.

Upvotes: 1

Views: 111

Answers (2)

Mallik
Mallik

Reputation: 334

//working code from Mongo Shell CLI 4.2.6 on windows 10
//you can use forEach and loop through for comparison if a value exists between two numbers
> print("MongoDB",db.version());
MongoDB 4.2.6
> db.lhColl.find();
{ "_id" : ObjectId("5f889258f3b30cd04c8a78e5"), "low" : 1, "high" : 5 }
{ "_id" : ObjectId("5f889258f3b30cd04c8a78e6"), "low" : 6, "high" : 15 }
{ "_id" : ObjectId("5f889258f3b30cd04c8a78e7"), "low" : 16, "high" : 412 }
> var arrayInput = [4,16,500];
> var inputLength = arrayInput.length;
> db.lhColl.aggregate([
... {$match:{}}
... ]).forEach(function(doc){
...     for (i=0; i<inputLength; i++){
...      if (arrayInput[i]>=doc.low){
...         if(arrayInput[i] <= doc.high)
...           print("arrayInputs value match:",arrayInput[i]);
...     }
... }
... });
arrayInputs value match: 4
arrayInputs value match: 16

Upvotes: 1

mickl
mickl

Reputation: 49945

You can use $anyElementTrue along with $map to check if any value is included within a range defined in your documents:

db.collection.find({
    $expr: {
        $anyElementTrue: {
            $map: {
                input: [ 4, 16 ],
                in: {
                    $and: [
                        { $gte: [ "$$this", "$low" ] },
                        { $lte: [ "$$this", "$high" ] },
                    ]
                }
            }
        }
    }
})

Mongo Playground

Upvotes: 1

Related Questions