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.
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.
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.
Yes I am having the same issue could anyone please help in this
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.
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
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.
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