MongoDB父子集合聚合查询：统计指定时段会员板球参与情况

统计需求 #

1. 统计2024年1月1日至2月29日注册且至少参与一次Cricket的会员数量
2. 统计同注册时段但从未参与Cricket的会员数量

集合结构参考 #

• members集合（父集合）：存储会员基础信息，recordNo为会员唯一标识，registerdDate为注册日期
• memberActivities集合（子集合）：存储会员活动记录，recordNo关联members集合的会员ID，activity数组存储具体活动内容

方案一：单次聚合返回两个统计结果 #

可直接在MongoDB Compass中执行，一次查询同时获取两个统计值：

db.members.aggregate([
  // 过滤2024年1-2月注册的会员
  {
    $match: {
      $expr: {
        $and: [
          { $gte: [{ $dateFromString: { dateString: "$registerdDate", format: "%d-%b-%Y" } }, ISODate("2024-01-01")] },
          { $lte: [{ $dateFromString: { dateString: "$registerdDate", format: "%d-%b-%Y" } }, ISODate("2024-02-29")] }
        ]
      }
    }
  },
  // 关联会员的所有活动记录
  {
    $lookup: {
      from: "memberActivities",
      localField: "recordNo",
      foreignField: "recordNo",
      as: "member_activities"
    }
  },
  // 判断会员是否参与过Cricket活动
  {
    $addFields: {
      has_played_cricket: {
        $anyElementTrue: {
          $map: {
            input: "$member_activities",
            as: "activity_doc",
            in: {
              $anyElementTrue: {
                $map: {
                  input: "$$activity_doc.activity",
                  as: "act",
                  in: { $eq: ["$$act.Comments", "Played Cricket"] }
                }
              }
            }
          }
        }
      }
    }
  },
  // 分分支统计两个结果
  {
    $facet: {
      played_cricket_count: [
        { $match: { has_played_cricket: true } },
        { $count: "total" }
      ],
      never_played_cricket_count: [
        { $match: { has_played_cricket: false } },
        { $count: "total" }
      ]
    }
  },
  // 格式化结果展示
  {
    $project: {
      参与过Cricket的会员数: { $arrayElemAt: ["$played_cricket_count.total", 0] },
      未参与过Cricket的会员数: { $arrayElemAt: ["$never_played_cricket_count.total", 0] }
    }
  }
])

方案二：单独执行两个统计查询 #

若需分别查询单个统计值，可使用以下两个脚本：

1. 统计参与过Cricket的会员数量 #

db.members.aggregate([
  {
    $match: {
      $expr: {
        $and: [
          { $gte: [{ $dateFromString: { dateString: "$registerdDate", format: "%d-%b-%Y" } }, ISODate("2024-01-01")] },
          { $lte: [{ $dateFromString: { dateString: "$registerdDate", format: "%d-%b-%Y" } }, ISODate("2024-02-29")] }
        ]
      }
    }
  },
  {
    $lookup: {
      from: "memberActivities",
      localField: "recordNo",
      foreignField: "recordNo",
      as: "member_activities"
    }
  },
  {
    $match: {
      $expr: {
        $anyElementTrue: {
          $map: {
            input: "$member_activities",
            as: "activity_doc",
            in: {
              $anyElementTrue: {
                $map: {
                  input: "$$activity_doc.activity",
                  as: "act",
                  in: { $eq: ["$$act.Comments", "Played Cricket"] }
                }
              }
            }
          }
        }
      }
    }
  },
  { $count: "参与过Cricket的会员数" }
])

2. 统计未参与过Cricket的会员数量 #

db.members.aggregate([
  {
    $match: {
      $expr: {
        $and: [
          { $gte: [{ $dateFromString: { dateString: "$registerdDate", format: "%d-%b-%Y" } }, ISODate("2024-01-01")] },
          { $lte: [{ $dateFromString: { dateString: "$registerdDate", format: "%d-%b-%Y" } }, ISODate("2024-02-29")] }
        ]
      }
    }
  },
  {
    $lookup: {
      from: "memberActivities",
      localField: "recordNo",
      foreignField: "recordNo",
      as: "member_activities"
    }
  },
  {
    $match: {
      $expr: {
        $not: {
          $anyElementTrue: {
            $map: {
              input: "$member_activities",
              as: "activity_doc",
              in: {
                $anyElementTrue: {
                  $map: {
                    input: "$$activity_doc.activity",
                    as: "act",
                    in: { $eq: ["$$act.Comments", "Played Cricket"] }
                  }
                }
              }
            }
          }
        }
      }
    }
  },
  { $count: "未参与过Cricket的会员数" }
])

关键逻辑说明 #

• $dateFromString：将字符串格式的注册日期转换为MongoDB日期类型，实现时间段精准过滤
• $lookup：建立会员集合与活动集合的关联，获取对应会员的所有活动记录
• $anyElementTrue + $map：嵌套遍历活动文档和活动数组，判断是否存在目标活动记录
• $facet：在单次聚合中并行执行多个统计任务，提升查询效率

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