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?

Answer 1

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