Using elemMatch in a MongoDB Query

I introduced a latent bug in some code recently while trying to find documents with a specific set of values embedded inside an array. For example, consider the following documents:

{
    "name" : "Thing1",
    "children" : [  
        { "name" : "C1", "value" : "A" },
        { "name" : "C2", "value" : "B" },
        { "name" : "C3", "value" : "C" }]
}, {
    "name" : "Thing2",
    "children" : [
        { "name" : "C3", "value" : "A" },
        { "name" : "C4", "value" : "B" },
        { "name" : "C5", "value" : "C" }]
}, {
    "name": "Thing3",
    "children": [
        { "name": "C5", "value": "A" },
        { "name": "C6", "value": "B" },
        { "name": "C7", "value": "C" }]
}

Let’s say we only want documents with a child entry that has a name of C3 AND a value of C (only Thing1 should match this criteria because of the last child in its array). It’s tempting to write a query with an AND clause and feel good about the C# code.

var query = things.Find(
    Query.And(
        Query.EQ("children.name", "C3"),
        Query.EQ("children.value", "C")
    ));

But MongoDB, being document oriented, interprets the query as “give me all the documents that have any child with a name of C3, and any child with a value of C. In other words, the name and value combination don’t need to match inside the same array element. A quick test from the shell can verify the behavior.

> db.things.find({"children.name":"C3", "children.value":"C"},{name:1, _id:0})
{ "Name" : "Thing1" }
{ "Name" : "Thing2" }

The $elemMatch query operator tells Mongo to only match documents when at least one element in the array satisfies the entire criteria.

var query = things.Find(
    Query.ElemMatch("children",
        Query.And(
            Query.EQ("name", "C3"), 
            Query.EQ("value", "C")
        )
     ));

The shell will verify that we only get the Thing1 we want:

> db.things.find({ children: { $elemMatch : { name:"C3", value:"C"}}}, {name:1, _id:0})
{ "Name" : "Thing1" }

Not an easy bug to catch in a test without a robust set of test data.