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:
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}}
]
})
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}
}
}
})
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 ?
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 }} ]
}
}
}
})
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
}
}
]
}
}
}
})