Apr 4
2 / 3
Apr 4
4d ago
Joseph_D
7d

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!

aneroidAnirudh Dutt
6d

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.

Joseph_D
4d

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.


Want to read more? Browse other topics in Working with Data or view latest topics.