Spring Boot实现MongoDB数据按星期分组及字段出现次数统计

给定的MongoDB集合数据如下：

[
  {"_id" : ObjectId("6406e56d18ab204cfe6b7915"), "timeStamp" : ISODate("2023-03-07"), "filterName" : "ABC1"},
  {"_id" : ObjectId("6406e56d18ab204cfe6b7916"), "timeStamp" : ISODate("2023-03-07"), "filterName" : "ABC2"},
  {"_id" : ObjectId("6406e56d18ab204cfe6b7917"), "timeStamp" : ISODate("2023-03-07"), "filterName" : "ABC2"},
  {"_id" : ObjectId("6406e56d18ab204cfe6b7918"), "timeStamp" : ISODate("2023-03-06"), "filterName" : "ABC3"},
  {"_id" : ObjectId("6406e56d18ab204cfe6b7919"), "timeStamp" : ISODate("2023-03-05"), "filterName" : "ABC3"}
]

（注：原数据中所有_id重复，这里修正为不同值以避免数据库报错）

要实现按星期分组并统计各filterName的出现次数，可使用以下MongoDB聚合查询：

db.collection.aggregate([
  // 1. 将timeStamp转换为中文星期名称
  {
    $addFields: {
      weekDay: {
        $switch: {
          branches: [
            { case: { $eq: [{ $dayOfWeek: "$timeStamp" }, 1] }, then: "周日" },
            { case: { $eq: [{ $dayOfWeek: "$timeStamp" }, 2] }, then: "周一" },
            { case: { $eq: [{ $dayOfWeek: "$timeStamp" }, 3] }, then: "周二" },
            { case: { $eq: [{ $dayOfWeek: "$timeStamp" }, 4] }, then: "周三" },
            { case: { $eq: [{ $dayOfWeek: "$timeStamp" }, 5] }, then: "周四" },
            { case: { $eq: [{ $dayOfWeek: "$timeStamp" }, 6] }, then: "周五" },
            { case: { $eq: [{ $dayOfWeek: "$timeStamp" }, 7] }, then: "周六" }
          ],
          default: "未知"
        }
      }
    }
  },
  // 2. 按星期和filterName分组，统计出现次数
  {
    $group: {
      _id: { weekDay: "$weekDay", filter: "$filterName" },
      count: { $sum: 1 }
    }
  },
  // 3. 按星期重新分组，整理每个filter的统计结果
  {
    $group: {
      _id: "$_id.weekDay",
      countOccurance: {
        $push: {
          $arrayToObject: [[{ k: "$_id.filter", v: "$count" }]]
        }
      }
    }
  },
  // 4. 调整输出字段名，将_id改为id
  {
    $project: {
      _id: 0,
      id: "$_id",
      countOccurance: 1
    }
  },
  // 可选：按星期顺序排序
  {
    $sort: {
      id: 1
    }
  }
])

执行查询后，将得到如下中文格式的结果：

[
  {
    "id": "周日",
    "countOccurance": [
      { "ABC3": 1 }
    ]
  },
  {
    "id": "周一",
    "countOccurance": [
      { "ABC3": 1 }
    ]
  },
  {
    "id": "周二",
    "countOccurance": [
      { "ABC1": 1 },
      { "ABC2": 2 }
    ]
  }
]

内容的提问来源于stack exchange，提问作者suneel kumar