MongoDB - Sum of a nested array with group_by

Question

I have a collection in MongoDB of bands that have multiple albuns and genres. Here is a simplified example of collection:

{"_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"]}

The goal is to select to sum all sales_amount of albuns between a specified date and give that number to each of the band's genres. For example, in this band all those 4 genres will have 14725 (6245 + 7347 + 1133) sales.

I want the output to be something like this:

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

mickl · Accepted Answer · 2020-10-14 14:50:17Z

2

You can use below aggregation:

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" }
        }
    }
])

Basically $sum can be used to get a total number of sales per artist. Then you run $unwind in order to aggregate each genre separately. The $group stage is needed twice since you want to aggregate by genre and then get a single document which summarizes the data for all genres. In the last step you need $replaceRoot to get genre names as keys.

Mongo Playground

answered Oct 14, 2020 at 14:50

mickl

50.1k9 gold badges71 silver badges99 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nypt008 Over a year ago

Thank you! How would you add the dates for the album?

mickl Over a year ago

@Nypt008 you can prepand one more stage and use $filter like here: mongoplayground.net/p/bP9Uomh7odr

Collectives™ on Stack Overflow

MongoDB - Sum of a nested array with group_by

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related