Mongo Query On Multiple Fields Of Sub-Document

Question

Suppose I have a collection like this:

{ "arr" : [ { "name" : "a", "num" : 1 }, { "name" : "a", "num" : 2 } ] },
{ "arr" : [ { "name" : "b", "num" : 1 }, { "name" : "a", "num" : 2 } ] },
{ "arr" : [ { "name" : "b", "num" : 1 }, { "name" : "b", "num" : 2 } ] }

and I want to find all documents who's arr contains a sub-document with a name = "b" and num = 2.

If I do a query like this:

db.collection.find({
    $and: [
        { "arr.name": "b" },
        { "arr.num": 2 }
    ]
});

it will return all documents in the collection because they each contain a sub-document with either a name of "b" or a num of 2.

I've also tried this:

db.collection.find({
    arr: [
        { "name": "b", "num": 2 }
    ]
});

which doesn't throw any errors, yet doesn't return any results.

How do you query on multiple sub-document fields in MongoDB?

Answer 1

This is actually what the $elemMatch operator is for even though it is often misused. It essentially performs the query conditions on each element "within" the array. All MongoDB arguments are an "and" operation unless explicitly called otherwise:

db.collection.find({ "arr": { "$elemMatch": { "name": "b", "num": 2  } } })

You probably also want to "project" here as well if you are expecting only the matched field and not that whole document:

db.collection.find(
    { "arr": { "$elemMatch": { "name": "b", "num": 2  } } },
    { "arr.$": 1 }
)

Finally to explain why your second attempt does not work, this query:

db.collection.find({
    "arr": [
        { "name": "b", "num": 2 }
    ]
})

Does not match anything because there is no actual document where "arr" contains a singular element exactly matching your conditions.

Your first example failed..:

db.collection.find({
    $and: [
        { "arr.name": "b" },
        { "arr.num": 2 }
    ]
});

Because there are several array elements that satisfy the conditions and this is not just considered that both conditions apply to the same element. That is what $elemMatch adds, and when you need more that one condition to match, then this is where you use it.