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?

Neil Lunn · Accepted Answer · 2015-01-13 04:16:25Z

42

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.

edited Jan 13, 2015 at 4:16

answered Jan 13, 2015 at 4:11

Neil Lunn

151k36 gold badges356 silver badges327 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Moppo Over a year ago

Very useful and complete answer, sorry to not being able to upvote twice. Thanks Neil !

Collectives™ on Stack Overflow

Mongo Query On Multiple Fields Of Sub-Document

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related