Update with Correlated Subquery

Working with Data
1

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);

1 Like
2

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).

2 Likes
3

Very helpful, thank you!

4

how do we modify this to :
update action a set Numberofdevices=(select count(serial_nr) from device d where a.device_type=d.device_type);

In which I need to update counts in main collection