MongoDB aggregation: Only keep the running total for the last item in the partition

Question

I have a data like:

[
  { "grp": "A", "seq": 1, "score": 1, x: 0 },
  { "grp": "A", "seq": 1, "score": 2, x: 0 },
  { "grp": "A", "seq": 1, "score": 3, x: 0 },
  { "grp": "A", "seq": 1, "score": 4, x: 0 }
]

Using $setWindowFields,

{
  partitionBy: "$grp",
  sortBy: { seq: 1 },
  output: {
    x: {
      $sum: "$score",
      window: {
        documents: ["unbounded", "current"]
      }
    },
  }
}

I get:

[
  { "grp": "A", "seq": 1, "score": 1, x: 1 },
  { "grp": "A", "seq": 1, "score": 2, x: 3 },
  { "grp": "A", "seq": 1, "score": 3, x: 6 },
  { "grp": "A", "seq": 1, "score": 4, x: 10 }
]

I only need to retain the running sum (x) for the last item of the partition.

[
  { "grp": "A", "seq": 1, "score": 1, x: 0 },
  { "grp": "A", "seq": 1, "score": 2, x: 0 },
  { "grp": "A", "seq": 1, "score": 3, x: 0 },
  { "grp": "A", "seq": 1, "score": 4, x: 10 }
]

I've been testing with $project and $last but I wasn't able to make it work.

What is a better expression or additional stage do I need to use?

Thank you!

Answer 1

Not sure if there is a deterministic sorting in your dataset, but with the same sorting that you are using, you can assign ordering with $documentNumber in your $setWindowFields. Then, compute $rank with the ordering field. The last document will have rank: 1. You can use this with $cond to conditionally set field x

db.collection.aggregate([
  {
    "$setWindowFields": {
      partitionBy: "$grp",
      sortBy: {
        seq: 1
      },
      output: {
        x: {
          $sum: "$score",
          window: {
            documents: [
              "unbounded",
              "current"
            ]
          }
        },
        ordering: {
          $documentNumber: {}
        }
      }
    }
  },
  {
    "$setWindowFields": {
      "partitionBy": "$grp",
      "sortBy": {
        "ordering": -1
      },
      "output": {
        "rank": {
          "$rank": {}
        }
      }
    }
  },
  {
    "$set": {
      "ordering": "$$REMOVE",
      "rank": "$$REMOVE",
      "x": {
        "$cond": {
          "if": {
            $eq: [
              1,
              "$rank"
            ]
          },
          "then": "$x",
          "else": 0
        }
      }
    }
  }
])

Mongo Playground