Find balance from debit and credit entries mongodb

Question

I have a Mongo collection like this:

{
    "user_id" : "1",
    "branch_id" : "1",
    "trans_type":"DEBIT",
    "total" : 500
},
{
    "user_id" : "1",
    "branch_id" : "1",
    "trans_type":"CREDIT",
    "total" : 200
},
{
    "user_id" : "1",
    "branch_id" : "3",
    "trans_type":"DEBIT",
    "total" : 1400
},
{
    "user_id" : "2",
    "branch_id" : "1",
    "trans_type":"DEBIT",
    "total" : 100
},
{
    "user_id" : "2",
    "branch_id" : "1",
    "trans_type":"CREDIT",
    "total" : 100
}

The expected output is this:

[
    {
        "user_id":"1",
        "branch_id":"1",
        "final_balance":"300"
    },
    {
        "user_id":"1",
        "branch_id":"3",
        "final_balance":"1400"
    },
    {
        "user_id":"2",
        "branch_id":"1",
        "final_balance":"0"
    }
]

Note that in the output I am looking for the final balance after checking out debit and credit entries per user per branch.

Thank you.

Answer 1

That sounds like a simple $group with a $cond would do the job for you:

db.collection.aggregate({
    $group: {
        "_id": { // group by both fields, "user_id" and "branch_id"
            "user_id": "$user_id",
            "branch_id": "$branch_id"
        },
        "final_balance": {
            $sum: { // calculate the sum of all "total" values
                $cond: {
                    if: { $eq: [ "$trans_type", "DEBIT" ] }, // in case of "DEBIT", we want the stored value for "total"
                    then: "$total", 
                    else: { $multiply: [ "$total", -1 ] } // otherwise we want the stored value for "total" times -1
                }
            }
        }
    }
}, {
    $project: { // this is not really needed unless you specifically need the output format you mentioned in the question
        "_id": 0,
        "user_id": "$_id.user_id",
        "branch_id": "$_id.branch_id",
        "final_balance": "$final_balance",
    }
})

Answer 2

     let docData = await db.Transactions.aggregate(
        [{
            $match: where(any condition)
        },
        {
            $addFields: {
                runningBalance: { $subtract: ['$debit', '$credit'] }
              }
        },
        stage2 = {
            $setWindowFields: {
                sortBy: { transaction_date: 1 },
                output: {
                    runningTotal: {
                        $sum: "$runningBalance",
                        window: {
                            documents: ["unbounded", "current"]
                        }
                    }
                }
            }
        },


        {
            $sort: sortByObj(any sorted by object)
        },
        ]
    );