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

Take a look at the [`$cond`](http://docs.mongodb.org/manual/reference/aggregation/#_S_cond) projection operator. — JohnnyHK, Mar 14 '13 at 14:23
I had asked a similar question and @JohnnyHK had answered it :) http://stackoverflow.com/questions/14102596/conditional-sum-in-mongodb See my answer below. — Aafreen Sheikh, Mar 14 '13 at 14:32

score 11 · Accepted Answer · edited Mar 14 '13 at 15:47

11

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

edited Mar 14 '13 at 15:47

Nerian

15,901
13
66
96

answered Mar 14 '13 at 14:31

Aafreen Sheikh

4,949
6
33
43

Mongodb aggregation framework project with conditions

1 Answers1