29

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.

A-Sharabiani
  • 17,750
  • 17
  • 113
  • 128
Sam
  • 2,545
  • 8
  • 38
  • 59
  • Could you describe how the failure occurred? Was there an error, or anything like that? – Steve Westbrook Oct 15 '13 at 15:36
  • No errors per se, but I found it difficult to have update and aggregate function together in a statement, I am very new to mongodb, I am trying the scenario in cmd. – Sam Oct 16 '13 at 17:27

6 Answers6

18

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.

Sam
  • 2,545
  • 8
  • 38
  • 59
  • 3
    So you didn't find a way of doing it in one statement? Eg, in a single updateMany()? You had to write some JavaScript to loop over the aggregation? – Chris Neve Mar 13 '19 at 08:59
10

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
  }}}}
]);
Mitar
  • 6,756
  • 5
  • 54
  • 86
  • 1
    As per doc. The $set and $unset used in the aggregation pipeline refers to the aggregation stages $set and $unset respectively, and not the update operators $set and $unset – raga Jul 08 '19 at 11:13
  • 2
    In your example, I can't see an aggregation pipeline?, I was expecting on these lines `db.collection.aggregate([{$match},{$lookup},{$match},{$update}`. how are you gonna attain this? or is this non-attainable and what you are saying is to pass my aggregation pipeline in `u` clause with the last stage as `$set` which gonna update the selected documents? – Anu Nov 14 '19 at 01:17
  • Is there mongodb.js equivalent for this solution with mongodb.js? I cannot seem to find it or any equivalent to the the db.runCommand as used above – Stephen Isienyi Apr 18 '20 at 22:01
7

You need to do this in two steps:

  1. Identify customers (cust_id) with a total score greater than 200
  2. For each of these customers, set clear to Yes

You already have a good solution for the first part. The second part should be implemented as a separate update() calls to the database.

Psudocode:

# Get list of customers using the aggregation framework
cust_to_clear = db.col.aggregate(
    {$match:{$or:[{status:'A'},{status:'B'}]}},
    {$group:{_id:'$cust_id', total:{$sum:'$score'}}},
    {$match:{total:{$gt:2000}}}
    )

# Loop over customers and update "clear" to "yes"
for customer in cust_to_clear:
    id = customer[_id]
    db.col.update(
        {"_id": id},
        {"$set": {"clear": "Yes"}}
    )

This isn't ideal because you have to make a database call for every customer. If you need to do this kind of operation often, you might revise your schema to include the total score in each document. (This would have to be maintained by your application.) In this case, you could do the update with a single command:

db.col.update(
    {"total_score": {"$gt": 2000}},
    {"$set": {"clear": "Yes"}},
    {"multi": true}
    )
SuperAce99
  • 712
  • 6
  • 13
  • Hi, thanks for the solution, I've been trying to run the above code, its giving me some error. 1. syntax error : unexpected identifier, 2. cant have undefined in a query expression. could you please write the complete code in format, cause I am actually new to mongoDB. – Sam Oct 16 '13 at 05:56
  • I used psudocode because it needs to be implemented on the application level and you don't specify which driver you're working with. I could do a Python (pymongo) version or perhaps figure out a way to do it with script for the Mongo shell directly. Which of these would be more helpful? – SuperAce99 Oct 16 '13 at 13:05
  • I am trying to run the code through cmd I guess script for mongo shell directly would help. I am trying to explore the capabilities of mongo by trying out different scenarios. Could you also please suggest me a good UI to work on mongo because cmd is quite frustrating. – Sam Oct 16 '13 at 17:13
5

Short Answer: To avoid looping a Database query, just add $merge to the end and specify your collection like so:

db.aggregation.aggregate([
    {$match: {
        $or: [
            {status: 'A'},
            {status: 'B'}
        ]
    }},
    {$group: {
        _id: '$cust_id',
        total: {$sum: '$score'}
    }},
    {$match: {
        total: {$gt: 2000}
    }},
    { $merge: "<collection name here>"}
])

Elaboration: The current solution is looping through a database query, which is not good time efficiency wise and also a lot more code. Mitar's answer is not updating through an aggregation, but the opposite => using an aggregation within Mongo's update. If your wondering what is a pro in doing it this way, well you can use all of the aggregation pipeline as opposed to being restricted to only a few as specified in their documentation.

Here is an example of an aggregate that won't work with Mongo's update:

db.getCollection('foo').aggregate([
  { $addFields: {
      testField: {
        $in: [ "someValueInArray", '$arrayFieldInFoo']
      } 
  }},
  { $merge : "foo" }]
)

This will output the updated collection with a new test field that will be true if "someValueInArray" is in "arrayFieldInFoo" or false otherwise. This is NOT possible currently with Mongo.update since $in cannot be used inside update aggregate.

Update: Changed from $out to $merge since $out would only work if updating the entire collection as $out replaces entire collection with the result of the aggregate. $merge will only overrite if the aggregate matches a document (much safer).

Logan Cundiff
  • 479
  • 8
  • 13
4

In MongoDB 2.6., it will be possible to write the output of aggregation query, with the same command.

More information here : http://docs.mongodb.org/master/reference/operator/aggregation/out/

Yann Moisan
  • 8,161
  • 8
  • 47
  • 91
1

The solution which I found is using "$out"

*) e.g adding a field :

db.socios.aggregate(
    [
        {
            $lookup: {
                from: 'cuotas',
                localField: 'num_socio',
                foreignField: 'num_socio',
                as: 'cuotas'
            }
        },
        { 
            $addFields: { codigo_interno: 1001 } 
        },
        {
            $out: 'socios' //Collection to modify
        }
    ]
)

*) e.g modifying a field :

db.socios.aggregate(
        [
            {
                $lookup: {
                    from: 'cuotas',
                    localField: 'num_socio',
                    foreignField: 'num_socio',
                    as: 'cuotas'
                }
            },
            { 
                $set: { codigo_interno: 1001 } 
            },
            {
                $out: 'socios' //Collection to modify
            }
        ]
    )
Jesus Ledesma
  • 11
  • 1