Get sum of Nested Array in Aggregate

Question

Ok, I have an issue I cannot seem to solve.

I have a document like this:

{
  "playerId": "43345jhiuy3498jh4358yu345j",
  "leaderboardId": "5b165ca15399c020e3f17a75",
  "data": {
    "type": "EclecticData",
    "holeScores": [
      {
        "type": "RoundHoleData",
        "xtraStrokes": 0,
        "strokes": 3,
      },
      {
        "type": "RoundHoleData",
        "xtraStrokes": 1,
        "strokes": 5,
      },
      {
        "type": "RoundHoleData",
        "xtraStrokes": 0,
        "strokes": 4
      }
    ]
  }
}

Now, what I am trying to accomplish is using aggregate sum the strokes and then order it afterwards. I am trying this:

var sortedBoard = db.collection.aggregate(
    {$match: {"leaderboardId": boardId}},
    {$group: {
        _id: "$playerId",
        played: { $sum: 1 },
        strokes: {$sum: '$data.holeScores.strokes'}
        }
    },
    {$project:{ 
        type: "$SortBoard",
        avgPoints: '$played',
        sumPoints: "$strokes",
        played : '$played'
    }}
);

The issue here is that I do net get the strokes sum correct, since this is inside another array.

Hope someone can help me with this and thanks in advance :-)

Neil Lunn · Accepted Answer · 2018-06-06 08:11:01Z

5

You need to say $sum twice:

var sortedBoard = db.collection.aggregate([
  { "$match": { "leaderboardId": boardId}},
  { "$group": {
    "_id": "$playerId",
    "SortBoard": { "$first": "$SortBoard" },
    "played": { "$sum": 1 },
    "strokes": { "$sum": { "$sum": "$data.holeScores.strokes"} }
  }},
  { "$project": { 
    "type": "$SortBoard",
    "avgPoints": "$playeyed",
    "sumPoints": "$strokes",
    "played": "$played"
  }}
])

The reason is because you are using it both as a way to "sum array values" and also as an "accumulator" for $group.

The other thing you appear to be missing is that $group only outputs the fields you tell it to, therefore if you want to access other fields in other stages or output, you need to keep them with something like $first or another accumulator. We also appear to be missing a pipeline stage in the question anyway, but it's worth noting just to be sure.

Also note you really should wrap aggregation pipelines as an official array [], because the legacy usage is deprecated and can cause problems in some language implementations.

Returns the correct details of course:

{
        "_id" : "43345jhiuy3498jh4358yu345j",
        "avgPoints" : 1,
        "sumPoints" : 12,
        "played" : 1
}

edited Jun 6, 2018 at 8:11

answered Jun 6, 2018 at 7:51

Neil Lunn

151k36 gold badges356 silver badges327 bronze badges

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

3 Comments

Mansa Over a year ago

Perfect, just what I was going for :-) Not sure what you mean about the official array?

Neil Lunn Over a year ago

@Mansa Look at the differences between your question and the answer. Then you should be sure.

Mansa Over a year ago

I see. Thanks for clarifying that :-)

Collectives™ on Stack Overflow

Get sum of Nested Array in Aggregate

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related