MongoDB: Find documents by range filter performed on array of nested documents

user_15059055 0
Viewed 505

Consider a collection of documents that collects scores as an array of nested documents, like this (I'm using MongoDb 4.2):

{
    "scores" : [
      { "value" : 2 }, 
      { "value" : 3 },
      { "value" : 4 }
    ]
}, {
    "scores" : [
      { "value" : 8  }, 
      { "value" : 9  },
      { "value" : 10 }
    ]
}, {
    "scores" : [
      { "value" : 7  }, 
      { "value" : 7 },
      { "value" : 10 }
    ]
}

Consider now the following use case: "Give me all the documents that contains ONLY scores in the range [X, Y]". Said differently, please filter out the documents that have at least one value that is NOT in the range [X-Y]. Side note: in the actual data, the scores are real numbers, so doing some $in queries won't help here.

Let's consider three ranges:

  • Range [8, 10] (one documents should match)
  • Range [0, 1] (0 document matching).
  • Range [5, 6] (0 document matching).

As a non-expert yet in mongo, my first try was to do this:

db.test.find({
  $and: [ 
    { "scores.value": {$gte: 8}}, 
    { "scores.value": {$lte: 10}} 
  ]
})
  • Case [8, 10]: 2 records.
  • Case [0, 1] : 0 records
  • Case [5, 6] : 2 records

So there are some odd stuff here: first results suggests that my query actually returns scores with either of the condition satisfied (somewhat an $or instead of $and). But is that were true, I would have retrieved all records on the second case [0, 1] instead of 0. Obviously something I'm using the wrong way here, but if someone can explain me why, I'd be very happy.

Next query:

db.test.find({
  "scores": {
    $elemMatch: { 
      value: {$gte: 6.5, $lte: 6.8} 
    }
  }
})
  • Case [8, 10]: 2 records.
  • Case [0, 1] : 0 records
  • Case [5, 6] : 0 records

For that one, everything is fine and works as documented / expected, but it does not answer my original use case.

So beside understanding what happened using the first query, does anyone knows a way to achieve what I'm trying to do ?

mongodb filter arrays
2 Answers

Taking [8-10] example, as you mentioned you can write a query to find all "bad" documents where at least on score is not in [8-10] range: That's the purpose of elementMatch.

"bad" document is ( score < 8 OR score > 10 ):

db.collection.find({
  "scores": {
    $elemMatch: {
      $or: [ { value: { $lt: 8 }}, { value: { $gt: 10 }} ]
    }
  }
})

Then you can invert the condition with a $not to get the result you expected (reject "bad" documents):

db.collection.find({
  "scores": { 
    $not: {
      $elemMatch: {
        $or: [ { value: { $lt: 8 }}, { value: { $gt: 10 }} ]
      }
    }
  }
})

MongoDB playground

user_22352860

Let's start by understand what went wrong in your first attempt.

You were using Mongo's dot notation, what does this do?

To specify or access an element of an array by the zero-based index position

Basically it evaluates all the arrays elements at once.

 $and: [ 
    { "scores.value": {$gte: 8}}, 
    { "scores.value": {$lte: 10}} 
  ]

Each of these expressions queries the entire array, meaning to satisfy it query all you need is a single value that's within the range.

What you can do is utilise the $not operator and combined with $elemMatch look for elements to exclude, like so:

db.collection.find({
  "scores": {
    $not: {
      $elemMatch: {
        $or: [
          {
            value: {
              $lt: 8
            }
          },
          {
            value: {
              $gt: 10
            }
          }
        ]
      }
    }
  }
})

Mongo Playground

user_107703700
Related