Subtract previous value from next in array

[johan_potgieter]

Hi. I have an array of values , i need the aggregation to subtract the next value from the previous one for all the values in the array. I other words subtract value 1 from 2 , then 2 from 3 etc.

[Prasad_Saya]

Hello @johan_potgieter,

You can use the $reduce aggregation array operator for this purpose. This is like using a for-loop and accessing the array elements, subtract one from another, and put each of the subtracted results in another array.

The document with the array: { "_id" : 1, "arr" : [ 12, 32, 88, 1, 76, 359, 90 ] }

The aggregation:

db.test.aggregate([ 
  { 
    $project: {
        result: { 
            $reduce: { 
                input: { $range: [ 1, { $size: "$arr" } ] }, 
                initialValue: [ ], 
                in: {
                    $concatArrays: [ 
                         "$$value", 
                         [ { $subtract: [ 
                                 { $arrayElemAt: [ "$arr", "$$this" ] }, 
                                 { $arrayElemAt: [ "$arr", { $subtract: [ "$$this", 1 ] } ] } 
                         ] } ]	
                    ]
                }
            }
        }
    }
  }
])

The result: { "_id" : 1, "result" : [ 20, 56, -87, 75, 283, -269 ] }

[slava]

Hello, @johan_potgieter!

Let’s solve this by example.
Assume, we have this dataset:

db.test1.insertMany([
  { _id: 'A', values: [4, 1, 6] },
  { _id: 'B', values: [] },
  { _id: 'C', values: [0, 11, 3, 3, 9] },
  { _id: 'D', values: [5] },
  { _id: 'E', }
])

So, if we want to get those results:

[
  {
    "_id" : "A",
    "initialValues" : [ 4, 6 ],
    "calculatedValues" : [ 3, -5 ]
  },
  {
    "_id" : "B",
    "initialValues" : [ ],
    "calculatedValues" : [ ]
  },
  {
    "_id" : "C",
    "initialValues" : [ 0, 11, 3, 3, 9 ],
    "calculatedValues" : [ -11, 8, 0, -6 ]
  },
  {
    "_id" : "D",
    "initialValues" : [ 5 ],
    "calculatedValues" : [ ]
  },
  {
    "_id" : "E"
  }
]

We could use the aggregation like this:

db.test1.aggregate([
  {
    $addFields: {
      result: {
        $reduce: {
          // walk array of $values with $reduce operator
          input: '$values',
          initialValue: {
            prevValue: null,
            calculatedValues: [],
          },
          in: {
            $cond: {
              if: {
                // if we do not know two neighbouring values
                // (first iteration)
                $eq: ['$$value.prevValue', null],
              },
              then: {
                // then we just skip the calculation
                // for current iteration
                prevValue: '$$this',
                calculatedValues: []
              },
              else: {
                // otherwise we know two neighbouring values
                // and it is possible to calculate the diff now
                $let: {
                  vars: {
                    newValue: {
                      // calculate the diff
                      $subtract: ['$$value.prevValue', '$$this'],
                    }
                  },
                  in: {
                    prevValue: '$$this',
                    calculatedValues: {
                      // push the calculated value into array of results
                      $concatArrays: [
                         '$$value.calculatedValues', ['$$newValue']
                      ]
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  },
  {
    // restructure the output documents
    $project: {
      initialValues: '$values',
      calculatedValues: '$result.calculatedValues',
    }
  }
]).toArray();

PS: Also, make sure all your values in ‘values’ prop are of numeric types. Otherwise, consider adding $convert operator to handle different data types.