How to declare and update Global variables in mongodb aggregate

[Owais_Ahmed]

I want to change the algorithm code written in JS to Mongodb aggregate for better performance and scope. MongoDB provides some custom powerful features like $function, $accumulator which can solve my global variables issue but unfortuantly digital ocean mongodb does not allow server scide scripting so i have to use built in aggregate stages for this.

This is the Algorithm written in JS:-

 // make new algo with capabilities of carry forward reserver advance & payable ---------

  let calcAdvance = 0; //holds calculated advance
  let adReserve = 0; //holds reserve advance
  let prevPayable = 0; //holds arreras

  let algo = (bills || []).forEach((item, idx) => {
    if (item.bill === "ADVANCE_PAYMENT") {
      calcAdvance += parseFloat(item.fromAdvance); //10000
      if (prevPayable && prevPayable > adReserve + calcAdvance) {
        //1000
        prevPayable -= adReserve + calcAdvance;
        calcAdvance = 0;
        adReserve = 0;
      } else {
        calcAdvance += adReserve - prevPayable;
        prevPayable = 0;
        adReserve = 0;
      }
      //----------------------------------------
      totalAdvance += parseFloat(item.fromAdvance);
      //----------------------------------------
    }
    if (item.bill === "INVOICE" && item.type !== "OPD") {
      //-----------------------------------------
      fullGrandTotal += parseFloat(item.payable);
      //-----------------------------------------
      if (adReserve > 0) {
        adReserve += calcAdvance;
        item.currentAdvance = adReserve;
        //check for advance payment reserved or left
        if (adReserve > parseFloat(item.payable)) {
          adReserve = adReserve - parseFloat(item.payable); //change grandtotal to payNow
        } else {
          prevPayable = 0 + parseFloat(item.payable) - adReserve; //change grandtotal to payNow
          adReserve = 0;
        }
        calcAdvance = 0;
      } else {
        item.currentAdvance = calcAdvance;
        //set totalPrevPayable to next invoice
        item.prevPayable = prevPayable; // new addition set prevPayable to next invoice

        if (calcAdvance > parseFloat(item.payable) + prevPayable) {
          //new addition add prevPayable
          adReserve = calcAdvance - (parseFloat(item.payable) + prevPayable);
          prevPayable = 0;
        } else {
          prevPayable += parseFloat(item.payable) - calcAdvance;
        }
        calcAdvance = 0;
      }
    }
  });

And this is the aggregate Code:-
i had alittle experiments using map and reduce

  const bills = await Bill.aggregate([
    {
      $addFields: {
        patient: { $toString: "$patient" },
      },
    },
    {
      $match: {
        patient: patientId,
      },
    },
    {
      $lookup: {
        from: "users",
        foreignField: "_id",
        localField: "author",
        as: "author",
      },
    },
    {
      $lookup: {
        from: "addmissions",
        foreignField: "_id",
        localField: "addmission",
        as: "addmission",
      },
    },
    {
      $lookup: {
        from: "invoices",
        let: { invoiceId: "$invoice" },
        pipeline: [{ $match: { $expr: { $eq: ["$_id", "$$invoiceId"] } } }],
        as: "invoice",
      },
    },
    {
      $lookup: {
        from: "advancepayments",
        let: { advancePaymentId: "$advancePayment" },
        pipeline: [
          { $match: { $expr: { $eq: ["$_id", "$$advancePaymentId"] } } },
        ],
        as: "advancePayment",
      },
    },
    {
      $addFields: {
        author: { $arrayElemAt: ["$author", 0] },
        addmission: { $arrayElemAt: ["$addmission", 0] },
        advancePayment: { $arrayElemAt: ["$advancePayment", 0] },
        invoice: { $arrayElemAt: ["$invoice", 0] },
      },
    },

    /* -------------------------------CALCULATIONS---------------------------------- */
    {
      $group: {
        _id: null,
        payments: {
          $push: {
            $cond: [
              { $eq: ["$bill", "ADVANCE_PAYMENT"] },
              {
                type: "ADVANCE_PAYMENT",
                amount: "$advancePayment.totalAmount",
              },
              { type: "INVOICE", amount: "$invoice.payable" },
            ],
          },
        },
        bills: { $push: "$$ROOT" },
      },
    },
    {
      $addFields: {
        finalPayments: {
          $reduce: {
            input: "$payments",
            initialValue: { total: 0, result: [] },
            in: {
              total: {
                $cond: [
                  { $eq: ["$$this.type", "ADVANCE_PAYMENT"] },
                  { $subtract: ["$$value.total", "$$this.amount"] },
                  { $add: ["$$value.total", "$$this.amount"] },
                ],
              },
              result: {
                $concatArrays: [
                  "$$value.result",
                  [{ $cond: ["$$this.type", "ADVANCE_PAYMENT", "$$total"] }],
                ],
              },
            },
          },
        },
      },
    },
    /* -------------------------------CALCULATIONS---------------------------------- */
  ]);

[slava]

Hello, @Owais_Ahmed ! Welcome to the community!

It clear, that you’re moving logic from your node.js code to the aggregation pipeline. But what is the global variables issue that you have?

@Owais_Ahmed

MongoDB provides some custom powerful features like $function, $accumulator which can solve my global variables issue

Can you give more details on this?

[Owais_Ahmed]

Hey @slava Thank you for your time. So what i want is to change this js code algorithm to aggregate so in aggregate i can iterate over all the bills and do calcuations but their is varaible updating problem. so like when their advance payment i would like to store this value in a global variable so that i can pass its value to the next bill and now if the next bill is also advance payment now i would add the passed advanceAmount and current advance amount and now if the next 3rd bill is invoice so i would subtract them from each other now imagine that after subtracting their is still invoice amount so what i would do i would save it in global variable as previous payable amount and pass it to next bill.

Visual Example:

[slava]

I have built an aggregation pipeline example of how that can be done using $reduce, $cond and some arithmetic pipeline operators. Please, read thoroughly the code and comments of my example, read the documentation for relevant operators, if needed and try to apply the solution to your own code. Then, return with more questions, if you have any

db.aggregate([
  { 
    // imagine your aggregation pipeline have 
    // these sample documents at some point
    $documents: [ 
      {
        billId: 'B1',
        type: 'advancePayment',
        amount: 500
      },
      {
        billId: 'B2',
        type: 'advancePayment',
        amount: 500
      },
      {
        billId: 'B3',
        type: 'invoice',
        amount: 2000
      }
    ] 
  },
  {
    // you need to gather them in an array
    // 'bills' in this case
    $group: {
      _id: null,
      bills: {
        $push: {
          type: '$type',
          amount: '$amount',
        }
      }
    }
  },
  {
    $addFields: {
      calculated: {
        $reduce: {
          input: '$bills',
          initialValue: {
            // initialize your variables here
            // later they can be accessted with $$value (see code below)
            previousPayable: 0,
            reserveAdvance: 0,
          },
          in: {
            // manipulate 'reserveAdvance' for each bill, represented as $$this
            reserveAdvance: {
              $cond: {
                if: {
                  // if bill.type === 'advancePayment'
                  $eq: ['$$this.type', 'advancePayment']
                },
                then: {
                  // then add its amount to reserveAdvance calculated variable
                  $add: ['$$value.reserveAdvance', '$$this.amount'],
                },
                else: {
                  // nested condition (short syntax of $cond)
                  $cond: [
                    { 
                      // if invoice amount is greater than calculated advancePayment
                      $gt: ['$$this.amount', '$$value.advancePayment']
                    },
                    // then assing zero to advancePayment
                    0,
                    // else write to advancePayment what is left
                    { $subtract: ['$$value.advancePayment', '$$this.amount'] },
                  ]
                },
              },
            },
            // manipulate 'previousPayable' for each bill, represented as $$this
            previousPayable: {
              $cond: {
                if: {
                  // if bill.type === 'invoice'
                  $eq: ['$$this.type', 'invoice']
                },
                then: {
                  // then subtract calculated reserveAdvance amount from it
                  $subtract: ['$$this.amount', '$$value.reserveAdvance'],
                },
                // else reuturn previousPayable unchanged
                else: '$$value.previousPayable'
              }
            }
          }
        }
      }
    }
  }
]);

Output of the pipeline:

[
  {
    _id: null,
    bills: [
      { type: 'advancePayment', amount: 500 },
      { type: 'advancePayment', amount: 500 },
      { type: 'invoice', amount: 2000 }
    ],
    calculated: { reserveAdvance: 0, previousPayable: 1000 }
  }
]

[Owais_Ahmed]

Hey @slava Thank you, man I just have one more query How can add the respective calculated amount to its bill?
Here is an example. Sorry for this at first I missed but I need this in this manner.No hurry just reply when you are free Thank you, man,