CODEWITHSUNDEEP
mongodbmongoosemongodb-queryaggregation-frameworkaggregate
masdap
masdap

Reputation: 161

Chained lookup aggregation mongoDB multiple collections

Hi guys i want to ask.

So i have 3 collections:

  1. brand: (brandId, brandName, etc ...)
  2. partner: (partnerId, partnerName, etc ...)
  3. partnership: (partnersipId, brandId, partnerId, etc...)

As you can see, i want to join partner collection with partnership collection by partnerId, and i already joined that. But i need brandName become brandName field on partnership collections. I already tried this query:

db.getCollection('partner').aggregate([
    {
        $match: { partnerId: 107 }
    },
    {"$lookup":
        {
            "from" : "partnership",
            "localField" : "partnerId",
            "foreignField" : "partnerId",
            "as" : "partnership"
        }
    },
    {"$lookup" :
        {
            "from" : "brand",
            "localField" : "partnership.brandId",
            "foreignField" : "partnership.brandId",
            "as" : "partnership.brand"
        }
    }])

But no luck :(

Expected result:

{
    partnerId: 107,
    partnerName: 'TEST',
    partnership: [
        {
            partnerId: 107,
            partnershipId: 'd8b674ab-3c3d-4772-b5e8-81b4c0279424',
            brandId: 66
            brandName: 'BrandNameId66'
        },
        {
            partnerId: 107,
            partnershipId: '13246d75-59ed-47c2-82c5-54f73ad25cf0',
            brandId: 77
            brandName: 'BrandNameId77'
        }
    ]
}

Current result:

{
    partnerId: 107,
    partnerName: 'TEST',
    partnership: {
        brand: [
            {
                brandId: 1,
                brandName: 'BrandNameId1'
            },
            ...
        ]
    }
}

Please help me. Thank you!

Upvotes: 0

Views: 276

Answers (1)

sushant mehta
sushant mehta

Reputation: 1274

You need to first unwind partnership before next lookup as:

db.getCollection('partner').aggregate([
    {
        $match: { partnerId: 107 }
    },
    {"$lookup":
        {
            "from" : "partnership",
            "localField" : "partnerId",
            "foreignField" : "partnerId",
            "as" : "partnership"
        }
    },
    {$unwind:{path: "$partnership", preserveNullAndEmptyArrays: true}}
    {"$lookup" :
        {
            "from" : "brand",
            "localField" : "partnership.brandId",
            "foreignField" : "partnership.brandId",
            "as" : "partnership.brand"
        }
    }
    {$group:{
      _id:"$_id",
      partnerId:{$min:"$partnerId"},
      partnerName:{$min:"$partnerName"},
      partnership:{$push:"$partnership"}
    }}
])

Upvotes: 1

Related Questions