Mar 27, 2016

MongoDB Query: $elemMatch vs. Dot Notation

In MongoDB query, Dot notation is to match by specific fields in an embedded document. When the field holds an array of embedded documents, then to match the field, concatenate the name of the field that contains the array, with a dot (.) and the name of the field in the embedded document.


{ <array field name>.<embedded document field> : <value> }

To specify multiple criteria on an array of embedded documents such that AT LEAST ONE embedded document satisfies all the specified criteria, $elemMatch operator is used.


{ <array field name>: { $elemMatch: { <query1>, <query2>, ... } } }

Let us take an example to understand it. Consider schoolinfo collection includes following documents


db.schoolinfo.insert([{
    "_id" : 1,
    "name" : "Class 1",
    "students" : [  
        { "rollNo" : 10001, "name" : "Ram", "score" : 65, "section" : "A" },
        { "rollNo" : 10002, "name" : "Shyam", "score" : 90, "section" : "B" },
        { "rollNo" : 10003, "name" : "Mohan", "score" : 75, "section" : "C" }       
        ]
},
{
    "_id" : 2,
    "name" : "Class 2",
    "students" : [  
        { "rollNo" : 20001, "name" : "Krishna", "score" : 88, "section" : "B" },
        { "rollNo" : 20002, "name" : "Sohan", "score" : 91, "section" : "A" },
        { "rollNo" : 20003, "name" : "Radhika", "score" : 82, "section" : "A" },
        { "rollNo" : 20004, "name" : "Komal", "score" : 55, "section" : "C" }        
        ]
},
{
    "_id" : 3,
    "name" : "Class 3",
    "students" : [  
        { "rollNo" : 30001, "name" : "Monika", "score" : 77, "section" : "B" },      
        { "rollNo" : 30002, "name" : "Rahul", "score" : 81, "section" : "A" }       
        ]
}])

Note: MongoDB 3.2 is used for this post.

1. To get list of class which has ANY student score >85.


db.schoolinfo.find ({'students.score': { $gt : 85}},{_id:0, 'name':1})

Output:


{ "name" : "Class 1" }
{ "name" : "Class 2" }

2. To get list of class which has ANY student of Section A.


db.schoolinfo.find ({'students.section': 'A'},{_id:0, 'name':1})

Output:


{ "name" : "Class 1" }
{ "name" : "Class 2" }
{ "name" : "Class 3" }

3. Now let's combine the conditions, To get list of class which has ANY student score >85 and section A. The expected output is Class 2 only.

If you use dot notation:


db.schoolinfo.find ({'students.score': { $gt : 85}, 'students.section': 'A'},
{_id:0, 'name':1})

Output:


{ "name" : "Class 1" }
{ "name" : "Class 2" }

Basically, it checks if the document has any student who scored > 85 and any student belongs to section A. It doesn't check that both conditions must be applied on same student.

Let's use $elemMatch to match documents with at least one element that matches both conditions.


db.schoolinfo.find ({'students': { $elemMatch:{ 'score' :{$gt : 85}, 'section': 'A'}}},
{_id:0, 'name':1})

Output:


{ "name" : "Class 2" }

Note: you do not need to use $elemMatch to specify only a single query condition.

As projection operator:

Now we will see difference between $elemMatch and Dot notation as projection operator.

Consider following query which uses dot notation:


db.schoolinfo.find ({'students.score': { $gt : 85}},
{_id:0,'name':1,'students.name':1, 'students.score' : 1}).pretty()

Output:


{
        "name" : "Class 1",
        "students" : [
                {
                        "name" : "Ram",
                        "score" : 65
                },
                {
                        "name" : "Shyam",
                        "score" : 90
                },
                {
                        "name" : "Mohan",
                        "score" : 75
                }
        ]
}
{
        "name" : "Class 2",
        "students" : [
                {
                        "name" : "Krishna",
                        "score" : 88
                },
                {
                        "name" : "Sohan",
                        "score" : 91
                },
                {
                        "name" : "Radhika",
                        "score" : 82
                },
                {
                        "name" : "Komal",
                        "score" : 55
                }
        ]
}

On first look, you might be confusing to see all embedded objects with selected fields even filtered with any score > 85. But it is okay because It filters classes with ANY score > 85, not students. If you want to select child objects with filter condition (show child object with score > 85 only), read following post:

MongoDB Query: Select and Filter Child Array

Consider following query with $elemMatch projection operator


db.schoolinfo.find ({},
{_id:0,'name':1,'students':{$elemMatch:{ 'score' :{$gt : 85}}}}).pretty()

Output:


{
        "name" : "Class 1",
        "students" : [
                {
                        "rollNo" : 10002,
                        "name" : "Shyam",
                        "score" : 90,
                        "section" : "B"
                }
        ]
}
{
        "name" : "Class 2",
        "students" : [
                {
                        "rollNo" : 20001,
                        "name" : "Krishna",
                        "score" : 88,
                        "section" : "B"
                }
        ]
}
{ "name" : "Class 3" }

It shows first matched document only if exists.

Conclusion:

In this post, we have seen $elemMatch vs dot notation as query operators and as projection operators. In query, dot notation allows for any nested elements to have an values and $elemMatch requires the same nested elements to have the values. In projection, dot notation shows all records of selected field and $elemMatch shows only first matched record.

Enjoy MongoDB !!