Partition data twice using key and subkey combination

Working with Data
1

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!

2

Maybe just a coincidence but someone with exactly your name and exactly your question asked this on StackOverflow and got two answers, including this one by me. Both result in the “expected output” in your post.

TL;DR: Use $group with the two fields, and no need to use $setWindowFields.

Clarify what additional info or help is needed which isn’t in the answers there.

1 Like
3

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.

4

Using $first isn’t ugly if it’s done correctly. And what operations do you want to do on the other fields which aren’t part of the group by? Will you be using them without changes from just the $first doc in the group?