CODEWITHSUNDEEP
mongodbaggregation-framework
Tal Levi
Tal Levi

Reputation: 331

How to nested sum in mongodb

I have this scheme:

[{
        name: 'John',
        plays: [{
                playNum: 1,
                song: {
                    length: 400,
                    param: 'a'
                }
            }, {
                playNum: 2,
                song: {
                    length: 350,
                    param: 'a'
                }
            }, {
                playNum: 3,
                song: {
                    length: 211,
                    param: 'a'
                }
            ]
        },
        {
            name: 'John',
            plays: [{
                    playNum: 2,
                    song: {
                        length: 400,
                        param: 'a'
                    }
                }, {
                    playNum: 3,
                    song: {
                        length: 350,
                        param: 'a'
                    }
                }, {
                    playNum: 4,
                    song: {
                        length: 211,
                        param: 'a'
                    }
                ]
            }
        ]

I want to calculate the time of specific play i.e. for play 2 - we have one song in 350 and another one in 400 = 750.

I tryied to do it that way:

aggregate(
        {$match : { "shows.showNum" : showNum } },
        {$unwind : "$shows"},
        {$group : {_id : "$shows.showNum", totalLength : { $sum : "$shows.song.length" }}})

But in that way I am getting the total of all songs of player who plays in that show.

can anyone help?

Thanks!

Upvotes: 0

Views: 119

Answers (2)

Sagar P. Ghagare
Sagar P. Ghagare

Reputation: 542

db.collection.aggregate([
    {$unwind: '$plays'}, 
    {$match: {"plays.playNum" : 2} },
    {$group: {
        _id: null, 
        "suma": {$sum: "$song.length" }
    }}
])

Upvotes: 0

Neil Lunn
Neil Lunn

Reputation: 151092

Your first $match only matches the "documents" that contain that value within the array. To actually "filter" the array contents you need another $match after you $unwind in the pipeline:

db.collection.aggregate([
    { "$match": { "plays.playNum" : playNum } },
    { "$unwind": "$plays"},
    { "$match": { "plays.playNum" : playNum } },
    { "$group": {
        "_id" : "$plays.playNum", 
        "totalLength": { "$sum" : "$plays.song.length" }
    }}
])

If you have MongoDB 2.6 or greater you can actually filter the array using the $map operator and other helper operators:

db.collection.aggregate([
    { "$match": { "plays.playNum" : playNum } },
    { "$project": {
        "plays": {
             "$setDifference": [
                {
                    "$map": {
                        "input": "$plays",
                        "as": "el",
                        "in": {
                            "$cond": [
                                { "$eq": [ "$$el.playNum", playNum ] },
                                "$$el",
                                false
                            ]
                        }
                    }
                ],
                [false]
            ]
        }
    }},
    { "$unwind": "$plays" },
    { "$group": {
        "_id" : "$plays.playNum", 
        "totalLength": { "$sum" : "$plays.song.length" }
    }}
])

Which makes things a bit faster by combining functions from various stages. It looks more complicated but is actually the fastest way to process to the end result.

Upvotes: 3

Related Questions