Lookup stage not working after unwind operation in aggregate pipeline in mongodb

user_9492640 0
Viewed 144

Being relatively new in mongodb I performed an aggregation pipeline with this three table and surprisingly I am getting the "r2" array as empty in the third lookup stage of the pipeline.I crosschecked the field names and everything is alright.

db.user.aggregate([
{
    $lookup:{
            from:"enrollment",
            localField:"user_id",
            foreignField:"userID",
            as:"r1"
        }
},

{
    $unwind:{
            path:"$r1",
            includeArrayIndex:"r1_id"
        }
},
{
    $lookup:{
            from:"course",
            localField:"r1.courseID",
            foreignField:"courseID",
            as:"r2"
        }
}
])

I have three collection as user , enrollment and course which are.

User as

> db.user.find()
{ "_id" : ObjectId("5ef4ba8d500ac8876da0d2ca"), "user_id" : 1, "first_name" : "Christian", 
"last_name" : "Hur", "email" : "christian@uta.com", "password" : "abc1234" }
{ "_id" : ObjectId("5ef4ba8d500ac8876da0d2cb"), "user_id" : 2, "first_name" : "Mary", "last_name" : 
"Jane", "email" : "mary.jane@uta.com", "password" : "password123" }
{ "_id" : ObjectId("5ef4bc2563742adee5403b1d"), "user_id" : 3, "first_name" : "ari", "last_name" : 
"dutta", "email" : "dutta@uta.com", "password" : "po1234" }

And , course as

> db.course.find()
{ "_id" : ObjectId("5ef4c1b64a77aec0af5e73ae"), "courseID" : 3333, "title" : "Adv PHP 201", 
"description" : "Advance PHP programming", "credits" : 3, "term" : "fall" }
{ "_id" : ObjectId("5ef4c20d4a77aec0af5e73af"), "courseID" : 5555, "title" : "Java 201", 
"description" : "Advanced Programming", "credits" : 4, "term" : "fall"}
{ "_id" : ObjectId("5ef4c2564a77aec0af5e73b0"), "courseID" : 6666, "title" : "Angular 1", 
"description" : "Intro to Angular", "credits" : 3, "term" : "fall,spring" }

And enrollment as

> db.enrollment.find()
{ "_id" : ObjectId("5ef771f42d98ffab4460a651"), "userID" : 1, "courseID" : "3333" }
{ "_id" : ObjectId("5ef7722d2d98ffab4460a652"), "userID" : 1, "courseID" : "6666" }

Result

{ "_id" : ObjectId("5ef4ba8d500ac8876da0d2ca"), "user_id" : 1, "first_name" : 
"Christian", "last_name" : "Hur", "email" : "christian@uta.com", "password" : 
"abc1234", "r1" : { "_id" : ObjectId("5ef771f42d98ffab4460a651"), "userID" : 
1, "courseID" : "3333" }, "r1_id" : NumberLong(0), "r2" : [ ] }

{ "_id" : ObjectId("5ef4ba8d500ac8876da0d2ca"), "user_id" : 1, "first_name" : 
"Christian", "last_name" : "Hur", "email" : "christian@uta.com", "password" : 
"abc1234", "r1" : { "_id" : ObjectId("5ef7722d2d98ffab4460a652"), "userID" : 
1, "courseID" : "6666" }, "r1_id" : NumberLong(1), "r2" : [ ] }

I also checked the documentation but found no help , how can I fix this ?

flask mongodb
1 Answers

You have a type mismach on courseID between the collections, in enrollment it's type string and in course it's type number.

Change your $lookup into this:

  {
    $lookup: {
      from: "course",
      let: {
        courseID: {
          $toInt: "$r1.courseID"
        }
      },
      pipeline: [
        {
          $match: {
            $expr: {
              $eq: [
                "$$courseID",
                "$courseID"
              ]
            }
          }
        }
      ],
      as: "r2"
    }
  }

Try it yourself: Mongo Playground

Also since you say you're new I personally advice you to use the _id field instead of the courseID/user_id that you generated yourself. it will just make it easier to maintain.

For Mongo version 4.2+ here is how to update the enrollment collection field:

db.enrollment.updateMany(
    {},
    [
        {
            $set: {
                courseID: {$toInt: "$courseID"}
            }
        }
    ]
)
user_107703700
Related