MongoDB Aggregation group _id by different fields

Question

I have collection accounts:

{
  "_id": ObjectId("571a0e145d29024733793cfe"),
  "name": "Cash",
  "user": ObjectId("571a0e145d29024733793cfc")
}

and transactions. Transaction can have a different fields:

{
  "_id": ObjectId("571a0e145d29024733793d06"),
  "amount": 100,
  "type": "earned",
  "account": ObjectId("571a0e145d29024733793cfe"),
  "user": ObjectId("571a0e145d29024733793cfc"),

},
{
  "_id": ObjectId("571a0e145d29024733793d04"),
  "amount": 300,
  "type": "spent",
  "account": ObjectId("571a0e145d29024733793cfe"),
  "user": ObjectId("571a0e145d29024733793cfc")
},
{
  "_id": ObjectId("571a0e145d29024733793d07"),
  "amount": 100,
  "type": "transfer",
  "sourceAccount": ObjectId("571a0e145d29024733793cfd"),
  "destinationAccount": ObjectId("571a0e145d29024733793cfe"),
  "user": ObjectId("571a0e145d29024733793cfc"),
}

I want to make a group for statistics by each account. I wrote a aggregation framework query to the database:

db.transactions.aggregate([

  { $match: { user: user._id } },

  {
    $group: {
      _id: '$account',

      earned: {
        $sum: {
          $cond: [{ $eq: ['$type', 'earned'] }, '$amount', 0]
        }
      },

      spent: {
        $sum: {
          $cond: [{ $eq: ['$type', 'spent'] }, '$amount', 0]
        }
      },

      deposits: {
        $sum: {
          $cond: [{ $eq: ['$type', 'transfer'] }, '$amount', 0]
        }
      },

      withdrawal: {
        $sum: {
          $cond: [{ $eq: ['$type', 'transfer'] }, '$amount', 0]
        }
      },

      maxEarned: {
        $max: {
          $cond: [{ $eq: ['$type', 'earned'] }, '$amount', 0]
        }
      },

      maxSpent: {
        $max: {
          $cond: [{ $eq: ['$type', 'spent'] }, '$amount', 0]
        }
      },

      count: { $sum: 1 }

    }
  }
]);

But it don't work properly. It works only for transactions with field account. I want to group by a field account or sourceAccount or destinationAccount.

I also tried write in "_id" field:

_id: { account: '$account', sourceAccount: '$sourceAccount', destinationAccount: '$destinationAccount' }

or

_id: {$or: ['$account', '$sourceAccount', '$destinationAccount']}

or

_id: {$in: ['$account', '$sourceAccount', '$destinationAccount']}

But it make wrong group or don't work.

How can I group from different fields?

Answer 1

Use the $cond operator as the _id expression to evaluate the group by key given the conditions you specified. The $cond operator uses the comparison operator $gt to evaluate the boolean expression which determines whether the field exists and uses this comparison order. You could thus restructure your pipeline as in the following example:

db.transactions.aggregate([
    {
        "$group": {
            "_id": { 
                "$cond": [
                    { "$gt": [ "$account", null ] }, 
                    "$account", 
                    { 
                        "$cond": [
                            { "$gt": [ "$sourceAccount", null ] }, 
                            "$sourceAccount", 
                            "$destinationAccount" 
                        ] 
                    } 
                ] 
            },
            "count": { "$sum": 1 }
        }
    }
])

Sample output

{ "_id" : ObjectId("571a0e145d29024733793cfd"), "count" : 1 }
{ "_id" : ObjectId("571a0e145d29024733793cfe"), "count" : 2 }