Mongodb calculation query--cummulative multiplication

Question

I recently started working in Mongodb for POC. I have one json collection below

db.ccpsample.insertMany([
  {
    "ccp_id":1,
    "period":601,
    "sales":100.00
  },
  {
    "ccp_id":1,
    "period":602,
    "growth":2.0,
    "sales":"NULL"    ##sales=100.00*(1+(2.0/100)) -- 100.00 comes from(ccp_id:1 and period=601) 
  },
  {
    "ccp_id":1,
    "period":603,
    "growth":3.0,
    "sales":"NULL"   ##sales=100.00*(1+(2.0/100))**(1+(3.0/100))-- 100.00 comes from(ccp_id:1 and period=601) 2.0 comes from (ccp_id:2 and period=602)  
  },
  {
    "ccp_id":2,
    "period":601,
    "sales":200.00
  },
  {
    "ccp_id":2,
    "period":602,
    "growth":2.0,
    "sales":"NULL"   ##sales=200.00*(1+(2.0/100))
  },
  {
    "ccp_id":2,
    "period":603,
    "growth":3.0,
    "sales":"NULL"   ##same like above
  }
])

And i need to calculate sales field which has NULL by using above documents with matching conditions of ccp_id should same and period field should be equal to 601. I have added a line to demonstrate calculation of sales field in collection itself above. I tried with $graphlookup but no luck. Can you people kindly help or suggest some way?

Counterchange

Can you provide the formula of the sales? Because those examples do not help much. – Mckenziemckeon

Answer 1

You can use below aggregation:

db.ccpsample.aggregate([
  { $sort: { ccp_id: 1, period: 1 } },
  { 
    $group: {
      _id: "$ccp_id",
      items: { $push: "$$ROOT" },
      baseSale: { $first: "$sales" },
      growths: { $push: "$growth" }
    }
  },
  {
    $unwind: {
      path: "$items",
      includeArrayIndex: "index"
    }
  },
  {
    $project: {
      cpp_id: "$items.cpp_id",
      period: "$items.period",
      growth: "$items.growth",
      sales: {
        $cond: {
          if: { $ne: [ "$items.sales", "NULL" ] },
          then: "$items.sales",
          else: {
            $reduce: {
              input: { $slice: [ "$growths", "$index" ] },
              initialValue: "$baseSale",
              in: { $multiply: [ "$$value", { $add: [1, { $divide: [ "$$this", 100 ] }] } ] }
            }
          }
        }
      }
    }
  }
])

Basically to calculate the value for n-th element you have to know following things:

• sales value of first element ($first in $group)
• the array of all growths ($push in $group)
• the n which indicates how many multiplications you have to perform

To calculate the index you should $push all elements into one array and then use $unwind with includeArrayIndex option which will insert the index of unwinded array to field index.

Last step calculates the cumulative multiplication. It uses $slice with index field to evaluate how many growths should be processed. So there will be one element for 601, two elements for 602 and so on.

Then it's time for $reduce to process that array and perform the multiplications based on your formula: (1 + (growth/100))

Campuzano

Change $slice spec to $slice: [ "$growths", 1, "$index" ] to offset the slicing properly. Otherwise nice solution! – Jordanna

@DonnyWinston I can't do it. growths is a 2 element array in both groups since there is no growth for first document in each group so no need to have an offset – Campuzano

@Campuzano Thanks for the answer !! And can you give any suggestions to me get strengthen in mongodb aggregation? – Counterchange

@Buddi I believe that writing code is the best way to learn programming :) In this case MongoDB has really useful docs. Thanks for really interesting question ! – Campuzano

@Campuzano thanks, you're right! I thought a null value would be $pushed for a document with no "growth" field, but you're right, it's a 2-element array. Thanks for clarifying! – Jordanna