如何用纯MongoDB查询统计嵌套数组中玩家的游戏参与次数？

需求说明 #

我需要基于以下房间文档结构，按user_name或user_id统计玩家在所有games数组中的总参与次数。例如玩家Andrew在第一个房间参与2场游戏，第二个房间参与1场，最终统计结果应为3。使用纯MongoDB聚合查询，不依赖Mongoose。

示例文档数据 #

[
  { // room data
    _id: '20ae0225-512a-405b-8b9f-d6ffdca6634c',
    games: [
      {
        _id: 'cb01da11-5809-43e6-b02e-e878b38f4e11',
        players: [
          { user_id: 'ef0d7656-38a1-4c82-982b-b4beb3941e07', user_name: 'Andrew' },
          { user_id: 'a13de96d-e137-41d8-bd5c-083d9dbc00d7', user_name: 'Jack' }
        ]
      },
      {
        _id: '9b03d0ef-178e-49d1-8b86-6b63c1957d6f',
        players: [
          { user_id: 'ef0d7656-38a1-4c82-982b-b4beb3941e07', user_name: 'Andrew' },
          { user_id: '67acb8b7-1670-4c07-979a-8a6e481bfd95', user_name: 'Thomas' }
        ]
      },
      {
        _id: '733a30d5-a6c1-4ce2-a33b-c8dbf61f2a2e',
        players: [
          { user_id: 'a13de96d-e137-41d8-bd5c-083d9dbc00d7', user_name: 'Jack' },
          { user_id: '67acb8b7-1670-4c07-979a-8a6e481bfd95', user_name: 'Thomas' }
        ]
      }
    ]
  },
  { // room data
    _id: '20ae0225-512a-405b-8b9f-d6ffdca6634c',
    games: [
      {
        _id: 'cb01da11-5809-43e6-b02e-e878b38f4e11',
        players: [
          { user_id: '67acb8b7-1670-4c07-979a-8a6e481bfd95', user_name: 'Thomas' },
          { user_id: 'a13de96d-e137-41d8-bd5c-083d9dbc00d7', user_name: 'Jack' }
        ]
      },
      {
        _id: '9b03d0ef-178e-49d1-8b86-6b63c1957d6f',
        players: [
          { user_id: 'ef0d7656-38a1-4c82-982b-b4beb3941e07', user_name: 'Andrew' },
          { user_id: '67acb8b7-1670-4c07-979a-8a6e481bfd95', user_name: 'Thomas' }
        ]
      },
      {
        _id: '733a30d5-a6c1-4ce2-a33b-c8dbf61f2a2e',
        players: [
          { user_id: 'a13de96d-e137-41d8-bd5c-083d9dbc00d7', user_name: 'Jack' },
          { user_id: '67acb8b7-1670-4c07-979a-8a6e481bfd95', user_name: 'Thomas' }
        ]
      }
    ]
  }
]

解决方案 #

1. 统计所有玩家的总参与次数 #

使用MongoDB聚合框架，通过多层拆分数组+分组统计实现：

db.your_collection_name.aggregate([
  // 拆分每个房间的games数组，每个game成为独立文档
  { $unwind: "$games" },
  // 拆分每个game的players数组，每个玩家成为独立文档（此时每条文档对应一次游戏参与记录）
  { $unwind: "$games.players" },
  // 按user_id和user_name分组，统计每个玩家的总参与次数
  {
    $group: {
      _id: {
        user_id: "$games.players.user_id",
        user_name: "$games.players.user_name"
      },
      total_games: { $sum: 1 }
    }
  },
  // 可选：重新整理输出字段，提升可读性
  {
    $project: {
      _id: 0,
      user_id: "$_id.user_id",
      user_name: "$_id.user_name",
      total_participated_games: "$total_games"
    }
  }
])

输出示例：

[
  { "user_id": "ef0d7656-38a1-4c82-982b-b4beb3941e07", "user_name": "Andrew", "total_participated_games": 3 },
  { "user_id": "a13de96d-e137-41d8-bd5c-083d9dbc00d7", "user_name": "Jack", "total_participated_games": 3 },
  { "user_id": "67acb8b7-1670-4c07-979a-8a6e481bfd95", "user_name": "Thomas", "total_participated_games": 4 }
]

2. 统计单个指定玩家的总参与次数 #

如果只需要统计某一个玩家的次数，可以在聚合流程中加入$match过滤条件：

按user_id查询

db.your_collection_name.aggregate([
  { $unwind: "$games" },
  { $unwind: "$games.players" },
  // 匹配目标玩家的user_id
  { $match: { "games.players.user_id": "ef0d7656-38a1-4c82-982b-b4beb3941e07" } },
  // 全局分组统计总次数
  {
    $group: {
      _id: null,
      total_games: { $sum: 1 }
    }
  },
  { $project: { _id: 0, total_participated_games: "$total_games" } }
])

按user_name查询

db.your_collection_name.aggregate([
  { $unwind: "$games" },
  { $unwind: "$games.players" },
  // 匹配目标玩家的user_name
  { $match: { "games.players.user_name": "Andrew" } },
  {
    $group: {
      _id: null,
      total_games: { $sum: 1 }
    }
  },
  { $project: { _id: 0, total_participated_games: "$total_games" } }
])

输出示例：

{ "total_participated_games": 3 }

内容的提问来源于stack exchange，提问作者Nguyen Kha Nam