MongoDB.live, free & fully virtual. June 9th - 10th. Register Now

Update with Correlated Subquery

I need to update a field in one collection using a field value from another collection. What is the best way to accomplish the following SQL in MongoDB 3.6?

update action a set serial_nr=(select serial_nr from device d where a.device_id=d.device_id);

Assuming there are two collections, device and action:

db.action.aggregate( 
[
  { 
      $lookup: {
          from: "device",
          localField: "device_id",
          foreignField: "device_id",
          as: "R"
      } 
  },
  { 
      $match: { $expr: { $gt: [ { $size: "$R" }, 0 ] } } 
  }
]
).forEach( doc => db.action.updateOne( { _id: doc._id }, { $set: { serial_nr: doc.R[0].serial_nr } } ) )

Note the $set update operator will create a field called as serial_nr in the action's document, in case the field doesn’t exist (otherwise just replaces the existing value).

1 Like

Very helpful, thank you!