MongoDB -包含group_by的嵌套数组的总和

Question

我有一个MongoDB乐队的集合，其中有多个专辑和流派。下面是一个简化的集合示例：

{"_id":1,
 "band_name":"Maritime (band)",
 "band_url":"http://dbpedia.org/resource/Maritime_(band)",
 "album":[{
    "album_name":"We, the Vehicles",
    "release_date":"2006-04-18",
    "description":"We, the Vehicles is the second album by Maritime. It is the band's final album with the bass guitarist Eric Axelson. Of the album, Brian Howe of Pitchfork said that it  'not only exceeds its predecessor [Glass Floor], but serves as a corrective to every one of its deficiencies. '",
    "running_time":36.28333333,
    "sales_amount":6245
 },
 {
    "album_name":"Heresy and the Hotel Choir",
    "release_date":"2007-10-16",
    "description":"Heresy and the Hotel Choir is an album by the indie pop band Maritime. It is the band's third full-length album and was released on October 16, 2007.",
    "running_time":41.65,
    "sales_amount":7347
 },
 {
    "album_name":"Glass Floor",
    "release_date":"2004-06-01",
    "description":"Glass Floor is the first full-length album by the indie pop group Maritime. It was the follow-up to the Adios EP, which contained five tracks. The songs on this album range from acoustic ballads ( 'Lights ') to fast acoustic driven songs ( 'James ') as well as heartfelt love songs ( 'I'm Not Afraid ') and fast, poppy tunes ( 'Adios ') which was on the Adios EP. Brian Howe, of Pitchfork panned the album, saying it was  'nothing to write home about, and didn't hold out any hope for great things to come either. ' However, Howe was more positive about the band's later album, We, the Vehicles.",
    "running_time":45.05,
    "sales_amount":1133
 }],
 "genre":["Emo","Indie rock","Indietronica","Indie pop"]}

目标是选择将指定日期之间的所有sales_amount相加，并将该数字分配给乐队的每个流派。例如，在这个波段中，所有这4种流派的销售额将达到14725 (6245 + 7347 + 1133)。

我希望输出是这样的：

{"Emo": 48965,
 "Rock": 74685,
 "Indie": 123456} 

Answer 1

您可以使用以下聚合：

db.collection.aggregate([
    {
        $project: {
            genre: 1,
            total_sales: { $sum: "$album.sales_amount" }
        }
    },
    {
        $unwind: "$genre"
    },
    {
        $group: {
            _id: "$genre",
            sum: { $sum: "$total_sales" }
        }
    },
    {
        $group: {
            _id: null,
            data: {
                $push: { k: "$_id", v: "$sum" }
            }
        }
    },
    {
        $replaceRoot:{
            newRoot: { $arrayToObject: "$data" }
        }
    }
])

基本上，$sum可以用来获得每个艺术家的总销售额。然后运行$unwind，以便分别聚合每个流派。$group阶段需要两次，因为您希望按流派进行聚合，然后获得汇总所有流派的数据的单个文档。在最后一步中，您需要$replaceRoot来获取类型名称作为关键字。

Mongo Playground