Populate one collection with lookup in mongoose

Question

I'm trying to populate a collection with lookup. The thing is I need to join three collections at once using mongoose in express js. I have three collections namely, users, skills, userskills.

User and UserSkills are connected. Skills and UserSkills are connected. But not User and Skills.

My model is like

users

{ 
    _id: 5ec6d940b98e8f2c3cea5f22, 
    name: "test one", 
    email: "[email protected]" 
}

skills

{ 
    _id: 5ec786b21cea7d8c8c186a54, 
    title: "java" 
}

user-skills

{
    _id: 5ec7879c1cea7d8c8c186a56,
    skillId: "5ec786b21cea7d8c8c186a54",
    userId: "5ec6d940b98e8f2c3cea5f22", 
    level: "good" 
}

I tried

user = await User.aggregate([{
        $lookup: {
          from: "userskills",
          localField: "_id",
          foreignField: "userId",
          as: "userskills"
        }
      }, {
        $unwind: {
          path: "$userskills",
          preserveNullAndEmptyArrays: true
        }
      }, {
        $lookup: {
          from: "skills",
          localField: "userskills.skillId",
          foreignField: "_id",
          as: "userskills.skill",
        }
      }, {
        $group: {
          _id : "$_id",
          name: { $first: "$name" },
          skill: { $push: "$skills" }
        }
      }, {
        $project: {
          _id: 1,
          name: 1,
          skills: {
            $filter: { input: "$skills", as: "a", cond: { $ifNull: ["$$a._id", false] } }
          } 
        }
      }]);

Required result:

{
    "users" : [
        {
            _id: "5ec6d940b98e8f2c3cea5f22"
            name: "test one",
            email: "[email protected]",
            "skills" : [
                {
                    _id: "5ec7879c1cea7d8c8c186a56",
                    level: "good",
                    "skill" : {
                         _id: "5ec786b21cea7d8c8c186a54",
                         title: "java"
                    }
                }
           ]
        },
        {
            _id: "5ec6d940b98e8f2c3cea5f23"
            name: "test two",
            email: "[email protected]",
            "skills" : [
                {
                    _id: "5ec7879c1cea7d8c8c186a57",
                    level: "good",
                    "skill" : {
                         _id: "5ec786b21cea7d8c8c186a55",
                         title: "php"
                    }
                }
           ]
        }
    ]
}

when using

user = await User.find().populate('Skills").exec()

The result is coming like

{
    "users" : [
        {
            _id: "5ec6d940b98e8f2c3cea5f22"
            name: "test one",
            email: "[email protected]",
            "skills" : [
                {
                    _id: "5ec7879c1cea7d8c8c186a56",
                    level: "good",
                    skillId: "5ec786b21cea7d8c8c186a55" 
                }
           ]
        }
    ]
}

The problem is I need the skill name should also be fetched. Please help me to solve this issue. I'm writing a backend API in nodejs and mongodb.

Answer 1

You can embed that second $lookup as part of custom pipeline:

await User.aggregate([
    {
        $lookup: {
            from: "userskills",
            let: { user_id: "$_id" },
            pipeline: [
                { $match: { $expr: { $eq: [ "$$user_id", "$userId" ] } } },
                {
                    $lookup: {
                        from: "skills",
                        localField: "skillId",
                        foreignField: "_id",
                        as: "skill"
                    }
                },
                { $unwind: "$skill" }
            ],
            as: "skills"
        }
    }
])

Mongo Playground