MongoDB get sum of fields which I have modified in the project pipeline

user_8119044 0
Viewed 23

I want to calculate avgAmount as the sum 3 fields = [balanceLoaded + operatorDueAmount - walletUsage]. But these fields can either be zero or themselves (balanceLoaded, operatorDueAmount, walletUsage) depending on the condition. I want to sum up the final value which is calculated after applying the condition. And yes I need to subtract the walletUsage from other fields. Can someone guide me on how to do that without changing the names of fields in the project pipeline?

const result = await Bus.aggregate(
    [
        {
            $match: { ...filter },
        },
        {
            $project: {
                _id: 1,
                Amount1: '$Amount1',
                Amount2: { $cond: [{ $in: ['example1', dueFieldsConfigs] }, '$Amount2', 0] },
                Amount3: { $cond: [{ $in: ['example2', dueFieldsConfigs] }, '$Amount3', 0] },
                Amount4: { $cond: [{ $in: ['example3', dueFieldsConfigs] }, '$Amount4', 0] },
                avgAmount: { $sum: ['$Amount1', '$Amount2', '$Amount3'] },
            },
        },
        { $sort: sort },
        { $skip: skip },
        { $limit: limit },
    ],
);
aggregation-framework mongodb
1 Answers

You just can't reference the same field names as you're creating them, the easiest way would be to just add another stage after the initial $project stage:

const result = await BusDayWise.aggregate(
    [
        {
            $match: {...filter}
        },
        {
            $project: {
                _id: 1,
                fromDate: "$fromDate",
                busNumber: "$busNumber",
                cardDueAmount: "$cardDueAmount",
                walletUsage: { $cond: [ {$in: ["digital", dueFieldsConfigs] }, "$walletUsage", 0 ]},
                balanceLoaded: { $cond: [ { $in: ["moneyLoaded", dueFieldsConfigs] }, "$balanceLoaded", 0] },
                operatorDueAmount: { $cond: [ { $in: ["contractual", dueFieldsConfigs] }, "$operatorPayout", 0] },
            }
        },
        {
            $addFields: {
                netDueAmount: { $sum: ["$balanceLoaded", "$operatorDueAmount", "$walletUsage"] },
            }
        },
        { $sort: sort },
        { $skip: skip },
        { $limit: limit },
    ]
)

If for whatever reason you want to put it in the same stage you'll have to use the same condition to "calculate" them:

const result = await BusDayWise.aggregate(
    [
        {
            $match: { ...filter },
        },
        {
            $project: {
                _id: 1,
                fromDate: '$fromDate',
                busNumber: '$busNumber',
                cardDueAmount: '$cardDueAmount',
                walletUsage: { $cond: [{ $in: ['digital', dueFieldsConfigs] }, '$walletUsage', 0] },
                balanceLoaded: { $cond: [{ $in: ['moneyLoaded', dueFieldsConfigs] }, '$balanceLoaded', 0] },
                operatorDueAmount: { $cond: [{ $in: ['contractual', dueFieldsConfigs] }, '$operatorPayout', 0] },
                netDueAmount: {
                    $sum: [
                        { $cond: [{ $in: ['digital', dueFieldsConfigs] }, '$walletUsage', 0] },
                        { $cond: [{ $in: ['moneyLoaded', dueFieldsConfigs] }, '$balanceLoaded', 0] },
                        { $cond: [{ $in: ['contractual', dueFieldsConfigs] }, '$operatorPayout', 0] },
                    ],
                },
            },
        },
        { $sort: sort },
        { $skip: skip },
        { $limit: limit },
    ],
);
user_107703700
Related