mongodbmongodb-queryaggregation-frameworkpymongostudio3t

How to perform lead and lag in MongoDB


I am using STudio 3T and I have query like this:

select [Dashbo],lead([Dashbo]) over(order by [Entered Date])
from ATest_prevback;

This is giving me and error. How to perform this in MongoDB? Can someone give me an example?

Thanks, Adi


Solution

  • Starting from MongoDB v5.0+, it can be done by using $shift in $setWindowFields.

    db.collection.aggregate([
      {
        "$setWindowFields": {
          "partitionBy": null,
          "sortBy": {
            "entered_date": 1
          },
          "output": {
            lag: {
              $shift: {
                output: "$Dashbo",
                by: -1,
                default: "Not available"
              }
            },
            lead: {
              $shift: {
                output: "$Dashbo",
                by: 1,
                default: "Not available"
              }
            }
          }
        }
      }
    ])
    

    Here is the Mongo playground for your reference.