How might I query MongoDB for all documents where at least one value in an array falls inside a range, e.g. 8 to 10.

If I query it as an enumeration, this works as desired: {x : {$in: [8, 9, 10]}} . This will return all records where any value in x is in the set [8,9,10]. In the example below it returns the first record but not the second.

But in my application, I can't enumerate the set as the range could be potentially very large and not necessarily even integers.

My first guess was { x: {$gte: 8, $lte: 10}} but this fails, as a document might have at least one value above the lower bound, and at least one value below the upper bound, and would thus pass even though no value is both above the lower and below the upper. In the example below, both records are returned, even though record 2 has no values in 8–10:

[
  {
    "key": 1,
    "x": [ 5,9,10 ]
  },
  {
    "key": 2,
    "x": [ 2, 3, 7, 999 ]
  }
]

Example on MongoPlayground: https://mongoplayground.net/p/2ZLfTcGnW4g

how to find values between a range in mongodb

CodePudding user response：

Best suitable for this task seems to be the $elemMatch operator:

db.collection.find({
 a: {
  $elemMatch: {
    $gte: 12,
    $lte: 17
  }
 }
})

Explained: It will match all documents where at least one value from the "a" array elements will match the range conditions at same time.

playground

CodePudding user response：

You can use an aggregation pipeline to $filter only matching items as a condition to match the document:

db.collection.aggregate([
  {
    $addFields: {
      valid: {
        $size: {
          $filter: {
            input: "$x",
            as: "item",
            cond: {$and: [{$gte: ["$$item", rangeMin]}, {$lte: ["$$item", rangeMax]}]}
          }
        }
      }
    }
  },
  {
    $match: {valid: {$gt: 0}}
  },
  {
    $unset: "valid"
  }
])

Playground