A MongoDB find() that matches when all $and conditions match the same sub-document?
- by MichaelOryl
If I have a set of MongoDB documents like the following, what can I do to get a find() result that only returns the families who have 2 pets who all like liver?
Here is what I expected to work:
db.delegation.find({pets:2, $and: [{'foods.liver': true}, {'foods.allLike': true}] })
Here is the document collection:
{
"_id" : ObjectId("5384888e380efca06276cf5e"),
"family": "smiths",
"pets": 2,
"foods" : [
{
"name" : "chicken",
"allLike" : true,
},
{
"name" : "liver",
"allLike" : false,
}
]
},
{
"_id" : ObjectId("4384888e380efca06276cf50"),
"family": "jones",
"pets": 2,
"foods" : [
{
"name" : "chicken",
"allLike" : true,
},
{
"name" : "liver",
"allLike" : true,
}
]
}
What I end up getting is both families because they both have at least one food marked as true for allLike. It seems that the two conditions in the $and are true if any foods sub-document matches, but what I want is the two conditions to match for the conditions as a pair.
As is, I get the Jones family back (as I want) but also Smith (which I don't). Smith gets returned because the chicken sub-doc has allLike set to true and the liver sub-doc has a name of 'liver'. The conditions are matching across separate foods sub-docs. I want them to match as a pair on a foods document.
This code is not the real use case, obviously. I have one, but I've simplified it to protect the innocent...
