MongoDB aggregation: Lookup an object of array child

Question

I have records in a collection of the following format.

STUDENT

[
    {
        "name" : "student A",
        "type" : 1,
        "results" : [ 
            {
                "position" : 1,
                "percent" : 90,
                "test_id" : ObjectId("aaaa")
            }, 
            {
                "position" : 2,
                "percent" : 88,
                "test_id" : ObjectId("bbbb")
            }
        ]
    },
    {
        "name" : "student B",
        "type" : 1,
        "results" : [
            {
                "position" : 2,
                "percent" : 56,
                "test_id" : ObjectId("bbbb")
            }
        ]
    }
]

TEST:

{
    "_id" : ObjectId("aaaa"),
    "name" : "Test A",
},
{
    "_id" : ObjectId("bbbb"),
    "name" : "Test B",
}

This is my required output, Condition: Test.name = "Test A"

[
    {
        "name" : "student A",
        "type" : 1,
        "results" : [ 
            {
                "position" : 1,
                "percent" : 90,
                "test" : {
                    "_id" : ObjectId("aaaa"),
                    "name" : "Test A",
                }
            }, 
            {
                "position" : 2,
                "percent" : 88,
                "test" : {
                    "_id" : ObjectId("bbbb"),
                    "name" : "Test B",
                }
            }
        ]
    }
]

I've tried various combinations of aggregate, unwind and project but still can't quite get there and would really appreciate any suggestions.

Answer 1

This pipeline should work for you:

[{
    $match: {
        name: "student A"
    }
}, {
    $unwind: {
        path: "$results"
    }
}, {
    $lookup: {
        from: 'TEST',
        localField: 'results.test_id',
        foreignField: '_id',
        as: 'results.test'
    }
}, {
    $group: {
        _id: "$name",
        name: {
            $first: "$name"
        },
        type: {
            $first: "$type"
        },
        results: {
            $push: "$results"
        }

    }
}]

Here are screenshots of your pipeline so you can see what is happening in each stage:

If you want to get rid of the extra fields, you can add a project stage.