MongoDB 聚合查询：按字段名分组并计算总数。

Question

我有一系列的文档，格式如下：

{
  gameId: '0001A',
  score: 40,
  name: 'Bob',
  city: 'London'
}

我尝试在我的文档上运行汇总，以便为每个gameId输出以下视图：

{
  cities: [
    London: {
      totalScore: 500 // sum of the scores for all documents that have a city of London
      people: [
        'Bob',
        'Anna',
        'Sally',
        'Sue'
      ],
      peopleCount: 4 // a count of all people who also have the city of London
    },
    Barcelona: {
      totalScore: 400 // sum of the scores for all documents that have a city of Barcelona
      people: [
        'Tim',
        'Tina',
        'Amir'
      ], // names of all people who also have the city of Barcelona
      peopleCount: 3 // count of how many names appear
    },
  ]

我尝试使用聚合管道中的$facet$和$bucket来实现这一点。但是这似乎不符合要求，因为$bucket / $bucketAuto需要范围或相应数量的存储桶。然后$bucketAuto在对象中设置min和max值。

目前，我可以简单地按如下方式分组总人数、姓名和分数：

$group: {
  _id: '$gameId',
  totalScore: {
    $sum: '$score'
 },  
  uniqueClients: {
    $addToSet: '$name'
  }
},
$addFields: {
  uniqueClientCount: {
  $size: '$uniqueClients'
 }
}

如何按城市进行分类？

- LearningPython

Answer 1

您可以尝试以下两个$group阶段：

db.collection.aggregate([
  {
    "$group": {
      "_id": {
        game: "$gameId",
        city: "$city"
      },
      "totalScore": {
        "$sum": "$score"
      },
      "people": {
        "$addToSet": "$name"
      }
    }
  },
  {
    "$addFields": {
      "peopleCount": {
        "$size": "$people"
      }
    }
  },
  {
    "$group": {
      "_id": "$_id.game",
      "cities": {
        "$push": {
          "$arrayToObject": [
            [
              {
                k: "$_id.city",
                v: {
                  people: "$people",
                  totalScore: "$totalScore",
                  peopleCount: "$peopleCount"
                }
              }
            ]
          ]
        }
      }
    }
  }
])

在mongoplayground上查看https://mongoplayground.net/p/f4uItCb0BwW

- Fourchette