MongoDB $and query on multiple fields and values within a single embedded document

Question

I'm looking for a way to query a collection like this:

[{ 
  name: "Parent 1",
  items: [
    { name: "Child 1", age: "60" },
    { name: "Child at heart", age: "27" }
  ]
 },

 {
  name: "Parent 2",
  items: [
    { name: "Child 3", age: "10" },
    { name: "Child 4", age: "15" },
    { name: "Child at heart", age: "60" }
  ]
 }
]

With a query like this:

db.collection.find( 'items.name' => "Child at heart", 'items.age' => "60" )

And get a result of just the object with name "Parent 2". That is, I want to query the documents looking for the ones that contain an embedded item that has both a name of "Child at heart", and an age of "60" in the same embedded document. Instead this query returns both documents, "Parent 1" and "Parent 2".

How do I query with an $and condition on different fields within the same embedded document?

Thanks.

Answer 1

The problem here is that $and takes each condition, applies it to all embedded documents, and counts the document as meeting the condition if any embedded document is a match. It doesn't correlate the conditions.

The operator you want is $elemMatch. It performs the correlation you're looking for. Here's the right query:

db.collection.find({items: {$elemMatch: {name: 'Child at heart', age: '60'} }})

items is the name of the array you want to search.

The output of the query on your sample data:

{
"_id" : ObjectId("51a012f0ac3dfe4f0c05ca89"),
"name" : "Parent 2",
"items" : [
    {
        "name" : "Child 3",
        "age" : "10"
    },
    {
        "name" : "Child 4",
        "age" : "15"
    },
    {
        "name" : "Child at heart",
        "age" : "60"
    }
]
}