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

Question

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)
    }
]

Why are you not using the build-in function? They also support `window` clause. — Wernfried Domscheit, Apr 24 '23 at 07:21
I'm looking to change some parameters in calculating std dev, like for calculating exponential deviation, etc. So, want to implement std deviation myself to start with. — AnujGeek, Apr 24 '23 at 07:22

nimrod serok · Accepted Answer · 2023-04-24T08:14:29.620

4

EDIT: for the updated question with the expected output:

db.collection.aggregate([
  {$setWindowFields: {
      sortBy: {_id: 1},
      output: {
        mean: {
          $push: "$price",
          window: {documents: [-N, 0]}
        }
      }
  }},
  {$set: {
      mean: "$$REMOVE",
      field_new: {
        $cond: [
          {$gt: [{$size: "$mean"}, N]},
          {$add: ["$price", {$avg: "$mean"}]},
          "$$REMOVE"
        ]
      }
  }}
])

See how it works on the playground example

** For the original question: ** One option is to add another $setWindowFields step with an opposite direction sorting:

db.collection.aggregate([
  {$setWindowFields: {
      sortBy: {_id: 1},
      output: {
        mean: {
          $avg: "$price",
          window: {documents: [-4, 0]}
        }
      }
  }},
  {$setWindowFields: {
      sortBy: {_id: -1},
      output: {
        lastMean: {
          $first: "$mean",
          window: {documents: ["unbounded", "current" ]}
        }
      }
  }}
])

See how it works on the playground example

edited Apr 24 '23 at 08:14

answered Apr 24 '23 at 06:55

nimrod serok

14,151
2
11
33

Thanks for the comment, but 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, ... Like we do in a standard deviation formula: Summation of square of difference between price and average price – AnujGeek Apr 24 '23 at 07:14
Is [this](https://mongoplayground.net/p/rt2N9keaKVk) what you are looking for? If not, can you provide the expected results? – nimrod serok Apr 24 '23 at 07:31
Hi @nimrod, I've updated my question with an expected output. – AnujGeek Apr 24 '23 at 07:56
Updated the answer accordingly – nimrod serok Apr 24 '23 at 08:15
Thank you so much, this is what I wanted! Any way to optimize this? – AnujGeek Apr 24 '23 at 08:27
If N is large you can replace the `$push` with 2 fields, `average` and `count` – nimrod serok Apr 24 '23 at 08:31

score 1 · Answer 2 · answered Apr 24 '23 at 06:56

Are you looking for this:

db.collection.aggregate([
   {
      $setWindowFields: {
         sortBy: { _id: 1 },
         output: {
            mean: {
               $avg: "$price",
               window: { documents: [-4, 0] }
            }
         }
      }
   },
   {
      $setWindowFields: {
         sortBy: { _id: 1 },
         output: {
            sum: {
               $sum: "$price",
               window: { documents: [-4, 0] }
            },
            last_mean: {
              $last: "$mean",
               window: { documents: [-4, 0] }
            }
         }
      }
   }
])

Thanks for the comment, but 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, ... Like we do in a standard deviation formula: Summation of square of difference between price and average price — AnujGeek, Apr 24 '23 at 07:14

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

2 Answers2