Filter sub documents by sub-sub document

Question

Imagine, you have the following mongoose schema:

mongoose.model('Team', mongoose.Schema(
{
 players : [{ 
    trikots : [{
        isNew : Boolean,
        color : String
    }]
 }]
})

I want to query my data to get all teams, that match the following

• have trikots {isNew : true, color : red}
• only select players, that have trikots {isNew : true, color : red}

I started by using $elemMatch for the sub-subdocument, but their still false players. Do I have to use aggregate()? And how?

Answer 1

Yes, that's quite achievable with aggregation framework. Your aggregation pipeline will consist of a $match operator which becomes the initial stage. This filters the documents in the collection by the specified criteria. The next pipeline steps will be a couple of $uniwnd operators on both arrays, players and the nested players.trikots. After the $uniwnd , you will need another $match to then filter the deconstructed array documents down to the required criteria which becomes your final solution.

Let's demonstrate this by inserting a couple of documents with the above schema to the team collection in mongo shell:

db.team.insert([
{
    "players" : [ 
        {
            "trikots" : [ 
                {
                    "isNew" : true,
                    "color" : "red"
                }, 
                {
                    "isNew" : true,
                    "color" : "blue"
                }
            ]
        }, 
        {
            "trikots" : [ 
                {
                    "isNew" : false,
                    "color" : "red"
                }, 
                {
                    "isNew" : true,
                    "color" : "green"
                }
            ]
        }
    ]
},
{
    "players" : [ 
        {
            "trikots" : [ 
                {
                    "isNew" : false,
                    "color" : "red"
                }, 
                {
                    "isNew" : false,
                    "color" : "blue"
                }
            ]
        }
    ]
}
])

The above aggregation pipeline can then be implemented as follows:

var pipeline = [
    {
        "$match": {
            "players.trikots.isNew": true,
            "players.trikots.color": "red"
        }
    },
    {
        "$unwind": "$players"
    },
    {
        "$unwind": "$players.trikots"
    },
    {
        "$match": {
            "players.trikots.isNew": true,
            "players.trikots.color": "red"
        }
    }
];
db.team.aggregate(pipeline);

Output:

/* 1 */
{
    "result" : [ 
        {
            "_id" : ObjectId("554bce9a2ba32ccf7f139bae"),
            "players" : {
                "trikots" : {
                    "isNew" : true,
                    "color" : "red"
                }
            }
        }
    ],
    "ok" : 1
}

Your mongoose aggregation would be similar:

Team.aggregate(pipeline).exec(callback);

Or using Mongoose aggregation pipeline builder for a fluent call:

Team.aggregate()
    .match({"players.trikots.isNew": true,"players.trikots.color": "red"})
    .unwind("players")
    .unwind("players.trikots")
    .match({"players.trikots.isNew": true,"players.trikots.color": "red"}) 
    .exec(callback);