mongodb: sum of difference between two fields

Question

I am trying to build a query returning the difference between 2 fields. Here are the documents of my collection:

[
 { _id: 1, type: 'in', amount: 10 },
 { _id: 2, type: 'in', amount: 5 },
 { _id: 3, type: 'out', amount: 7}
]

I am trying to get the balance: total of 'in' amount minus 'out' amount. The output should be something like:

{ ..., total: 8 } ( (10 + 5) - 7 )

This is what I have tried to do:

db.aggregate([
 {
   $group: {
     _id: '$type',
     total: { $sum: '$amount' } 
   }
 }
])

This just output the total per type: in --> 15, out --> 7

How do I in a later stage find the difference of the above result?

Thank you in advance

B. Fleming · Answer 1 · 2019-06-03T23:32:37.293

$group with an _id of null and use $cond to make the value either positive or negative. Specifically, if type is not in, then you can use $multiply to multiply the $total by -1. Something like the following should work:

db.aggregate([
  {
    $group: {
      _id: '$type',
      total: { $sum: '$amount' } 
    }
  },
  {
    $group: {
      _id: null,
      total: { $sum: { $cond: {
        if: { $eq: [ '$_id', 'in']},
        then: '$total',
        else: { $multipy: [ '$total', -1 ]}
      }}}
    }
  }
])

mongodb: sum of difference between two fields

1 Answers1