How to do operations to a document other than the current one in MongoDB's setWindowFields

Working with Data
1

I have a MongoDB collection like this:

[
  {
    "_id": 1,
    "price": 1
  },
  {
    "_id": 2,
    "price": 2
  },
  {
    "_id": 3,
    "price": 3
  },
  {
    "_id": 4,
    "price": 4
  },
  {
    "_id": 5,
    "price": 5
  },
  {
    "_id": 6,
    "price": 6
  }
]

I want to calculate standard deviation myself (I know there’s a built in operator but I want to change some parameters, so implementing it myself).

I calculated the running mean, but how do use the last mean in a setWindowFields stage:

const aggregation1 = [
    {
        $setWindowFields: {
            sortBy: {
                _id: 1
            },
            output: {
                mean: {
                    $avg: "$price",
                    window: {
                        documents: [-4, 0]
                    }
                }
            }
        }
    },
    {
        $setWindowFields: {
            sortBy: {
                _id: 1
            },
            output: {
                field_new: {
                    $sum: [
                        "$price",
                        { $last: "$mean" } //Gives error
                    ],
                    window: {
                        documents: [-4, 0]
                    }
                }
            }
        }
    }
];
db.collection.aggregate(aggregation);

I’m looking to perform an operation on each price field in a document (sum), with the last mean.
e.g. x1 + mean at x5 , x2 + mean at x5, … , x6 + mean at x10, x7 + mean at x10, …

Like we do in a standard deviation formula: Summation of square of difference between price and average price.

Here’s how the expected output should look like:

[
    {
      "_id": 1,
      "price": 1
    },
    {
      "_id": 2,
      "price": 2
    },
    {
      "_id": 3,
      "price": 3
    },
    {
      "_id": 4,
      "price": 4
    },
    {
      "_id": 5,
      "price": 5,
      "field_new": 8 // 5 + 3 (3=(1+2+3+4+5)/5 mean from last 5 docs)
    },
    {
      "_id": 6,
      "price": 6,
      "field_new": 10 // 6 + 4 (4=(2+3+4+5+6)/5 mean from last 5 docs)
    }
]
2

Hi @Anuj_Agrawal,

Apologies as I’m still trying to understand the calculations explained but briefly scanning your expected output, would using an $addFields to calculate field_new after the initial setWindowFields is used (to calculate the running mean) work for you? Based off the expected output you had provided, field_new just appears to be a sum of the current price and running mean value. Is this correct?

Pipeline used in my test environment:

sd>const a =
[
  {
    '$setWindowFields': {
      sortBy: { _id: 1 },
      output: {
        mean: { '$avg': '$price', window: { documents: [ -4, 0 ] } }
      }
    }
  },
  {
    '$addFields': { field_new: { '$sum': [ '$price', '$mean' ] } }
  },
  { '$project': { _id: 1, price: 1, field_new: 1 } }
]

Output:

sd>db.collection.aggregate(a)
[
  { _id: 1, price: 1, field_new: 2 },
  { _id: 2, price: 2, field_new: 3.5 },
  { _id: 3, price: 3, field_new: 5 },
  { _id: 4, price: 4, field_new: 6.5 },
  { _id: 5, price: 5, field_new: 8 },
  { _id: 6, price: 6, field_new: 10 }
]

I used a $project at the end to get it closer to your expected output but I believe the $addFields and $project could probably be combined :slight_smile:

Look forward to hearing from you.

Regards,
Jason