Mongodb aggrregate $lookup of a field inside array

Question

I have a database with 3 collections. The first one has documents with some references to the other ones.

I need a query to populate the references on projects so requirements are loaded. I've managed to $lookup the requirements array, but I can't get it work on the optionals array, as it's a field inside an object of an array. Notice it's an object with a reference and an extra field that needs to be preserved.

db.projects.findOne({user: ObjectId("602acb4b839ec6001d3ef506")})
{
        "_id" : ObjectId("6033cf8503ac5003f873cc9b"),
        "date" : 1614008193362,
        "requirements" : [
                ObjectId("6032c1249588930368d7603c"),
                ObjectId("6032c2dc9588930368d7603e")
        ],
        "status" : "ACTIVE",
        "hours" : 10,
        "expire" : 1629560197,
        "optionals" : [
                {
                        "requirement" : ObjectId("602e3104dd86db01c89dd47e"),
                        "amount" : 10
                },
                {
                        "requirement" : ObjectId("603171e89588930368d76038"),
                        "amount" : 10
                }
        ],
        "user" : ObjectId("602acb4b839ec6001d3ef506"),
        "__v" : 0
}

db.requirements.findOne()
{
        "_id" : ObjectId("602da2bbdd86db01c89dd479"),
        "date" : "1613602429148",
        "status" : "ACTIVE",
        "name" : "Test1",
        "description" : "Test1 description",
        "creator" : ObjectId("602acb4b839ec6001d3ef506"),
        "__v" : 0
}

Here it's pointed to a solution based on $lookup, but I can't get it work: MongoDB aggregate field in array of objects


db.project.aggregate([
    {
        $match : {user: ObjectId($interesting_user)}
    },
    {
        $lookup: {
            from: "requirements",
            localField: "requirements",
            foreignField: "_id",
            as: "requirements"
        }
    },
    {
        $lookup: {
            from: "requirements",
            localField: "optionals.requirement",
            foreignField: "_id",
            as: "optionals.requirement"
        }
    },
]).pretty()

I am missing something here? Browsing the MongoDb Doc gave me no solution

Dheemanth Bhat · Accepted Answer · 2021-02-28T11:59:36.110

The idea is to $unwind optionals since it is an array, perform $lookup and then $group it. All this just to retain amount field! So try this:

NOTE I created only two fields in requirements collection in my test data. You will get all other remaining fields in your case.

db.project.aggregate([
    {
        $match : {user: ObjectId("602acb4b839ec6001d3ef506")}
    },
    {
        $lookup: {
            from: "requirements",
            localField: "requirements",
            foreignField: "_id",
            as: "requirements"
        }
    },
    { $unwind: "$optionals" },
    {
        $lookup: {
            from: "requirements",
            let: { requirements: "$optionals.requirement" },
            pipeline: [
                {
                    $match: {
                        $expr: {
                            $eq:["$_id", "$$requirements"]
                        }
                    }
                }
            ],
            as: "optionals.requirement"
        }
    },
    { $unwind: "$optionals.requirement" },
    {
        $group: { 
            _id: "$_id",
            date: { $first: "$date" },
            requirements: { $first: "$requirements" },
            status: { $first: "$status" },
            hours: { $first: "$hours" },
            expire: { $first: "$expire" },
            optionals: { $push: "$optionals" },
            user: { $first: "$user" }
        }
    }
]);

Output:

{
    "_id" : ObjectId("6033cf8503ac5003f873cc9b"),
    "date" : 1614008193362,
    "requirements" : [
        {
            "_id" : ObjectId("6032c1249588930368d7603c"),
            "name" : "Test1"
        },
        {
            "_id" : ObjectId("6032c2dc9588930368d7603e"),
            "name" : "Test2"
        }
    ],
    "status" : "ACTIVE",
    "hours" : 10,
    "expire" : 1629560197,
    "optionals" : [
        {
            "requirement" : {
                "_id" : ObjectId("602e3104dd86db01c89dd47e"),
                "name" : "Test3"
            },
            "amount" : 10
        },
        {
            "requirement" : {
                "_id" : ObjectId("603171e89588930368d76038"),
                "name" : "Test4"
            },
            "amount" : 10
        }
    ],
    "user" : ObjectId("602acb4b839ec6001d3ef506")
}

If u are looking for any other output format then update the question with the expected result. — Dheemanth Bhat, Feb 28 '21 at 12:12
Amazing @Dheemanth. It works perfectly. I couldn't figure out the need for a second $unwind. Thank you much — GonAlonso, Feb 28 '21 at 16:51
It depends on your need whether to have `requirement` as an array inside `optionals` array. If u `$unwind` it will be pushed as object otherwise as an array. — Dheemanth Bhat, Feb 28 '21 at 17:16

Mongodb aggrregate $lookup of a field inside array

1 Answers1