Cascaded join using mongoose node js

Question

I am trying to fetch data from MongoDB using Node Js. I have three schemas: Projects, Users, and Teams. I need to retrieve the project details based on it's type with the worker users. I got stuck in making join for these schemas:

Projects:

const Project = new Schema({
    projectName: { type: String, required: true, trim: true },
    type: { type: String, required: true, trim: true },
    teamID: { type: Schema.Types.ObjectId, required: true },
});

Teams

const Team = new Schema({
    teamId: { type: Schema.Types.ObjectId, required: true, trim: true },
    users: { type: [Schema.Types.ObjectId], required: true, trim: true },
    teamName: { type: String, required: true },
});

Users:

const User = new Schema({
    userId: { type: Schema.Types.ObjectId, required: true, trim: true },
    name: { type: String, required: true, trim: true },
    profilePicture: { type: String, required: true, trim: true },
});

I am trying to find a way to get

[
   {
      projectName: "s",
      type: "w",
      users: ["Jon", "Ali", "Mark"]
   },
   {
      projectName: "a",
      type: "w",
      users: ["Jon", "Mark"]
   },   {
      projectName: "s",
      type: "w",
      users: ["Jon", "Ali", "Mark"]
   },
]

I tried to use $lookup, but I can not use it because the relation is complex many to many relations. Is there a way more efficient than retrieving all users, all teams, and all projects?

Answer 1

I think there is no other efficient way except aggregation and without lookup we can't join collections, You can use nested lookup,

• $match condition for type
• $lookup to join Team collection using teamID
• $match teamID
• $lookup to join User collection using users array
• $project to convert user's name array using $map
• $addFields to get users array in users using $arrayElemAt

db.Project.aggregate([
  { $match: { type: "w" } },
  {
    $lookup: {
      from: "Team",
      let: { teamID: "$teamID" },
      as: "users",
      pipeline: [
        { $match: { $expr: { $eq: ["$$teamID", "$teamId"] } } },
        {
          $lookup: {
            from: "User",
            localField: "users",
            foreignField: "userId",
            as: "users"
          }
        },
        {
          $project: {
            users: {
              $map: {
                input: "$users",
                in: "$$this.name"
              }
            }
          }
        }
      ]
    }
  },
  { $addFields: { users: { $arrayElemAt: ["$users.users", 0] } } }
])

Playground

---

Second possible way, you can combine $project and $addFields stages in single stage,

  {
    $addFields: {
      users: {
        $arrayElemAt: [
          {
            $map: {
              input: "$users.users",
              in: "$$this.name"
            }
          },
          0
        ]
      }
    }
  }

Playground