Mongodb multi level aggregation

Question

Data in mongo

[{  
    "_id": "5d71d1432f7c8151c58c4481",
    "payment": {
        "transactions": [
            {
                "_id": "5d71d1ff2f7c8151c58c44cf",
                "method": "paytm",
                "amount": 100,
                "paymentOn": "2019-09-06T03:26:44.959Z"
            }, 
            {
                "_id": "5d71d1ff2f7c8151c58c44ce",
                "method": "cash",
                "amount": 650,
                "paymentOn": "2019-09-06T03:26:55.531Z"
            }
        ],
        "status": "partial"
    },
    "customer": "5d66c434c24f2b1fb6772014",
    "order": {
        "orderNumber": "WP-ORD-06092019-001",
        "total": 770,
        "balance": 20
    }
},
{
    "_id": "5d71d1432f7c8151c58c4481",
    "payment": {
        "transactions": [
            {
                "_id": "5d71d1ff2f7c8151c58c44cf",
                "method": "paytm",
                "amount": 100,
                "paymentOn": "2019-09-06T03:26:44.959Z"
            }
        ],
        "status": "partial"
    },
    "customer": "5d66c434c24f2b1fb6772014",
    "order": {
        "orderNumber": "WP-ORD-06092019-001",
        "total": 200,
        "balance": 100
    }
}]

I want to aggregate payments by method. So the result would look like below:

Output:
Paytm: 200
Cash : 650
Unpaid(Balance): 120

I have tried:

[
  {
    '$unwind': {
      'path': '$payment.transactions', 
      'preserveNullAndEmptyArrays': true
    }
  }, {
    '$project': {
      'amount': '$payment.transactions.amount', 
      'method': '$payment.transactions.method'
    }
  }, {
    '$group': {
      '_id': '$method', 
      'amount': {
        '$sum': '$amount'
      }
    }
  }
] 

But how to include balance calculation as well

Answer 1

Working solution : https://mongoplayground.net/p/7IWELKKMsWe

db.collection.aggregate([
  {
    "$unwind": "$payment.transactions"
  },
  {
    "$group": {
      "_id": "$_id",
      "balance": {
        "$first": "$order.balance"
      },
      "paytm": {
        "$sum": {
          "$cond": [
            {
              "$eq": [
                "$payment.transactions.method",
                "paytm"
              ]
            },
            "$payment.transactions.amount",
            0
          ]
        }
      },
      "cash": {
        "$sum": {
          "$cond": [
            {
              "$eq": [
                "$payment.transactions.method",
                "cash"
              ]
            },
            "$payment.transactions.amount",
            0
          ]
        }
      }
    }
  },
  {
    "$group": {
      "_id": null,
      "balance": {
        "$sum": "$balance"
      },
      "cash": {
        "$sum": "$cash"
      },
      "paytm": {
        "$sum": "$paytm"
      }
    }
  }
])

Answer 2

Using the above dataset, use the aggregate pipeline for calculation using aggregate as:

db.collection.aggregate([
  {
    $facet: {
      paidAmounts: [
        { '$unwind': { 'path': '$payment.transactions', 'preserveNullAndEmptyArrays': true } },
        {
          $group: {
            _id: "$payment.transactions.method",
            amount: {
              $sum: "$payment.transactions.amount"
            }
          }
        }
      ],
      leftAmounts: [
        {
          $group: {
            _id: null,
            balance: {
              $sum: "$order.balance"
            }
          }
        }
      ]
    }
  }
])

giving output: here leftAmounts has left balance and paidAmounts having grouped paid data on basis of payment type

[
  {
    "leftAmounts": [
      {
        "_id": null,
        "balance": 120
      }
    ],
    "paidAmounts": [
      {
        "_id": "cash",
        "amount": 650
      },
      {
        "_id": "paytm",
        "amount": 200
      }
    ]
  }
]