CODEWITHSUNDEEP
mongodbnosqlaggregation-framework
Oleg Ivanytskyi
Oleg Ivanytskyi

Reputation: 1091

Join multiple collections in mongodb and keep all fields

I am new to NoSQL databases, and I got a little confused with collection aggregation. Here is what I am trying to do: I have three collections: productCollection, detailsCollection and brandCollection.
productCollection has the following fields: _id, name, details
detailsCollection has _id and brand
brandCollection has _id and name

These collections also have other fields, but these are the most interesting for me. As you may guess, details in productCollection is a reference to _id in detailsCollection, while brand in detailsCollection is a reference to _id in brandCollection. What I need is to get the collection with products and their brands. So, basically, I need to join these three collections and extract name from productCollection and name from brandCollection

So far, I managed to write this script:

db.productCollection.aggregate([
   {
      $lookup: {
         from: "detailsCollection",
         localField: "details",   
         foreignField: "_id",  
         as: "det"
      }
   },
   
   {
      $replaceRoot: { newRoot: { $mergeObjects: [ { $arrayElemAt: [ "$det", 0 ] }, "$$ROOT" ] } }
   },
   
   { $project: { "det": 0 } },
   
   {
       $lookup: {
           from: "brandCollection",
           localField: "brand",
           foreignField: "_id",
           as: "br"
       }
   },
   
   {
      $replaceRoot: { newRoot: { $mergeObjects: [ { $arrayElemAt: [ "$br", 0 ] }, "$$ROOT" ] } }
   },
   
   { $project: { "br": 0 } }
])

It shows me all the fields in all three collections, but it does not show me the brand's name. I think it might be because the field name appears in both productCollection and brandCollection. All other fields are fine.
Hence, my question is: how do I make name from brandCollection appear in the result too? Maybe I can rename it in the process to be shown under another name? And is there an easier way to join these three collections? Or is the script above fine?

Thank you for any help!

Upvotes: 1

Views: 113

Answers (1)

turivishal
turivishal

Reputation: 36154

  • $lookup with detailsCollection collection
  • $lookup with brandCollection and pass localField as brand id
  • $arrayElemAt to get first element from brand result
  • remove details field its no longer needed
db.productCollection.aggregate([
  {
    $lookup: {
      from: "detailsCollection",
      localField: "details",
      foreignField: "_id",
      as: "brand"
    }
  },
  {
    $lookup: {
      from: "brandCollection",
      localField: "brand.brand",
      foreignField: "_id",
      as: "brand"
    }
  },
  {
    $addFields: {
      brand: {
        $arrayElemAt: ["$brand.name", 0]
      },
      details: "$$REMOVE"
    }
  }
])

Playground

Upvotes: 1

Related Questions