Mongodb aggregation framework project with conditions
mongodb, mongoid
Solution
BookKeepingEntry.collection.aggregate(
{ $project: {
_id: 0,
credit : { $cond: [ {$lt: ['$value',0]}, '$value', 0 ] },
debit : { $cond: [ {$gt: ['$value',0]}, '$value', 0 ] },
month : { $month : "$date" }
}},
{ $group : {_id: {Month: "$month"} , CreditSum: {$sum: "$credit"}, DebitSum: {$sum: "$debit"}} },
{ $sort : { "_id.Month" : 1 } }
);
Problem
I have a collection of book keeping entries, that look like this: ``` { _id: 5141aff1a1d24c991a000002, date: 2012-02-23 00:00:00 UTC, details: "sdfd", value: 250, clinic_id: "513e2227a1d24ceab3000001" } ``` I want to get a report of monthly credit and debit totals. Like this: ``` [ {"credit"=> -229, "debit" => 0 "month"=>1}, {"credit"=> -229, "debit" => 0 "month"=>2}, {"credit"=> -229, "debit" => 0 "month"=>3}, {"credit"=> -229, "debit" => 0 "month"=>4}, {"credit"=> -229, "debit" => 0 "month"=>5}, {"credit"=> -229, "debit" => 0 "month"=>6}, {"credit"=> -229, "debit" => 0 "month"=>7}, {"credit"=> 0, "debit" => 300 "month"=>8}, {"credit"=> 0, "debit" => 300 "month"=>9}, {"credit"=> 0, "debit" => 300 "month"=>10}, {"credit"=> 0, "debit" => 300 "month"=>11}, {"credit"=> 0, "debit" => 300 "month"=>12} ``` ] In order to do that, I plan to use the aggregation framework. - How do I assign $value to `credit` when $value <= 0 ? - How do I assign $value to `debit` when $value >= 0 ? - How do I group this? I have this: ``` BookKeepingEntry.collection.aggregate( [ { "$match" => { "clinic_id" => self.clinic.id } }, { "$project" => { "credit" => { what here? }, "debit" => { What here?} "month" => { "$month" => "$date" } } }, { "$group" => {} } { "$sort" => { "month" => 1 } } ] ) ```