Mongo db transaction query

Question

I'm trying to create a mongodb query to see which invoices are paid or not. I would like to add few things to the outcome like:

• virtualAmount : original amount - (sum of all creditnotes)
• total paid amount : sum of all transactions where delete is false
• paid (true/false) : if virtualAmount - total paid amount is 0

I have created mongo playground:

https://mongoplayground.net/p/0OyK_bOZu9X

• Anyone know if this is possible?
• How to still have the original object when using group?
• Is it also possible to create this result with mongoose?

Answer 1

by $unwind array and $group it.

db.collection.aggregate(
    [{
        $match: {
            _id: '62b46391be7c618aa5c9bf86'
        }
    }, {
        $set: {
            'transactions': {
                $filter: {
                    'input': '$transactions',
                    'as': 'item',
                    'cond': { $eq: ['$$item.deleted', false] }
                }
            },
        }
    }, {
        $unwind: {
            path: '$transactions'
        }
    }, {
        $group: {
            _id: '_id',
            'total-paid-amount': { $sum: '$transactions.amount.value'},
            //keep to next stage
            'creditnote': {$first: '$creditnote'},
            'original-amount': {$first: '$amount.value'}
        }
    }, {
        $unwind: {
            path: '$creditnote'
        }
    }, {
        $group: {
            _id: '_id',
            'sum-all-creditnotes': {$sum: '$creditnote.amount.value'},
            //keep to next stage
            'total-paid-amount': {$first: '$total-paid-amount'},
            'original-amount': {$first: '$original-amount'}
        }
    }, {
        $addFields: {
            'virtual-amount': {$subtract: ['$original-amount','$sum-all-creditnotes']}
        }
    }, {
        $addFields: {
            paid: {$eq: [{$subtract: ['$virtual-amount','$total-paid-mount']},0]}
        }
    }]

result

{
    "_id" : "_id",
    "sum-all-creditnotes" : 1000,
    "total-paid-amount" : 1000,
    "original-amount" : 3370,
    "virtual-amount" : 2370,
    "paid" : false
}

---

EDIT

or easy way without group

db.collection.aggregate(
[{
    $match: {
        _id: '62b46391be7c618aa5c9bf86'
    }
}, {
    $set: {
        'transactions': {
            $filter: {
                'input': '$transactions',
                'as': 'item',
                'cond': { $eq: ['$$item.deleted', false] }
            }
        },
    }
}, {
    $project:
    {
        'total-paid-amount': { $sum: '$transactions.amount.value' },
        'sum-all-creditnotes': { $sum: '$creditnote.amount.value' },
        'original-amount': '$amount.value'
    }
}, {
    $addFields: {
        'virtual-amount': { $subtract: ['$original-amount', '$sum-all-creditnotes'] }
    }
}, {
    $addFields: {
        'paid': { $eq: [{ $subtract: ['$virtual-amount', '$total-paid-mount'] }, 0] }
    }
}])