how to connect multiple level collection in mongodb using aggregate?

user_16717863 0
Viewed 31

i have 3 collection

vehicleModel

{
 id_vehicleModel: {type:Number}
 vehicleModelName: {type:String}
}

vehicleModel_vehicleBodyType

{
 id_vehicleModel: {type:Number}
 id_vehicleBodyType: {type:Number}
}

vehicleBodyType

{
 id_vehicleBodyType: {type:Number}
 vehicleBodyTypeName: {type:String}
}

i want to join that 3 collection so i can get result

{
 id_vehicleModel: 1
 vehicleModelName: "Tesla"
 VModel_VBodyType: {
            id_vehicleModel: 1,
            id_vehicleBodyType: 4
            VBodyType: {
                        id_vehicleBodyType: 4
                        vehicleBodyTypeName: "Sport"
                        }
         }
}

i've try to aggregate with

schemaVehicleModel.aggregate([
       {
         $lookup: {
           from: "vehicleModel_vehicleBodyType",
           localField: "id_vehicleModel",
           foreignField: "id_vehicleModel",
           as: "VModel_VBodyType",
         },
       },
       { $unwind: "$VModel_VBodyType" },
])

but how can i aggregate the next collection?

mongoose mongodb-query mongodb
1 Answers

You can refer to sample here: https://mongoplayground.net/p/np_4eGhIM3C

Your aggregation would look like below:

db.vehicleModel.aggregate([
  {
    "$lookup": {
      "from": "vehicleModel_vehicleBodyType",
      "localField": "id_vehicleModel",
      "foreignField": "id_vehicleModel",
      "as": "vehicleModel_vehicleBodyType_Join"
    }
  },
  {
    "$unwind": "$vehicleModel_vehicleBodyType_Join"
  },
  {
    "$lookup": {
      "from": "vehicleBodyType",
      "localField": "vehicleModel_vehicleBodyType_Join.id_vehicleBodyType",
      "foreignField": "id_vehicleBodyType",
      "as": "vehicleBodyType_Join"
    }
  },
  {
    "$unwind": "$vehicleBodyType_Join"
  }
  
])

Aggregation stages: lookup: to join vehicleModel with vehicleModel_vehicleBodyType based on field id_vehicleModel unwind: to convert joined array objects to objects lookup: to join result with vehicleBodyType based on field id_vehicleBodyType unwind: to convert joined array objects to objects you can add project to get the desired fields in output.

Please note it does not matter how many lookups you want to do key here is you should now "localField" & "foreignField" and then just do the lookup the way you do for any first lookup stage

user_64625380
Related