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?

Can you provide the formula of the `sales`? Because those examples do not help much. — willie17, Apr 01 '18 at 16:41

score 9 · Accepted Answer · answered Apr 02 '18 at 06:10

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

Change `$slice` spec to `$slice: [ "$growths", 1, "$index" ]` to offset the slicing properly. Otherwise nice solution! — Donny Winston, Apr 06 '18 at 17:03
@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 — mickl, Apr 06 '18 at 18:51
@mickl Thanks for the answer !! And can you give any suggestions to me get strengthen in mongodb aggregation? — Tharunkumar Reddy, Apr 08 '18 at 14:02
@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 ! — mickl, Apr 08 '18 at 15:10
@mickl thanks, you're right! I thought a null value would be `$push`ed for a document with no "growth" field, but you're right, it's a 2-element array. Thanks for clarifying! — Donny Winston, Apr 09 '18 at 18:08

Mongodb calculation query--cummulative multiplication

1 Answers1

Linked