CODEWITHSUNDEEP
arraysmongodbmongodb-queryaggregation-framework
NEET JASSI
NEET JASSI

Reputation: 85

MongoDB client join on multiple Collections

My Collections

Bookings - id,unitId

Unit - id,title,courseId

Course - id,title

I want to get booking array with unit & course title, like following

Boking_id Unit_title course_title

xxxx ABC XYZ

My Current Code

const bookings   = await db.getDB().collection('bookings').aggregate([
  {
    $lookup:
    {
      from: 'courseUnit',
      localField: 'courseUnitId',
      foreignField: '_id',
      as: 'uu'
    }
  },
  {
      $unwind:"$uu"
  }
]).toArray();

Upvotes: 0

Views: 105

Answers (1)

krishna Prasad
krishna Prasad

Reputation: 3812

The solution you are looking for is solved by using multiple time $lookup pipeline in the aggregation pipelines. e.g:

db.Bookings.aggregate([
    { 
        "$lookup" : 
            {"from":  "Unit", "localField": "unitId", "foreignField": "_id", as : "UnitDetails"   } 
    }, 
    {
        "$unwind" : "$UnitDetails"
    },
    {
        $lookup:   
            { "from": "Course", "localField": "UnitDetails.courseId", "foreignField": "_id", as: "CouserDetails"    }  
    },
    {
        "$unwind" : "$CouserDetails"
    },
    {   
        $project:  {_id:0, "Boking_id" : "$_id",  "Unit_title": "$UnitDetails.title", "course_title": "$CouserDetails.title" } 
    }   

]).pretty()

The output of the above query is as below: 

{
    "Boking_id" : ObjectId("5dff07e4187da4c2b5ffc59f"),
    "Unit_title" : "unit title for course title 3",
    "course_title" : "Title of the Course collection 3"
}

My Initial collection datasets as below in all the three collections: 

> db.Bookings.find()
{ "_id" : ObjectId("5dff07e4187da4c2b5ffc59f"), "unitId" : ObjectId("5dff06d6187da4c2b5ffc59e") }
> db.Unit.find()
{ "_id" : ObjectId("5dff06d6187da4c2b5ffc59e"), "title" : "unit title for course title 3", "courseId" : ObjectId("5dff010d187da4c2b5ffc59b") }
> db.Course.find()
{ "_id" : ObjectId("5dff0108187da4c2b5ffc599"), "title" : "Title of the Course collection" }
{ "_id" : ObjectId("5dff010b187da4c2b5ffc59a"), "title" : "Title of the Course collection 2" }
{ "_id" : ObjectId("5dff010d187da4c2b5ffc59b"), "title" : "Title of the Course collection 3" }
{ "_id" : ObjectId("5dff010f187da4c2b5ffc59c"), "title" : "Title of the Course collection 4" }
>

For details please go the through below references:

  1. How to join multiple collections with $lookup in mongodb
  2. Multiple join conditions using the $lookup operator
  3. Mongodb Join on _id field from String to ObjectId

If the values of unitId and courseId in Unit & Course collection is stored as simple string then you have to use the $toObjectId. I have created another set of collections and used the $toObjectId as below: 

> db.Bookings2.find()
{ "_id" : ObjectId("5dff6c2a187da4c2b5ffc5a3"), "unitId" : "5dff66c9187da4c2b5ffc5a2" }
> db.Unit2.find()
{ "_id" : ObjectId("5dff66c9187da4c2b5ffc5a2"), "title" : "Unit title 1", "courseId" : "5dff6694187da4c2b5ffc5a0" }
> db.Course2.find()
{ "_id" : ObjectId("5dff6694187da4c2b5ffc5a0"), "title" : "Title of the Course collection 1" }
{ "_id" : ObjectId("5dff6694187da4c2b5ffc5a1"), "title" : "Title of the Course collection 2" }
> 
> db.Bookings2.aggregate([ {$project: {"unitObjectId" : {$toObjectId: "$unitId"}} },  {$lookup:  {"from": "Unit2", "localField": "unitObjectId", "foreignField": "_id", as:  "UnitDetails" }  }, {$unwind: "$UnitDetails"}, {$project: {"courseObjectId":   {$toObjectId:  "$UnitDetails.courseId" }, "unitTitle": "$UnitDetails.title"  } }, {$lookup:  {"from": "Course2", "localField": "courseObjectId", "foreignField": "_id", as:  "Course2Details"  }  }, {$unwind: "$Course2Details"}, {$project: {_id: 0, "Boking_id": "$_id",  "Unit_title":  "$unitTitle", "course_title": "$Course2Details.title" }  }    ]).pretty()
{
    "Boking_id" : ObjectId("5dff6c2a187da4c2b5ffc5a3"),
    "Unit_title" : "Unit title 1",
    "course_title" : "Title of the Course collection 1"
}
>

Upvotes: 2

Related Questions