Mongo db transaction query-CodePudding

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?

CodePudding user response：

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] }
    }
}])