How to partition the data twice using MongoDB aggregation pipeline? - Stack Overflow

admin管理员组

文章数量:1352128

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.

Sample Data:

[
  {
    "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 group well in the subkey level.

db.collection.aggregate({
  "$setWindowFields": {
    "partitionBy": "$key",
    "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

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

Playground:

Thank you!

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.

Sample Data:

[
  {
    "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 group well in the subkey level.

db.collection.aggregate({
  "$setWindowFields": {
    "partitionBy": "$key",
    "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

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

Playground: https://mongoplayground/p/Zmf0LtKKJrl

Thank you!

• mongodb
• aggregation-framework

Share Improve this question Follow asked Apr 1 at 8:53 Joseph D.Joseph D. 12.2k44 gold badges3939 silver badges7171 bronze badges

Add a comment  | 

2 Answers 2

Sorted by: Reset to default 2

You could use a $group with the two fields provided for the group _id. And then $sum on the amount field:

db.collection.aggregate([
  {
    $group: {
      _id: {
        key: "$key",
        subkey: "$subkey"
      },
      total: { "$sum": "$amount" }
    }
  },
  {
    $sort: { _id: 1 }
  },
  {
    $project: {
      _id: 0,
      key: "$_id.key",
      subkey: "$_id.subkey",
      total: "$total"
    }
  }
])

Mongo Playground

I've added a $sort stage, so that it's ordered by key & subKey. You can also sort on key-subKey after $project like this. If sort is not needed, you can remove that stage.

You can use $group & $project :

// code : 
db.collection.aggregate({
  $group: {
    _id: {
      key: "$key",
      subkey: "$subkey"
    },
    totalAmount: {
      $sum: "$amount"
    }
  }
},
{
  $project: {
    key: "$_id.key",
    subkey: "$_id.subkey",
    total: "$totalAmount",
    _id: 0
  }
})

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

本文标签： How to partition the data twice using MongoDB aggregation pipelineStack Overflow