Access to previous document in MongoDB aggregation result

user_5380897 0
Viewed 1589

The question is how I can refer to the previous document from the result set?
I have an aggregation with such result:

var result = db.collection.aggregate([...])

{
    "_id" : "2018-01",
    "month" : "2018-01",
    "count" : 283.0
}
{
    "_id" : "2018-02",
    "month" : "2018-02",
    "count" : 260.0
}
{
    "_id" : "2018-03",
    "month" : "2018-03",
    "count" : 287.0
}
{
    "_id" : "2018-04",
    "month" : "2018-04",
    "count" : 264.0
}
{
    "_id" : "2018-05",
    "month" : "2018-05",
    "count" : 292.0
}

The goal is to get the difference between count in this month and count in the previous one. So to have results like this:

{
    "_id" : "2018-01",
    "month" : "2018-01",
    "count" : 283.0,
    "difference" : 283.0
}
{
    "_id" : "2018-02",
    "month" : "2018-02",
    "count" : 260.0,
    "difference" : -23.0
}
{
    "_id" : "2018-03",
    "month" : "2018-03",
    "count" : 287.0,
    "difference" : 17.0
}
mongodb window-functions aggregate
1 Answers

It would be easier to help if you'd supply the document scheme and the aggregation you use as we might be able to do it "dynamically" through out the process.

With that said we can just add 2 steps to the end of your current aggregation.

First (assuming your results aren't in an array) we'll group them so we can iterate over the array:

{
  $group: {
    _id: null,
    temp: {$push: "$$ROOT"}
  }
},
{ 
   "$project" : {
       _id: 0,
       "temp_field" : {
            "$reduce" : {
                "input" : "$temp", 
                 "initialValue" : {
                     "prev" : 0.0, 
                     "results" : []
                 }, 
                 "in" : {
                    "prev" : "$$this.count", 
                     "results" : {
                        "$concatArrays" : ["$$value.results", 
                                    [
                                        {
                                            "month" : "$$this.month", 
                                            "_id" : "$$this._id"
                                            "count" : "$$this.count", 
                                            "diff" : {
                                                "$subtract" : [
                                                    "$$this.count", 
                                                    "$$value.prev"
                                                ]
                                            }
                                        }
                                    ]
                                ]
                            }
                        }
                    }
                }
            }
        },

Finally we just need to "restore" the old result format:

{
   $unwind: "$temp_field"
},
{
   $project: {
      _id: "$temp_field.results._id",
      count: "$temp_field.results.count",
      month: "$temp_field.results.month",
      difference: "$temp_field.results.diff"
   }
}

*** Note: I'm not actually calculating differences correctly (match a month to a previous month by value), meaning if you have missing months you should take care of it before hand. as well as sort the array by date ahead of time as i just use the previous in array to do the subtraction.

user_107703700
Related