How Can I update data with lookup

Working with Data
#1

I want to update collection A on based on Collection B field.

My query like this,

db.a.updateMany(condition,{$set:{
"productMinQty":collection b field value,
})

Thnaks.

1 Like
#2

I would take a look at https://docs.mongodb.com/manual/tutorial/update-documents-with-aggregation-pipeline/.

I am not too sure if you can $lookup. Please share any findings.

#3

@Seekex_youtube, any followup on this?

#4

Yes I am having the same issue could anyone please help in this

#5

Adding $lookup to the update pipeline would be a great add. Increasingly no-code solutions that plug directly into Mongodb are missing any ability to do collection level security, something that is usually happened in API middleware.

$lookup makes it easy to reference a single user list in a collection and protect all collections for read operations. Without a $lookup update pipeline capability we can’t easily protect writes based on a single user list collection.

#6

Hi @John_Armstrong and @Seekex_youtube ,

So MongoDB does not currently allow lookup stages in an update command, however, it does have the $merge pipeline.

Technically you cna achieve the same update with a following pipeline:

db.a.aggregate([CONDITION, LOOKUP value, ADJUST DOCUMENT, MERGE])

For example consider the following data:


// Collection collA

db.collA.find()
{ _id: ObjectId("62138110d18675354ee41fc7"), a: 1, b: 'id 1' }
{ _id: ObjectId("62138115d18675354ee41fc8"), a: 2, b: 'id 2' }


db.collB.find()
{ _id: ObjectId("62137e41d18675354ee41fc5"), id: 'id 1', v: 10 }
{ _id: ObjectId("62137e41d18675354ee41fc6"), id: 'id 2', v: 20 }

Now to update collA with productMinQty as “v” from collection collB the following merge could be used:


db.collA.aggregate([{$match: {
 a: {
  $in: [
   1,
   2
  ]
 }
}}, {$lookup: {
 from: 'collB',
 localField: 'b',
 foreignField: 'id',
 as: 'productMinQty'
}}, {$addFields: {
 productMinQty: {
  $first: '$productMinQty.v'
 }
}}, {$merge: {
 into: 'collA',
 whenMatched: 'replace',
 whenNotMatched: 'discard'
}}]
)


db.collA.find()
{ _id: ObjectId("62138110d18675354ee41fc7"),
  a: 1,
  b: 'id 1',
  productMinQty: 10 }
{ _id: ObjectId("62138115d18675354ee41fc8"),
  a: 2,
  b: 'id 2',
  productMinQty: 20 }

Thanks

1 Like
#7

Thanks for the suggested workaround @Pavel_Duchovny. I tried this but I’m getting “$merge is not supported when the output collection is the same as the aggregation collection” is this now supported in a newer version of mongodb? Thanks.

#8

Hi @Oscar_Bernal ,

Oh, yes this is available starting 5.0…

Would it be ok to do a $out to a temp collection and then run the $merge from that collection to the original collection as a workaround?

Thanks
Pavel

#9

@Pavel_Duchovny That would work for now, yes. Thank you!