How to get sum of differences of two fields in MongoDB?

Question

I already have a solution but I am looking at a solution which does all the job on MongoServer (Because I think it'd be faster and less memory consuming)

I have a Class Method like:

function getTotalOutstandingAmount(){
    $outstandingAmount = 0;
    $subs = $this->mongo->selectCollection('SmsSubscriptions');
    $activeSubsctiptions = $subs->find(array('Status' => 1, '$where' => "this.SubscriptionPayments < this.SubscriptionTotal"));
    foreach ($activeSubsctiptions AS $sub){
        $outstandingAmount += $sub['SubscriptionTotal'] - $sub['SubscriptionPayments'];
    }

    return $outstandingAmount;
}

Now is there a way of calculating the Sum of differences of the two fields using aggregate method of MongoDB? Is there any other more efficient way of doing this?

score 4 · Accepted Answer · answered Aug 23 '16 at 08:05

The aggregation approach should have this pipeline:

db.SmsSubscriptions.aggregate([
    { 
        "$project": {
            "outstandingAmount": { 
                "$subtract": ["$SubscriptionTotal", "$SubscriptionPayments"] 
            },
            "Status": 1
        }
    },
    { "$match": { "Status": 1, "outstandingAmount": { "$gt": 0 } } },   
    {
        "$group": {
            "_id": null,
            "totalOutstandingAmount": { "$sum": "$outstandingAmount" }
        }
    }
])

The equivalent PHP example implementation:

$ops = array(
    array(
        "$project" => array(
            "Status" => 1,
            "outstandingAmount" => array(               
                "$subtract" => array("$SubscriptionTotal", "$SubscriptionPayments")
                )
            )           
        )
    ),
    array( 
        "$match" => array( 
            "Status" => 1, 
            "outstandingAmount" => array("$gt" => 0) 
        ) 
    ),
    array(
        "$group" => array(
            "_id" => null,
            "totalOutstandingAmount" => array("$sum" => "$outstandingAmount" )
        )
    )
);
$results = $this->mongo->selectCollection('SmsSubscriptions')->aggregate($ops);

Works perfectly. I was wondering how to make this work if one or both of the fields in `$subtract` are strings? — enemetch, Aug 23 '16 at 08:17
The only unfortunate part is `$subtract` won't work well with strings as the arguments to the operator can be any valid expression as long as they resolve to numbers and/or dates. So you need a mechanism to convert the strings to some numerical representation for that to work. — chridam, Aug 23 '16 at 08:28

score 1 · Answer 2 · answered Jan 01 '20 at 12:20

1

A generic simple compact solution to get sum of differences

collectionX

{ _id: 'xxxx', itemOne: 100, itemTwo: 300 }

{ _id: 'yyyy', itemOne: 200, itemTwo: 800 }

{ _id: 'zzzz', itemOne: 50, itemTwo: 400 }

Aggregate operation

db.collectionX.aggregate([
  {
    $group: {
      _id: null,
      sumOfDifferences: { $sum: { $subtract: ['$itemTwo', '$itemOne']}
  }
])

response

{
   "_id" : null,
   "sumOfDifferences" : 1150
}

answered Jan 01 '20 at 12:20

P.Vamsi

73
1
1
10

small and simple. worked for me – momal Feb 05 '23 at 18:30

How to get sum of differences of two fields in MongoDB?

2 Answers2