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.

The data you have provided is mis matching the requirements. Could you correct your required and input data. I suspect you need Test.name = "student A" — Sohan
– Sohan, Commented Feb 7, 2020 at 12:56

Lauren Schaefer · Accepted Answer · 2020-02-07 12:59:40Z

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.

Collectives™ on Stack Overflow

MongoDB aggregation: Lookup an object of array child

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related