CODEWITHSUNDEEP
mongodbaggregation-framework
Mish
Mish

Reputation: 113

Mongodb Aggregation append a field from a collection to every document in an array in another collection

I have two collections like (see below) and I'd like to append only the supplier name to every document inside the product array. The supplier name is stored in the second collection and can be linked by the supplierId in the product array. 

order
{
    _id: objectId  // objectId
    product: [{
               sku: 123,
               supplierId: 123,  // objectId
               qty: 10
             },
             {
               sku: 123456,
               supplierId: 1234,
               qty: 5
             }]
     }
}

supplier
{ 
  _id: 1234,   // objectId
  name: "Supplier 1"
},
{
 _id: 123,
 name: "Supplier 2"
}

Desired output being something like the below

{
    _id: objectId  // objectId
    product: [{
               sku: 123,
               supplierId: 123,  // objectId
               qty: 10,
               name: Supplier 2
             },
             {
               sku: 123456,
               supplierId: 1234,  // objectId
               qty: 5,
               name: Supplier 1
             }]
     }
}

My attempt looks something like this which results in two seperate documents with the correct supplier against the correct product. Not sure if i'm heading in the right direction or where to go next. Is it a case of some grouping or project step next? Please advise

[{ $unwind: {  path: '$product'} }, 
 { $lookup: {  from: 'supplier',  localField: 'product.supplierId', foreignField: '_id',  as: 'sup'} }
, { $unwind: {  path: '$sup'} }]

Upvotes: 2

Views: 1746

Answers (1)

s7vr
s7vr

Reputation: 75984

You can use below aggregation in 3.4.

$addFields to add the name to the product from supplier document followed by $group to collect product back into array.

db.order.aggregate([
 {"$unwind":"$product"},
 {"$lookup":{"from":"supplier","localField":"product.supplierId","foreignField":"_id","as":"supplier"}},
 {"$unwind":"$supplier"},
 {"$addFields":{"product.name":"$supplier.name"}},
 {"$group":{"_id":"$_id","product":{"$push":"$product"}}}
]

Upvotes: 1

Related Questions