Mongodb lookup query when foreign field is an array

Question

I have these 2 collections in MongoDb:

User

{
  _id: ObjectId("xxxxxxxxx"),
  roleId: ObjectId: ("xxxxxxxxx)
}

Courses

{
  _id: ObjectId("xxxxxxxxx"),
  name: "Course 1",
  relationRoleCourses: [
    {
      userRoleId: "xxxxxxxxxxx"
    },
    {
      userRoleId: "xxxxxxxxxxx"
    }
  ]
}

I'm looking for a query using lookup to get all users with their corresponding courses where the course must include the role of the user.

Expected Result

{
  _id: ObjectId("xxxxxxxxx"),
  roleId: ObjectId: ("xxxxxxxxx),
  courses: [
    {
      _id: ObjectId("xxxxxxxxx"),
      name: "Course 1",
    },
    {
      _id: ObjectId("xxxxxxxxx"),
      name: "Course 2",
    }
  ],
}

Considerations:

• In the relationRoleCourses array the userRoleId is a string, not an ObjectId.

Answer 1

You can try this:

db.users.aggregate([
  {
    $lookup: {
      from: "courses",
      localField: "roleId",
      foreignField: "relationRoleCourses.userRoleId",
      as: "courses"
    }
  }
])