Update all objects in nested array with values from other collection

Working with Data
1

I have a collection of vehicles with the following car structure:

 {
    "_id": {}
    brand : ""
    model : ""
    year : ""
    suppliers : [
        "name": "",
        "contact": ""
        "supplierId":""
    ]
    
 }

And a Suppliers collection with a structure like:

{
    "name":"",
    "contact":"",
    "_id":{}
    "internalId":"",
    "address":"",
    ...
}

I need to add a new field in the suppliers array within each document in the vehicles collection with the internalId field from the supplier in the suppliers collection that has the same _id.

if the supplier array has a document with the id 123, i should go to the suppliers collection and look for the supplier with the id 123 and retrieve the internalId. afterwards should create the field in the supplier array with that value.

So that i end up with the vehicles collection as:

 {
        "_id": {}
        brand : ""
        model : ""
        year : ""
        suppliers : [
            "name": "",
            "contact": ""
            "supplierId":""
            "internalId":"" <-- the new field
        ]
        
     }

Tried an aggregation pipeline with a lookup to get the suppliers data but failed miserably.

How can i achieve this?

2

It would be helpful if you could provide:

  1. Sample documents from both collections that we can import in our environment.
  2. The pipeline you have tried so far and some explanation on how it failed.
4

Sample vehicles:

{
    “_id”: “1”,
    brand: “ford”,
    model: “explorer”,
    year: “1999”,
    suppliers: [
    {
        name: “supplier1”,
        contact: “john doe”,
        supplierId: “001”
   },
   {
       name: “supplier2”,
       contact: “jane doez”,
        supplierId: “002”
   }]
},
{
    “_id”: “2”,
    brand: “honda”,
    model: “accord”,
    year: “2002”,
    suppliers: [
{
    name: “supplier1”,
    contact: “john doe”,
    supplierId: “001”
},


  ]
}

Sample suppliers:

{
    “name”: “supplier1”,
    “contact”: “john doe”,
    “_id”: “001”,
    “internalId”: “999-001”,
    “address”: “111 main street”
},
{
    “name”: “supplier2”,
    “contact”: “jane doez”,
    “_id”: “002”,
    “internalId”: “999-002”,
    “address”: “222 north street”
}

The query:

db.vehicles.aggregate([
{
    $unwind: “$suppliers”
},
{
    $lookup: {
        from: “suppliers”,
        localField: “suppliers.supplierId”,
        foreignField: “_id”, 
        as: “vehicle_suppliers”
     }
},
{
    $unwind: “$vehicle_suppliers”
},
{
    $addFields: {
        “suppliers.internalId”: “$vehicle_suppliers.internalId”
}
},
{
    $group: {
        _id: “$_id”,
    brand: {
        $first: “$brand”
},
    model: {
    $first: “$model”
},
    year: {
       $first: “$year”
},
suppliers: {
   $push: “$suppliers”
}
}
}
])

It return the correct supplier internalId within the array of suppliers in the vehicles.
The problem is that it isnt affecting the real collection but just formating the result :<

5

All the quotes from your sample documents are fancy HTML matching quotes so the documents cannot be inserted as-is into our environment. Enclose in 2 lines of triple back ticks to preserve real quotes. In principles keys are also in quotes. Without quotes it will not work in Compass but will work in the shell because JS allows unquoted keys.

To update the source collection take a look at https://docs.mongodb.com/manual/reference/operator/aggregation/merge/

Personally, I prefer to use https://docs.mongodb.com/manual/reference/operator/aggregation/out/ in a temporary collection. I then replace the original collection once I am happy with the result.