0

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.
danronmoon
  • 3,814
  • 5
  • 34
  • 56
Jose Masri
  • 1

1 Answers1

0

You can try this:

db.users.aggregate([
  {
    $lookup: {
      from: "courses",
      localField: "roleId",
      foreignField: "relationRoleCourses.userRoleId",
      as: "courses"
    }
  }
])
Emanuele Moccia
  • 41
  • 3
  • Not working, courses are empty in the result – Jose Masri Aug 10 '21 at 16:06
  • That’s because of string stored roleId, check this https://mongoplayground.net/p/hL21XKr3F-B and this, it depends also on the mongo version. https://stackoverflow.com/questions/41093647/mongodb-join-on-id-field-from-string-to-objectid#comment108271411_41095024 – Emanuele Moccia Aug 10 '21 at 18:53