How to use $update/ $set operator in aggregation pipeline mongodb?

Question

I am trying to update a field called name in my coll1 collection based on certain criteria. I first created a create an aggregation pipeline that filters out documents based on my criteria.

var local_filter = { "$or" :[ 
                                          {'fullText': {'$eq': "404 Error"}},
                                          {'fullText': {'$eq': "Unknown Error"}},
                                          {'fullText': {'$eq': "503 Error"}},
                                          {'fullText': {'$eq': "400 Error"}},
                                          {'fullText': {'$eq': "500 Error"}},
                                          {'fullText': {'$eq': "Read timed out"}},
                                          {'fullText': {'$eq': "410 Error"}},
                                          {'fullText': {'$eq': "403 Error"}},
                                          {"fullText": {'$eq':""}},
                              ]}

var foreign_filter= { "$and" :[
                              {'matchingrecords.Text': {'$ne': "404 Error"}},
                              {'matchingrecords.Text': {'$ne': "Unknown Error"}},
                              {'matchingrecords.Text': {'$ne': "503 Error"}},
                              {'matchingrecords.Text': {'$ne': "400 Error"}},
                              {'matchingrecords.Text': {'$ne': "500 Error"}},
                              {'matchingrecords.Text': {'$ne': "Read timed out"}},
                              {'matchingrecords.Text': {'$ne': "410 Error"}},
                              {'matchingrecords.Text': {'$ne': "403 Error"}},
                              {"matchingrecords.Text": {'$ne': ""}},
                              {"matchingrecords.Text": {'$ne':'null'}}
                              ]}

db.coll1.aggregate([
    {$match:local_filter //9474
    },
    {$lookup: {
           from: "coll2",
           localField: "_id",   //from coll1
           foreignField: "_id", //from coll2
           as: "matchingrecords"
         }//4518
    },
    { $match: foreign_filter
    },
    { $match: {matchingrecords: {$ne:[]} }
    },//3645
    {
      $count: "totalCount"
    }
    ])//3645

So, I get now 3645 documents in coll1which I need to update the name field. There are 2 ways I've tried, both don't work:

adding { $set: { "Name" :matchingrecords.Text} } to above pipeline. This sets Name literally with string matchingrecords.Text and not the value from it. Also, adding $ doesn't help too!
Using aggregation with Update, I passed my aggregation pipeline in u clause.

    db.runCommand(
              {
                update: "coll1",
                updates: [
                   {
                     q: { },
                     u: [// You can pass you aggregation pipeline here
                             {$match: local_filter//9474
                              },
                              {$lookup: {
                                     from: "coll2",
                                     localField: "_id",
                                     foreignField: "_id",
                                     as: "matchingrecords"
                                   }//4518
                              },
                              { $match: foreign_filter
                              },
                              { $match: {matchingrecords: {$ne:[]} }
                              },//3645
                              { $set: { "Name" : 'matchingrecords.Text' } }
                         ],
                         multi: true
                       }
                    ],
                    ordered: false,
                    writeConcern: { w: "majority", wtimeout: 5000 }
                 })

It complains that $match operator isn't allowed in update!

{ 
    "n" : 0.0, 
    "nModified" : 0.0, 
    "writeErrors" : [
        {
            "index" : 0.0, 
            "code" : 72.0, 
            "errmsg" : "$match is not allowed to be used within an update"
        }
    ], 
    "ok" : 1.0
}

Any suggestions on how I can update my 3645 documents?

Got a simple hack!

Solution(It worked for me!):

Using coll1, create a new collection with 3645 docs.

      db.coll1.aggregate([
        {$match:filter //9474
        },
        {$lookup: {
               from: "coll2",
               localField: "_id",
               foreignField: "_id",
               as: "matchingrecords"
             }//4518
        },
        { $match: foreign_filter
        },
        { $match: {matchingrecords: {$ne:[]} }
        },//3645
        { $unwind: { path: "$matchingrecords", preserveNullAndEmptyArrays: true }
        },
        { $project : <what All fields you Need?>
        },
        { $out: "child_coll1"
        }//get 3645 in the a new collection

Using coll1, get non-matched docs in a separate collection

   db.coll1.aggregate([
        {$lookup: {
               from: "child_coll1",
               localField: "_id",
               foreignField: "_id",
               as: "matchingrecords"
             }//
        },
        { $match: {matchingrecords: {$eq:[]} }
        },//30944
        { $unwind: { path: "$matchingrecords", preserveNullAndEmptyArrays: true }
        },
        { $out: "child_coll2"
        }//get out 30944 docs other than 3645
    ])

Simply merge new collections from 1 & 2

        db.child_coll1.find().forEach(function(doc){
           db.child_coll2.insert(doc); 
        });

delete all collection other than child_coll2, you can rename it to coll1

This isn't an elegant solution, just a hack to get things done! Does anyone have a better/elegant solution in one query?

The question is not very clear. Some notes: **(1)** Aggregation queries _do not update_ collection data; only transform data. **(2)** You can use Aggregation pipeline within an `update` query: (a) starting with _MongoDB version 4.2_, and (b) aggregation stages $addFields (its alias $set), $project (its alias $unset) and $replaceRoot (its alias $replaceWith) _can only be used_. — prasad_, Nov 14 '19 at 04:31
Please see these for more info: [Update with Aggregation Pipeline](https://docs.mongodb.com/manual/reference/method/db.collection.update/index.html#update-with-aggregation-pipeline) _and_ [Aggregation Pipeline Quick Reference](https://docs.mongodb.com/manual/meta/aggregation-quick-reference/). — prasad_, Nov 14 '19 at 04:45
@matthPen, I provided a simple hack, could you suggest a single query that does my steps into 1? — Anu, Nov 14 '19 at 07:59
I think there's a solution with a $out stage on self collection, but it must be used very carefully, as it will replace your entire collection. The idea is to do lookup on all documents, update those who are matching updating conditions, and project fields to output documents with same format as original. The tip is that document not matching updating consditions must be in output, even if they weren't modified. But depending to your collection (do all docs respect the same structure? percentage of docs to be updated, etc) it can be less efficient (as 'no filter' is mandatory) than your hack — matthPen, Nov 14 '19 at 08:10

score 6 · Accepted Answer · edited Dec 26 '20 at 21:07

Why your first solution with aggregation doesn't work?

$set is not a valid stage operator for the aggregation pipeline.

Why your 2nd solution with the update command doesn't work?

Update command does not accept aggregation pipeline operators. The only couple of operators are supported starting mongo@4.2 as per reference shared by @prasad_

So how to fix this problem?

Check if you can use $replaceRoot for your use case or not. If not, you can use the following hack.

First, start with aggregation as you did first and add $addFields stage to add a new field for each document with the value you want to set. Then run another update command as following

db.coll1.aggregate([
   { 
   // your match queries 
   },
   {
   $addFields: { myNewName: "myvalue" } 
   }
]).toArray().forEach(function(myDoc){
   db.coll1.update({ _id: myDoc.id }, { $set: {Name: myDoc.myNewName } })
})

How to use $update/ $set operator in aggregation pipeline mongodb?

Got a simple hack!

1 Answers1