CODEWITHSUNDEEP
mongodbmongodb-queryaggregation-framework
Alireza
Alireza

Reputation: 6868

how to turn amount*(1+percent)/10 into a mongoDB aggregation?

In aggregate I have reached this point: 

amount: { $sum: { $multiply: ["$amount", {$add: ["$percent", 1]}]  }  }  } }]);

When I want to use $divide it gives me the error of unknown group operator '$divide'". How to divide this by 10?

EDIT: 

db.junk.aggregate([{  $project: {  _id: 0,  cart_closed: 1,  amount: 1,  
percent: 1, shop_id: 1  } }, {  $group: {  
_id: {  cart_closed: {  month: {  $month: "$cart_closed"  },  year: {   
$year: "$cart_closed"  }  }, shop_id: "$shop_id"  },  amount: { $sum: {   
$multiply: ["$amount", {$add: ["$percent", 1]}]  }  }  } }]);

Actually I don't group by using that arithmetic expression.

Upvotes: 1

Views: 1566

Answers (1)

Blakes Seven
Blakes Seven

Reputation: 50416

If you are getting errors then you are calling the method in the wrong place. Anything under $group must either be the grouping _id or an accumulator function, such as $sum:

Presuming you have documents like this:

{ "amount": 100, "percent": 10 },
{ "amount": 20, "percent": 33 }

Then you just do something like this:

db.junk.aggregate([
    { "$group": {
        "_id": null,
        "amount": { "$sum": {
            "$multiply": [
                "$amount",
                { "$divide": [ "$percent", 100 ] },
            ]
        }}
    }}
])

Or for documents like this:

{ "amount": 100, "percent": .10 },
{ "amount": 20, "percent": .33 }

Then it's just a simple $multiply:

db.junk.aggregate([
    { "$group": {
        "_id": null,
        "amount": { "$sum": {
            "$multiply": [
                "$amount",
                "$percent"
            ]
        }}
    }}
])

Both give the expected result:

{ "_id" : null, "amount" : 16.6 }

Otherise if the fields are not present then just fill in the constant values you want.

Just a matter of calling everything in the accumulator. If you wanted to "divide after" the sum, then you $project after the $group stage.

db.junk.aggregate([
    { "$group": {
        "_id": null,
        "amount": { "$sum": "$amount" }
    }},
    { "$project": {
        "amount": { "$multiply": [ "$amount", .70 ] }
    }}
])

If you are not even grouping at all then just use the math on the field:

db.junk.aggregate([
    { "$project": {
        "amount": {
            "$multiply": [
                "$amount",
                "$percent"
            ]
        }
    }}
])

To discount say by 5% where stored like this:

{ "amount": 100, "percent": .05 }

Then you do:

db.junk.aggregate([
    { "$project": {
        "amount": {
            "$multiply": [
                "$amount",
                { "$subtract": [ 1, "$percent" ] }
            ]
        }
    }}
])

Which returns:

{ "_id" : ObjectId("55bc9d29408c74e7d57c0176"), "amount" : 95 }

Being 5% ( 5 ) lower than the original amount.

Your formula returns a different result. Order the operations to BODMAS:

db.junk.aggregate([
    { "$project": {
        "amount": {
            "$divide": [
                { "$multiply": [
                    "$amount",
                    { "$add": [ 1, "$percent" ] }
                ]},
                10
            ]
        }
    }}
])

Which is:

{ "_id" : ObjectId("55bc9d29408c74e7d57c0176"), "amount" : 10.5 }

Upvotes: 1

Related Questions