Mongodb aggregation framework project with conditions

Question

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 } }
    ]
  )

Answer 1

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