filtering a multi-level array collection in mongodb

Question

In mongoDB, how do I write a query that filters on postcode, student name, and subjId from this multilevel array? I am using mongoskin with ExpressJS. Here is the data structure

[{
    "_id": "1",
    "postcode": "SE41TB",
    "students": [{
    "name": "ajax",
    "school": "100",
    "age": "7",
    "subjects": [{
        "subjId": "1",
        "subjName": "Maths"
    }, {
        "subjId": "2",
        "subjName": "English"
    }]
    }, {
    "name": "backbone",
    "school": "100",
    "age": "8",
    "subjects": [{
        "subjId": "1",
        "subjName": "Maths"
    }, {
        "subjId": "2",
        "subjName": "English"
    }]
    }]
}, {
    "_id": "2",
    "postcode": "SEI3BT",
    "students": [{
    "name": "jquery",
    "school": "100",
    "age": "7",
    "subjects": [{
        "subjId": "1",
        "subjName": "Maths"
    }, {
        "subjId": "2",
        "subjName": "English"
    }]
    }, {
    "name": "angular",
    "school": "100",
    "age": "8",
    "subjects": [{
        "subjId": "1",
        "subjName": "Maths"
    }, {
        "subjId": "2",
        "subjName": "English"
    }]
    }]
}]

Here is what I have thus far that works down to the student name, but I can't figure out how to add the subjId filter -

    db.collection('test').find({
    'postcode': postcode
}, {
    'students': {
    $elemMatch: {
        'name': name
    }
    }
});

Thanks in advance.

Answer 1

You can use aggregation to get expected result.

It is recommended to avoid complex nested document structure.

You can try out following aggregation query:

db.collection.aggregate({
    "$match": {
    "postcode": "SE41TB"
    }
}, {
    "$unwind": "$students"
}, {
    "$match": {
    "students.name": "ajax"
    }
}, {
    "$unwind": "$students.subjects"
}, {
    "$match": {
    "students.subjects.subjId": "1"
    }
}, {
    "$group": {
    "_id": {
        "postcode": "$postcode"
    },
    "name": {
        "$first": "$students.name"
    },
    "school": {
        "$first": "$students.school"
    },
    "age": {
        "$first": "$students.age"
    },
    "subjects": {
        "$push": "$students.subjects"
    }
    }
}, {
    "$group": {
    "_id": "$name",
    "students": {
        "$push": {
            "name": "$name",
            "school": "$school",
            "age": "$age",
            "subjects": "$subjects"
        }
    }
    }
})