Amazon DocumentDB中MongoDB $bucket聚合操作符的替代实现问询

迁移至Amazon DocumentDB后，发现该数据库不支持MongoDB的$bucket聚合阶段操作符，现需实现原$bucket配置的分桶统计效果。

原始数据 #

[
  {
    "_id": { "$oid": "6447544a4e512379dee1ced4" },
    "name": "Mike Trout",
    "uniformNumber": 27,
    "team": "Los Angeles Angels",
    "date": "2023/02",
    "totalHomeruns": 333
  },
  {
    "_id": { "$oid": "644755b54e512379dee1ced5" },
    "name": "Shohei Ohtani",
    "uniformNumber": 17,
    "team": "Los Angeles Angels",
    "date": "2023/03",
    "totalHomeruns": 337
  },
  {
    "_id": { "$oid": "644755b54e512379dee1ced6" },
    "name": "Aaron Judge",
    "uniformNumber": 99,
    "team": "New York Yankees",
    "date": "2023/01",
    "totalHomeruns": 295
  },
  {
    "_id": { "$oid": "644755b54e512379dee1ced7" },
    "name": "Mookie Betts",
    "uniformNumber": 11,
    "team": "Los Angeles Dodgers",
    "date": "2023/01",
    "totalHomeruns": 251
  },
  {
    "_id": { "$oid": "644755b54e512379dee1ced8" },
    "name": "Bryce Harper",
    "uniformNumber": 25,
    "team": "Philadelphia Phillies",
    "date": "2023/02",
    "totalHomeruns": 217
  },
  {
    "_id": { "$oid": "644755b54e512379dee1ced9" },
    "name": "Ronald Acuna Jr.",
    "uniformNumber": 23,
    "team": "Atlanta Braves",
    "date": "2023/02",
    "totalHomeruns": 229
  },
  {
    "_id": { "$oid": "644755b54e512379dee1cee1" },
    "name": "Marcus Semien",
    "uniformNumber": 19,
    "team": "Texas Rangers",
    "date": "2023/03",
    "totalHomeruns": 196
  },
  {
    "_id": { "$oid": "644755b54e512379dee1cee2" },
    "name": "Vladimir Guerrero Jr.",
    "uniformNumber": 6,
    "team": "Toronto Blue Jays",
    "date": "2023/03",
    "totalHomeruns": 188
  },
  {
    "_id": { "$oid": "644755b54e512379dee1cee3" },
    "name": "Yordan Alvarez",
    "uniformNumber": 10,
    "team": "Houston Astros",
    "date": "2023/02",
    "totalHomeruns": 137
  },
  {
    "_id": { "$oid": "644755b54e512379dee1cee4" },
    "name": "Andrew Benintendi",
    "uniformNumber": 23,
    "team": "Chicago Whitesox",
    "date": "2023/02",
    "totalHomeruns": 116
  },
  {
    "_id": { "$oid": "644755b54e512379dee1cee5" },
    "name": "Wander Franco",
    "uniformNumber": 33,
    "team": "Tampa Bay Rays",
    "date": "2023/02",
    "totalHomeruns": 97
  }
]

原$bucket配置 #

{
  groupBy: '$totalHomeruns',
  boundaries: [
    0, 95, 105,115,125,135,145,155,165,175,185,195,
    205,215,225,235,245,255,265,275,285,295,
    305,315,325,335,345,355,365,375,385,395,405,
  ],
  default: 'Other',
  output: {
    count: { $sum: 1 },
    playerData: {
      $push: {
        name: '$name',
        uniformNumber: '$uniformNumber',
        team: '$team',
        totalHomeruns: '$totalHomeruns',
        date: '$date'
      }
    }
  }
}

预期结果 #

[
  {
    "_id": 95,
    "count": 1,
    "playerData": [
      {
        "name": "Wander Franco",
        "uniformNumber": 33,
        "team": "Tampa Bay Rays",
        "totalHomeruns": 97,
        "date": "2023/02"
      }
    ]
  },
  {
    "_id": 115,
    "count": 1,
    "playerData": [
      {
        "name": "Andrew Benintendi",
        "uniformNumber": 23,
        "team": "Chicago Whitesox",
        "totalHomeruns": 116,
        "date": "2023/02"
      }
    ]
  },
  {
    "_id": 135,
    "count": 1,
    "playerData": [
      {
        "name": "Yordan Alvarez",
        "uniformNumber": 10,
        "team": "Houston Astros",
        "totalHomeruns": 137,
        "date": "2023/02"
      }
    ]
  },
  {
    "_id": 185,
    "count": 1,
    "playerData": [
      {
        "name": "Vladimir Guerrero Jr.",
        "uniformNumber": 6,
        "team": "Toronto Blue Jays",
        "totalHomeruns": 188,
        "date": "2023/03"
      }
    ]
  },
  {
    "_id": 195,
    "count": 1,
    "playerData": [
      {
        "name": "Marcus Semien",
        "uniformNumber": 19,
        "team": "Texas Rangers",
        "totalHomeruns": 196,
        "date": "2023/03"
      }
    ]
  },
  {
    "_id": 215,
    "count": 1,
    "playerData": [
      {
        "name": "Bryce Harper",
        "uniformNumber": 25,
        "team": "Philadelphia Phillies",
        "totalHomeruns": 217,
        "date": "2023/02"
      }
    ]
  },
  {
    "_id": 225,
    "count": 1,
    "playerData": [
      {
        "name": "Ronald Acuna Jr.",
        "uniformNumber": 23,
        "team": "Atlanta Braves",
        "totalHomeruns": 229,
        "date": "2023/02"
      }
    ]
  },
  {
    "_id": 245,
    "count": 1,
    "playerData": [
      {
        "name": "Mookie Betts",
        "uniformNumber": 11,
        "team": "Los Angeles Dodgers",
        "totalHomeruns": 251,
        "date": "2023/01"
      }
    ]
  },
  {
    "_id": 295,
    "count": 1,
    "playerData": [
      {
        "name": "Aaron Judge",
        "uniformNumber": 99,
        "team": "New York Yankees",
        "totalHomeruns": 295,
        "date": "2023/01"
      }
    ]
  },
  {
    "_id": 325,
    "count": 1,
    "playerData": [
      {
        "name": "Mike Trout",
        "uniformNumber": 27,
        "team": "Los Angeles Angels",
        "totalHomeruns": 333,
        "date": "2023/02"
      }
    ]
  },
  {
    "_id": 335,
    "count": 1,
    "playerData": [
      {
        "name": "Shohei Ohtani",
        "uniformNumber": 17,
        "team": "Los Angeles Angels",
        "totalHomeruns": 337,
        "date": "2023/03"
      }
    ]
  }
]

替代实现方案 #

可以通过$addFields配合$switch标记桶ID，再用$group完成聚合，完全匹配原$bucket的效果：

db.collection.aggregate([
  {
    $addFields: {
      bucket_id: {
        $switch: {
          branches: [
            { case: { $and: [{ $gte: ["$totalHomeruns", 0] }, { $lt: ["$totalHomeruns", 95] }] }, then: 0 },
            { case: { $and: [{ $gte: ["$totalHomeruns", 95] }, { $lt: ["$totalHomeruns", 105] }] }, then: 95 },
            { case: { $and: [{ $gte: ["$totalHomeruns", 105] }, { $lt: ["$totalHomeruns", 115] }] }, then: 105 },
            { case: { $and: [{ $gte: ["$totalHomeruns", 115] }, { $lt: ["$totalHomeruns", 125] }] }, then: 115 },
            { case: { $and: [{ $gte: ["$totalHomeruns", 125] }, { $lt: ["$totalHomeruns", 135] }] }, then: 125 },
            { case: { $and: [{ $gte: ["$totalHomeruns", 135] }, { $lt: ["$totalHomeruns", 145] }] }, then: 135 },
            { case: { $and: [{ $gte: ["$totalHomeruns", 145] }, { $lt: ["$totalHomeruns", 155] }] }, then: 145 },
            { case: { $and: [{ $gte: ["$totalHomeruns", 155] }, { $lt: ["$totalHomeruns", 165] }] }, then: 155 },
            { case: { $and: [{ $gte: ["$totalHomeruns", 165] }, { $lt: ["$totalHomeruns", 175] }] }, then: 165 },
            { case: { $and: [{ $gte: ["$totalHomeruns", 175] }, { $lt: ["$totalHomeruns", 185] }] }, then: 175 },
            { case: { $and: [{ $gte: ["$totalHomeruns", 185] }, { $lt: ["$totalHomeruns", 195] }] }, then: 185 },
            { case: { $and: [{ $gte: ["$totalHomeruns", 195] }, { $lt: ["$totalHomeruns", 205] }] }, then: 195 },
            { case: { $and: [{ $gte: ["$totalHomeruns", 205] }, { $lt: ["$totalHomeruns", 215] }] }, then: 205 },
            { case: { $and: [{ $gte: ["$totalHomeruns", 215] }, { $lt: ["$totalHomeruns", 225] }] }, then: 215 },
            { case: { $and: [{ $gte: ["$totalHomeruns", 225] }, { $lt: ["$totalHomeruns", 235] }] }, then: 225 },
            { case: { $and: [{ $gte: ["$totalHomeruns", 235] }, { $lt: ["$totalHomeruns", 245] }] }, then: 235 },
            { case: { $and: [{ $gte: ["$totalHomeruns", 245] }, { $lt: ["$totalHomeruns", 255] }] }, then: 245 },
            { case: { $and: [{ $gte: ["$totalHomeruns", 255] }, { $lt: ["$totalHomeruns", 265] }] }, then: 255 },
            { case: { $and: [{ $gte: ["$totalHomeruns", 265] }, { $lt: ["$totalHomeruns", 275] }] }, then: 265 },
            { case: { $and: [{ $gte: ["$totalHomeruns", 275] }, { $lt: ["$totalHomeruns", 285] }] }, then: 275 },
            { case: { $and: [{ $gte: ["$totalHomeruns", 285] }, { $lt: ["$totalHomeruns", 295] }] }, then: 285 },
            { case: { $and: [{ $gte: ["$totalHomeruns", 295] }, { $lt: ["$totalHomeruns", 305] }] }, then: 295 },
            { case: { $and: [{ $gte: ["$totalHomeruns", 305] }, { $lt: ["$totalHomeruns", 315] }] }, then: 305 },
            { case: { $and: [{ $gte: ["$totalHomeruns", 315] }, { $lt: ["$totalHomeruns", 325] }] }, then: 315 },
            { case: { $and: [{ $gte: ["$totalHomeruns", 325] }, { $lt: ["$totalHomeruns", 335] }] }, then: 325 },
            { case: { $and: [{ $gte: ["$totalHomeruns", 335] }, { $lt: ["$totalHomeruns", 345] }] }, then: 335 },
            { case: { $and: [{ $gte: ["$totalHomeruns", 345] }, { $lt: ["$totalHomeruns", 355] }] }, then: 345 },
            { case: { $and: [{ $gte: ["$totalHomeruns", 355] }, { $lt: ["$totalHomeruns", 365] }] }, then: 355 },
            { case: { $and: [{ $gte: ["$totalHomeruns", 365] }, { $lt: ["$totalHomeruns", 375] }] }, then: 365 },
            { case: { $and: [{ $gte: ["$totalHomeruns", 375] }, { $lt: ["$totalHomeruns", 385