Update all objects in nested array with values from other collection

Question

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

 {
    "_id": {}
    brand : ""
    model : ""
    year : ""
    suppliers : [
        "name": "",
        "contact": ""
        "supplierId":"",
        "orders":[], <-- Specific to the vehicles collection
        "info":"" <-- Specific to the vehicles collection
    ]
    
 }

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:

db.vehicles.aggregate([
    {
     "$unwind": { "path": "$suppliers", "preserveNullAndEmptyArrays": false }
    },
    { 
       "$project": { "supplierObjId": { "$toObjectId": "$suppliers.supplierId" } } 
    },
    {
     "$lookup":
        {
         "from": "suppliers",
         "localField": "supplierObjId",
         "foreignField": "_id",
         "as": "supplierInfo"
        }
    },{
     "$set": {
    "suppliers.internalId": "$supplierInfo.internalid"
  }}
     
])

But it is adding the new field, to the returned values instead to the array item at the collection.

How can i achieve this?

Answer 1

But it is adding the new field, to the returned values instead to the array item at the collection.

The .aggregate method does not update documents, but it will just format the result documents,

You have to use 2 queries, first aggregate and second update,

I am not sure i guess you want to execute this query for one time, so i am suggesting a query you can execute in mongo shell,

1. Aggregation query:

• $lookup with pipeline, pass suppliers.supplierId in let
• $toString to convert object id to string type
• $match the $in condition
• $project to show required fields
• $map to iterate loop of suppliers array
• $reduce to iterate loop of suppliers_data array and find the matching record by supplierId
• $mergeObjects to merge current object properties with new property internalId

2. Loop the result from aggregate query using forEach

3. Update Query to update suppliers array

db.vehicles.aggregate([
  {
    $lookup: {
      from: "suppliers",
      let: { supplierId: "$suppliers.supplierId" },
      pipeline: [
        {
          $match: {
            $expr: {
              $in: [{ $toString: "$_id" }, "$$supplierId"]
            }
          }
        },
        {
          $project: {
            _id: 0,
            supplierId: { $toString: "$_id" },
            internalId: 1
          }
        }
      ],
      as: "suppliers_data"
    }
  },
  {
    $project: {
      suppliers: {
        $map: {
          input: "$suppliers",
          as: "s",
          in: {
            $mergeObjects: [
              "$$s",
              {
                internalId: {
                  $reduce: {
                    input: "$suppliers_data",
                    initialValue: "",
                    in: {
                      $cond: [
                        { $eq: ["$$this.supplierId", "$$s.supplierId"] },
                        "$$this.internalId",
                        "$$value"
                      ]
                    }
                  }
                }
              }
            ]
          }
        }
      }
    }
  }
])
.forEach(function(doc) {
    db.vehicles.updateOne({ _id: doc._id }, { $set: { suppliers: doc.suppliers } });
});

Playground for aggregation query, and Playground for update query.

Answer 2

It looks like one way to solve this is by using $addFields and $lookup. We first flatten any matching suppliers, then add the property, then regroup.

You can find a live demo here via Mongo Playground.

Database

Consider the following database structure:

[{
  // Collection
  "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"
        },
        
      ]
    }
  ],
  // Collection
  "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"
    },
    {
      "name": "ignored_supplier",
      "contact": "doesnt matter",
      "_id": "xxxxxxx",
      "internalId": "xxxxxxx",
      "address": "0987 midtown"
    }
  ]
}]

Query

This is the query that I was able to get working. I'm not sure how efficient it is, or if it can be improved, but this seemed to do the trick:

db.vehicles.aggregate([
  {
    $unwind: "$suppliers"
  },
  {
    $lookup: {
      from: "suppliers",
      localField: "suppliers.supplierId",
      foreignField: "_id",  // <---- OR MATCH WHATEVER FIELD YOU WANT
      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"
      }
    }
  }
])

Results

Which returns:

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