Get cumulative values

Working with Data Drivers & ODMs
, , ,
#1

Hi!

How can I get the accumulated value of one param? I mean I have a query that get documents from two collections. All documents have a parameter quantity and price. Some add quantity and others rest quantity. I have ordered them by date and I’d like to have a new parameter, say accumSoFar, that is the accumulated value of the quantity. Example:

I’m working with node.js. And although my version of mongodb is 4.0.1, which should support $setWindowFields when I try this approach it just fires an error like: unrecognized name $setWindowFields

const arr = [
    {
        sign: '+',
        quantity: 3
    },
    {
        sign: '+',
        quantity: 5
    },
    {
        sign: '2',
        quantity: 2
    }
]

An the end product should be

const arr = [
    {
        sign: '+',
        quantity: 3,
        accumSoFar: 3
    },
    {
        sign: '+',
        quantity: 5,
        accumSoFar: 8
    },
    {
        sign: '-',
        quantity: 2,
        accumSoFar: 6
    }
]```
#2

Hi @kevin_Morte_i_Piferrer,

I’m working with node.js. And although my version of mongodb is 4.0.1, which should support $setWindowFields when I try this approach it just fires an error like: unrecognized name $setWindowFields

You have stated here that your MongoDB version is 4.0.1. The $setWindowFields is new in version 5.0.

 {
        sign: '2',
        quantity: 2
 }

Also, I assume the "sign" value in your example should be '-' instead of '2' but please correct me if I’m wrong in my assumption.

Please see the below example and advise if it works for your scenario.

The example documents:

/// initial documents
testdb>db.collection.find()
[
  { _id: ObjectId("61e4e539dc5507d000395ed8"), sign: '+', quantity: 3 },
  { _id: ObjectId("61e4e539dc5507d000395ed9"), sign: '+', quantity: 5 },
  { _id: ObjectId("61e4e539dc5507d000395eda"), sign: '-', quantity: 2 }
]

Pipeline stages:

///  adding formattedQuantity field
stage1 = {
   $addFields: {
      formattedQuantity:
         {$cond:[{$eq:["$sign","-"]},{$multiply:["$quantity",-1]},"$quantity"]}
         }
   }

/// using $setWindowFields to get the `runningTotal` by adding (using $sum)the values of the "formattedQuantity" field
stage2 = {
      $setWindowFields: {
         sortBy: { sign: 1 },
         output: {
            runningTotal: {
               $sum: "$formattedQuantity",
               window: {
                  documents: [ "unbounded", "current" ]
               }
            }
         }
      }
   }

Result:

testdb> db.collection.aggregate([stage2,stage3])
[
  {
    _id: ObjectId("61e4e539dc5507d000395ed8"),
    sign: '+',
    quantity: 3,
    formattedQuantity: 3,
    runningTotal: 3
  },
  {
    _id: ObjectId("61e4e539dc5507d000395ed9"),
    sign: '+',
    quantity: 5,
    formattedQuantity: 5,
    runningTotal: 8
  },
  {
    _id: ObjectId("61e4e539dc5507d000395eda"),
    sign: '-',
    quantity: 2,
    formattedQuantity: -2,
    runningTotal: 6
  }
]

The "formattedQuantity" field is used in the above example to help display what is occurring at each stage of the pipeline.

Depending on your use case, it may be better to initially add the data as integers which are already positive or negative rather than having a field for the integer and another field for the sign as the formatting stage is no longer required to get the negative integer to be used in the $setWindowFields stage. e.g. The documents inserted as:

{"quantity" : 3},
{"quantity" : 5},
{"quantity" : -2}

Hope this helps.

Regards,
Jason

1 Like