MongoDB聚合总计字段和按字段名分组

ekqde3dh  于 2023-01-20  发布在  Go
关注(0)|答案(1)|浏览(147)

我有一个文件集,内容如下:

{
  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在对象中设置minmax的值。
现在,我可以像这样直接地对总人数、姓名和分数进行分组:

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

我该怎么按城市细分呢?

toe95027

toe950271#

您可以尝试两个$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"
                }
              }
            ]
          ]
        }
      }
    }
  }
])

在蒙戈Playgroundhttps://mongoplayground.net/p/f4uItCb0BwW上看到

相关问题