Mongo DB交易查询

Question

我正在尝试创建一个MongoDB查询，以查看哪些发票是付款。 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

我创建了Mongo游乐场

• ： 可能的？
• 使用组时如何仍然具有原始对象？
• 还可以用猫鼬创建这个结果吗？

Answer 1

由$ Undind Array和$组组。

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

结果

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

---

编辑

或者没有小组

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