MongoDB aggregation - replace field in collection from value in another collection

Question

so i've got a collection like so

_id: "a6c67aad-e90c-4a13-aae0-74e5ca5c8632"
value : true

and one like this

_id: "a6c67aad-e90c-4a13-aae0-74e5ca5c8632"
otherValue : false

How can i use aggregation pipelines to update the second collection otherValue with the value from the first collection based on _id

I've tried using lookup and then unwind like

{
  from: 'col1', 
  localField: 'otherValue', 
  foreignField: 'value', 
  as: 'data'
}

and then unwind

{
  path: '$val'
}

But im not quite sure where to go from here, any help would be greatly appreciated.

Dheemanth Bhat · Accepted Answer · 2021-03-15 16:33:03Z

Try this:

db.collection1.aggregate([
    {
        $lookup: {
            from: "collection2",
            let: { c1_id: "$_id", value: "$value" },
            pipeline: [
                {
                    $match: {
                        $expr: { $eq: ["$_id", "$$c1_id"] }
                    }
                },
                {
                    $addFields: { otherValue: "$$value" }
                }
            ],
            as: "data"
        }
    },
    {
        $unwind: "$data"
    }
])

Output:

{
    "_id" : "a6c67aad-e90c-4a13-aae0-74e5ca5c8632",
    "value" : true,
    "data" : {
        "_id" : "a6c67aad-e90c-4a13-aae0-74e5ca5c8632",
        "otherValue" : true
    }
}

Where collection1 is:

{
    "_id" : "a6c67aad-e90c-4a13-aae0-74e5ca5c8632",
    "value" : true
}

Where collection2 is:

{
    "_id" : "a6c67aad-e90c-4a13-aae0-74e5ca5c8632",
    "otherValue" : false
}

Joe · Accepted Answer · 2021-03-15 21:06:36Z

You might use the $merge aggregation stage.

match the documents from the source collection that you want to use to update the second collect.
lookup the matching document from the second collection
unwind so it is a single document instead of an array (this stage also eliminates documents that don't match one from the second collection)
addFields to store the value from the first document into the looked up document
replaceRoot to the modified looked-up document
merge the modified documents with the original collection, matching on _id

db.collection.aggregate([
  {$match: { filter to pick the documents }},
  {$lookup: {
       from: "otherCollection"
       localField: "_id",
       foreignField: "_id",
       as: "otherDocument"
  }},
  {$unwind: "$otherDocument"},
  {$addFields:{
       "otherDocument.otherValue": "$value"
  }},
  {$replaceRoot: {newRoot: "$otherDocument"}},
  {$merge: {
       into: "otherCollection", 
       on: "_id", 
       whenMatched: "replace", 
       whenNotMatched: "insert" 
  }}
])

Collectives™ on Stack Overflow

MongoDB aggregation - replace field in collection from value in another collection

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related