Join multiple collections using Aggregations with conditions

Question

i have 3 collections like this

  ModelA : 

  {
      _id :ObjectId("60d2cd964200b320e7dc5c04"),
      name : 'Item 1'
  }


  ModelB : 

  {
      _id :ObjectId("60e3c8e0748d2a18476ceb6f"),
      modelA : ObjectId("60d2cd964200b320e7dc5c04"),
      field1 : 'field1 item A',
      field2 : 'field2 item B',
  }


  ModelC : 

  {
      _id :ObjectId("60e3c8e0748d2a18476ceb70"),
      modelB : ObjectId("60e3c8e0748d2a18476ceb6f"),
      status : 'finish'
  }

i want to fetch data all these collections where the ModelA name is 'Item 1' and ModelC status is equal 'Finish', how can i join these 3 collections using Aggregations ?

You can use the aggregation [$lookup](https://docs.mongodb.com/manual/reference/operator/aggregation/lookup/) stage query for joining multiple collections. — prasad_, Jul 06 '21 at 06:31
@prasad_ i know i have try using that, the problem is i dont know how to join 3 collections. — captain sparrow, Jul 06 '21 at 06:37
To start with, you join two collections. Then on the resulting data set you do another join with the third collection. Please feel free to search the net for more examples ( "mongodb how to join three collections" ). Here is one such example: https://stackoverflow.com/questions/40789057/mongodb-join-multiple-collections — prasad_, Jul 06 '21 at 06:39
thx for reply, i know we can using multiple $lookup, in my case is how we can join if ModelC foreignField is Model B _id. And i know too we can join two collections first but i want to know if there is solution for just one query. — captain sparrow, Jul 06 '21 at 06:51

Duy Quoc · Accepted Answer · 2021-07-06T08:49:06.623

1

You can put multiple $lookup stages to join multiple collections, so you could use a query like this:

db.ModelA.aggregate([
   {
      $lookup:{
         from:"ModelB",
         localField:"_id",
         foreignField:"modelA",
         as:"modelB"
      }
   },
   {
      $unwind:"$modelB"
   },
   {
      $lookup:{
         from:"modelC",
         localField:"ModelB._id",
         foreignField:"modelB",
         as:"modelC"
      }
   },
   {
      $unwind:"$modelC"
   },
   {
      $match: {
         name: "Item 1",
         "modelC.status": "finish"
      }
   },
   { // disable this stage if you want to get all fields of 3 collections or modify it with the output that you want
      $project:{
         "name": 1,
         "status": "$modelC.status"
      }
   }
]);

edited Jul 06 '21 at 08:49

answered Jul 06 '21 at 07:07

Duy Quoc

171
1
8

thx for reply how can we do for the match where ModelA name is 'Item 1' and ModelC status is 'Finish' ? – captain sparrow Jul 06 '21 at 08:21
You can match documents to the condition you want by adding stage [match](https://docs.mongodb.com/manual/reference/operator/aggregation/match/) to the query. I just updated my answer again. hope it can helpful to you – Duy Quoc Jul 06 '21 at 08:56

Join multiple collections using Aggregations with conditions

1 Answers1