How do I update a MongoDB collection with values from another collection?

Question

I have a MongoDB collection called properties and another collection of new data in a collection called newProperties.

I want to update a single field (amountOwed) in properties with the new value in newProperties for all records where the saleNumber is the same in both tables.

I've been able to write a single update script when I hard code the values. I can't get the update script to work when using an array and then parsing it using a forEach loop. Can someone explain how to do this?

So I start with a collection called properties that looks like this (I'm only including the relevant fields):

{ 
    "_id" : ObjectId("551816b02eecf1238b3baadb"), 
    "saleNumber" : NumberInt(17917), 
    "saleDetails" : {
        "amountOwed" : 266.0, 
    }
}
{ 
    "_id" : ObjectId("551816b02eecf1238b3baadc"), 
    "saleNumber" : NumberInt(851400070), 
    "saleDetails" : {
        "amountOwed" : 270.0, 
    }
}

I import a new collection called newProperties that looks like this (notice the amountOwed has changed for the record with saleNumber 17917):

{ 
    "_id" : ObjectId("551816b02eecf1238b3baadb"), 
    "saleNumber" : NumberInt(17917), 
    "saleDetails" : {
        "amountOwed" : 300.0, 
    }
}

What I want is to update properties.saleDetails.amountOwed with the value from newProperties.saleDetails.amountOwed where the saleNumbers match.

Here's the query I've written:

newActiveProperties.forEach(function(doc) {
     db.properties.update(
     {
        "saleNumber": doc.saleNumber
        , "auction": ObjectId("56fbf3a8d4c6fe5d73af67c9")
     },
    {
      $set: { "saleDetails.amountOwed": doc.saleDetails.amountOwed}
    },
    {
      multi: true
    });
});

When it runs, nothing happens. There are not results in the results tab (I'm using Mongo Chef) and none of the records get updated.

I can get it to work if I hard code the saleNumber and amountOwed:

db.properties.update(
{
    "saleNumber": "17917"
    , "auction": ObjectId("56fbf3a8d4c6fe5d73af67c9")
},
{
  $set: { "saleDetails.amountOwed": 2000}
},
{
  multi: true
});

I'm guessing it has something to do with the syntax inside a foreach loop, but I'm not sure what it is. Can someone explain how to do this?

Answer 1

With MongoDB v4.2+,

you can now use $merge in aggregation pipeline to perform update to collections.

db.properties.aggregate([
  {
    "$lookup": {
      "from": "newProperties",
      "localField": "saleNumber",
      "foreignField": "saleNumber",
      "as": "newPropertiesLookup"
    }
  },
  {
    // only matched records are kept
    "$unwind": "$newPropertiesLookup"
  },
  {
    $project: {
      "saleDetails.amountOwed": "$newPropertiesLookup.saleDetails.amountOwed"
    }
  },
  {
    "$merge": {
      "into": "properties",
      "on": "_id",
      "whenMatched": "merge"
    }
  }
])

Here is the Mongo playground for your reference.

---

Prior to MongoDB v4.2,

You can use aggregation pipeline to perform the $lookup. Then iterate the aggregation result to update the matched properties document one-by-one.

db.properties.aggregate([
  {
    "$lookup": {
      "from": "newProperties",
      "localField": "saleNumber",
      "foreignField": "saleNumber",
      "as": "newPropertiesLookup"
    }
  },
  {
    // only matched records are kept
    "$unwind": "$newPropertiesLookup"
  }
]).forEach(function(result){
  db.properties.update(
    {
      _id : result._id
    },
    {
      $set: { "saleDetails.amountOwed": result.newPropertiesLookup.saleDetails.amountOwed}
    }
  );
})