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:

1. 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!

2. 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!):

1. 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

2. 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
    ])

3. Simply merge new collections from 1 & 2

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

4. 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?

Answer 1

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 } })
})