Retrieve n-level deep sub-document in MongoDB

Question

I have a deeply nested document in mongoDB and I would like to fetch individual sub-objects.

Example:

{
   "schoolName": "Cool School",
   "principal": "Joe Banks",
   "rooms": [
      {
         "number": 100
         "teacher": "Alvin Melvin"
         "students": [
            {
               "name": "Bort"
               "currentGrade": "A"
            },
            // ... many more students
         ]
      },
      // ... many more rooms
   ]
}

Recently Mongo updated to allow 1-level-deep sub-object retrieval using $elemMatch projection:

var projection = { _id: 0, rooms: { $elemMatch: { number: 100 } } };
db.schools.find({"schoolName": "Cool School"}, projection);
// returns { "rooms": [ /* array containing only the matching room */ ]  }

But when I try to fetch a student (2 levels deep) in this same fashion, I get an error:

var projection = { _id: 0, "rooms.students": { $elemMatch: { name: "Bort" } } };
db.schools.find({"schoolName": "Cool School"}, projection);
// "$err": "Cannot use $elemMatch projection on a nested field (currently unsupported).", "code": 16344

Is there a way to retrieve arbitrarily deep sub-objects in a mongoDB document?

I am using Mongo 2.2.1

Answer 1

I recently asked a similar question and can provide a suitably general answer (see Using MongoDB's positional operator $ in a deeply nested document query)

This solution is only supported for Mongo 2.6+, but from then you can use the aggregation framework's $redact function.

Here is an example query which should return just your student Bort.

db.users.aggregate({

    $match: { schoolName: 'Cool School' }

}, {

    $project: {

        _id: 0,
        'schoolName': 1,
        'rooms.number': 1,
        'rooms.students': 1

    }

}, {

    $redact: {
        $cond: {
            "if": {
                $or: [{
                    $gte: ['$schoolName', '']
                }, {
                    $eq: ['$number', 100]
                }]
            },
            "then": "$$DESCEND",
            "else": {
                $cond: {
                    "if": {
                        $eq: ['$name', 'Bort']
                    },
                    "then": "$$KEEP",
                    "else": "$$PRUNE"
                }
            }
        }
    }

});

$redact can be used to make sub-queries by matching or pruning sub-documents recursively in the matched documents.

You can read about $redact here to understand more about what's going on but the design pattern I've identified has the following requirements:

• The redact condition is applied at each sub-document level so you need a unique field at each level e.g. you can't have number as a key on both rooms and students say
• It only works on data fields not array indices so if you want to know the returned position of a nested document (for example to update it) you need to include that and maintain it in your documents
• Each part of the $or statement in $redact should match the documents you want at a specific level
• Therefore each part of the $or statement needs to include a match to the unique field of the document at that level. For example, $eq: ['$number', 100] matches the room with number 100
• If you aren't specifying a query at a level, you need to still include the unique field. For example, if it is a string you can match it with $gte: ['$uniqueField': '']
• The last document level goes in the second if expression so that all of that document is kept.