Aggregation with update in mongoDB

Question

I've a collection with many similar structured document, two of the document looks like

Input:

{      "_id": ObjectId("525c22348771ebd7b179add8"),      "cust_id": "A1234",      "score": 500,      "status": "A"     "clear": "No" }  {      "_id": ObjectId("525c22348771ebd7b179add9"),      "cust_id": "A1234",      "score": 1600,      "status": "B"     "clear": "No" } 

By default the clear for all document is "No",

Req: I have to add the score of all documents with same cust_id, provided they belong to status "A" and status "B". If the score exceeds 2000 then I have to update the clear attribute to "Yes" for all of the document with the same cust_id.

Expected output:

{      "_id": ObjectId("525c22348771ebd7b179add8"),      "cust_id": "A1234",      "score": 500,      "status": "A"     "clear": "Yes" }  {     "_id": ObjectId("525c22348771ebd7b179add9"),      "cust_id": "A1234",      "score": 1600,      "status": "B"     "clear": "Yes" } 

Yes because 1600+500 = 2100, and 2100 > 2000.

---

My Approach: I was only able to get the sum by aggregate function but failed at updating

db.aggregation.aggregate([     {$match: {         $or: [             {status: 'A'},             {status: 'B'}         ]     }},     {$group: {         _id: '$cust_id',         total: {$sum: '$score'}     }},     {$match: {         total: {$gt: 2000}     }} ]) 

Please suggest me how do I proceed.

Answer 1

After a lot of trouble, experimenting mongo shell I've finally got a solution to my question.

Psudocode:

# To get the list of customer whose score is greater than 2000 cust_to_clear=db.col.aggregate(     {$match:{$or:[{status:'A'},{status:'B'}]}},     {$group:{_id:'$cust_id',total:{$sum:'$score'}}},     {$match:{total:{$gt:500}}})  # To loop through the result fetched from above code and update the clear cust_to_clear.result.forEach (    function(x)    {       db.col.update({cust_id:x._id},{$set:{clear:'Yes'}},{multi:true});     } ) 

Please comment, if you have any different solution for the same question.

Answer 2

With Mongo 4.2 it is now possible to do this using update with aggregation pipeline. The example 2 has example how you do conditional updates:

db.runCommand(    {       update: "students",       updates: [          {            q: { },            u: [                  { $set: { average : { $avg: "$tests" } } },                  { $set: { grade: { $switch: {                                        branches: [                                            { case: { $gte: [ "$average", 90 ] }, then: "A" },                                            { case: { $gte: [ "$average", 80 ] }, then: "B" },                                            { case: { $gte: [ "$average", 70 ] }, then: "C" },                                            { case: { $gte: [ "$average", 60 ] }, then: "D" }                                        ],                                        default: "F"                  } } } }            ],            multi: true          }       ],       ordered: false,       writeConcern: { w: "majority", wtimeout: 5000 }    } ) 

Another example:

db.c.update({}, [   {$set:{a:{$cond:{     if: {},    // some condition       then:{} ,   // val1       else: {}    // val2 or "$$REMOVE" to not set the field or "$a" to leave existing value   }}}} ]);