Partition data twice using key and subkey combination

I have a sample data that I need to partition using two keys: key and subkey . Based on the grouping, the amount will be summed up.

[
  {
    "key": "1",
    "subkey": "1_1",
    "amount": 1
  },
  {
    "key": "1",
    "subkey": "1_1",
    "amount": 2
  },
  {
    "key": "1",
    "subkey": "1_2",
    "amount": 5
  },
  {
    "key": "2",
    "subkey": "2_1",
    "amount": 3
  },
  {
    "key": "2",
    "subkey": "2_2",
    "amount": 4
  }
]

My current aggregation pipeline doesn’t partition well in the subkey level.

db.collection.aggregate({
  "$setWindowFields": {
    "partitionBy": {
      "key": "$key",
      "subkkey": "$subkey"
    },
    "sortBy": {
      "subkey": 1
    },
    "output": {
      "total": {
        "$sum": "$amount",
        "window": {
          "documents": [
            "unbounded",
            "current"
          ]
        }
      }
    }
  }
})

Expected output:

key | subkey | total
 1  | 1_1    | 3
 1  | 1_2    | 5
 2  | 2_1    | 3
 2  | 2_2    | 4

What is the best way to do this? Do I need to combine $setWindowFields with other operators?

Thanks in advance!

Hey @aneroid, appreciate your response. Not exactly the same question.

In SO:

Any suggestions on the best way to do two-level partition aside from using $setWindowFields ?

In here:

Do I need to combine $setWindowFields with other operators?

You see when using $group, you need to do use $first accumulator. It’s a bit “ugly” especially when the document has a lot of fields that should be retained for the next stage.

$setWindowFields on the other hand retains the rest of the document fields for the next stage.

Thus, I was thinking if it’s possible to do it using two-stage sliding window or any other operators I can combine.

Thanks in advance.