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!

#10

To merge into the aggregation collection is actually available since MongoDB 4.4, see: https://www.mongodb.com/docs/manual/reference/operator/aggregation/merge/#std-label-merge-behavior-same-collection.

I tripped over that because we are locally on version 4.4 and on production on 4.2 where that error appeared. We had some time pressure when we noticed it’s not working on production.