MongoDB aggregations. Get value differences

user_10767629 0
Viewed 34

I've been struggling with mongo trying to find a solution to show the differences between values.

I have values like this:

[
    {val: 1},
    {val: 4},
    {val: 7},
    {val: 8},
    {val: 11}
]

And I want to receive something like this:

[
    {diff: 3},
    {diff: 3},
    {diff: 1},
    {diff: 3}
]

Every value is evaluated by taking the next one (4) and subtracting the previous one (1). After all this, we receive 3 in output, which is located in the second list as the first item.

Is it possible to achieve it using MongoDB aggregations?

mongodb
1 Answers

You need to group them into array, calculate diff and flatten again.

Pseudocode

//We $group here all values
var _data = [{val: 1}, {val: 4}, ..., {val: 11}]; 
//With $range operator we get nº of items
// We ensure even items, since odd items will return null as last item
var idx  = [0, 1, 2, ..., n]; 
//Here we store diff items with $map operator
var data  = []; 
//$map
for (var i in idx) {
   data[i] = _data[i+1] - _data[i];
}
//$unwind
{data:[0]}, {data[1]}, {data[2]}, ...
//$replaceRoot
{                   
  data:{            {
    diff : 3   -->    diff : 3 
  }                 }
}

Add these steps into your pipeline:

db.collection.aggregate([
  {
    $group: {
      _id: null,
      data: { $push: "$$ROOT" }
    }
  },
  {
    $addFields: {
      data: {
        $map: {
          input: {
            $range: [
              0,
              {
                $subtract: [
                  { $size: "$data" },
                  { $mod: [ { $size: "$data" }, 2 ] }
                ]
              },
              1
            ]
          },
          as: "idx",
          in: {
            diff: {
              $subtract: [
                {
                  $arrayElemAt: [
                    "$data.val",
                    {
                      $add: [ "$$idx", 1 ]
                    }
                  ]
                },
                {
                  $arrayElemAt: [ "$data.val", "$$idx" ]
                }
              ]
            }
          }
        }
      }
    }
  },
  {
    $unwind: "$data"
  },
  {
    $replaceRoot: {
      newRoot: "$data"
    }
  }
])

MongoPlayground

user_37104900
Related