mongodbmongoosemongodb-queryaggregateaggregate-initialization

Mongo db transaction query


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:

I have created mongo playground:

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


Solution

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