MongoDB聚合管道修正：将对象数组转为数组数组

数据集合结构 #

用户行为集合 #

[{"user_id": "1","action": "action_1","timestamp": ISODate("2022-11-17T12:55:34.804+0000")},{"user_id": "1","action": "action_2","timestamp": ISODate("2022-11-17T14:55:34.804+0000")},{"user_id": "1","action": "action_1","timestamp": ISODate("2022-11-17T17:55:34.804+0000")},{"user_id": "1","action": "action_1","timestamp": ISODate("2022-11-18T11:55:34.804+0000")},{"user_id": "1","action": "action_2","timestamp": ISODate("2022-11-18T13:55:34.804+0000")},{"user_id": "1","action": "action_2","timestamp": ISODate("2022-11-18T15:55:34.804+0000")}]

行为分数集合 #

[{"action": "action_1","score": 1},{"action": "action_2","score": 2}]

预期输出结构 #

{"res_date": [["2022-11-17", 3, 5],["2022-11-18", 3, 5]],"act_stat": [{"action_name": "action_1","actions_count": 3,"total_score": 3},{"action_name": "action_2","actions_count": 3,"total_score": 6}],"tot": {"ac": 6,"po": 9}}

现有问题 #

当前聚合管道输出的res_date是对象数组，而非预期的数组数组，输出示例：

{"res_date": [{"2022-11-17": 3,"actions_count": 3,"action_total_score": 4},{"2022-11-21": 3,"actions_count": 3,"action_total_score": 5}],"act_stat": [{"action_name": "action_1","actions_count": 3,"total_score": 3},{"action_name": "action_2","actions_count": 3,"total_score": 6}],"tot": {"ac": 6,"po": 9}}

修正后的聚合管道 #

[
  {$match: {'timestamp': {"$gte": ISODate('2022-11-17'),"$lt": ISODate('2022-11-23')}}},
  {$lookup: {from: "actions_score",localField: "action",foreignField: "action",as: "score"}},
  {$unwind: "$score"},
  // 第一步分组：按日期+行为统计基础数据
  {$group: {
    _id: {date: {$dateToString: {format: "%Y-%m-%d",date: "$timestamp"}},action: "$action"},
    actions_count: {$sum: 1},
    total_score: {$sum: "$score.score"}
  }},
  // 第二步分组：按日期聚合，计算当日总行为数和总分数
  {$group: {
    _id: "$_id.date",
    daily_actions: {$sum: "$actions_count"},
    daily_total_score: {$sum: "$total_score"},
    act_details: {$push: {action_name: "$_id.action", actions_count: "$actions_count", total_score: "$total_score"}}
  }},
  // 第三步全局分组：整合所有数据
  {$group: {
    _id: null,
    res_date: {$push: ["$_id", "$daily_actions", "$daily_total_score"]},
    act_stat: {$push: "$act_details"},
    tot_ac: {$sum: "$daily_actions"},
    tot_po: {$sum: "$daily_total_score"}
  }},
  // 展平act_stat数组，计算最终tot结构
  {$project: {
    _id: 0,
    res_date: 1,
    act_stat: {$reduce: {input: "$act_stat", initialValue: [], in: {$concatArrays: ["$$value", "$$this"]}}},
    tot: {ac: "$tot_ac", po: "$tot_po"}
  }}
]

修改说明 #

1. 调整分组逻辑：第二步分组直接按日期聚合，计算当日总行为数(daily_actions)和总分数(daily_total_score)，同时收集每个行为的统计详情。
2. 生成数组格式的res_date：全局分组时，直接用数组字面量["$_id", "$daily_actions", "$daily_total_score"]生成预期的数组结构，替代原有的$arrayToObject操作。
3. 优化act_stat的整合：通过$reduce和$concatArrays展平嵌套的行为统计数组，得到预期的一维对象数组。
4. 简化tot的计算：全局分组时直接累加总行为数和总分数，避免后续复杂的数组操作。

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