CODEWITHSUNDEEP
mongodbgroupingaggregateconditional-statements
Mansa
Mansa

Reputation: 2325

MongoDB count fi true in aggregate group

I am trying, in an easy way, to average and sum some statistics, but have hit a wall!

I have a collection of documents with some statistics like this:

{
  "playerId": "5c6024b5031f5bc44d790e01",
  "courseId": "5b2a0ab1c6dc0f04e9a1e769",
  "gir": true,
  "data": {
    "roundHoles": [
      {
        "type": "HoleData",
        "holeNo": 1,
        "par": 3,
        "hcp": 18
      },
      {
        "type": "HoleData",
        "holeNo": 2,
        "par": 4,
        "hcp": 4
      },
      {
        "type": "HoleData",
        "holeNo": 3,
        "par": 4,
        "hcp": 8
      }
    ],
    "holeScores": [
      {
        "type": "RoundHoleData",
        "strokes": 3,
        "points": 2,
        "puts": 1,
        "gir": true,
        "scrambled": false
      },
      {
        "type": "RoundHoleData",
        "strokes": 5,
        "points": 1,
        "puts": 2,
        "gir": false,
        "scrambled": false
      },
      {
        "type": "RoundHoleData",
        "strokes": 4,
        "points": 2,
        "puts": 1,
        "gir": false,
        "scrambled": true
      }
    }
  }
}

What I would like to do is to get the average strokes, points and puts and the sum of scrambles and gir when true, bur only if the main "gir" is set to true.

Here is what i have come up with so far:

var allScores = runtimeCollection('roundScores').aggregate(
  {
      $match: { "playerId": playerId, "courseId": "5b2a0ab1c6dc0f04e9a1e769" }
  },
  {
      $unwind: {
          path: "$data.holeScores",
          includeArrayIndex: "index"
      }
  },
  {
      $group: {
          _id: "$index",
          rounds: { $sum: 1 },
          avgStrokes: { $avg: "$data.holeScores.strokes" },
          avgPoints: { $avg: "$data.holeScores.points" },
          avgPuts: { $avg: "$data.holeScores.puts" },
          sumScrambles: { $sum: "$data.holeScores.scrambled" }
      }
  }
);

Here is what I get from that:

{
  "_id": 17,
  "rounds": 18,
  "avgStrokes": 3.4444444444444446,
  "avgPoints": 1.2777777777777777,
  "avgPuts": 1.6111111111111112,
  "sumScrambles": 0
},
{
  "_id": 14,
  "rounds": 18,
  "avgStrokes": 5.388888888888889,
  "avgPoints": 2.1666666666666665,
  "avgPuts": 1.5,
  "sumScrambles": 0
},
{
  "_id": 12,
  "rounds": 18,
  "avgStrokes": 5,
  "avgPoints": 1.6111111111111112,
  "avgPuts": 1.8333333333333333,
  "sumScrambles": 0
}

It looks like I get the average parts just fine, but the sum does not work. I guess I have to add a condition to the sumScrambles, but not sure how?

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

Upvotes: 1

Views: 2534

Answers (2)

chridam
chridam

Reputation: 103375

You don't necessarily need to $unwind and $group to get the averages and sum for this aggregate, all could be done in a $project stage since the $sum and $avg operators also work in the $project stage given that the fields are arrays.

As for the conditional sums, you can use $filter on the array and then count the elements in the reduced array with $size to give you a sum.

The following pipeline demonstrates this approach

runtimeCollection('roundScores').aggregate([
    { '$match': { 'playerId': playerId, 'courseId': '5b2a0ab1c6dc0f04e9a1e769' } },
    { '$project': {
        'rounds': { '$size': '$data.holeScores' },
        'avgStrokes': { '$avg': '$data.holeScores.strokes' },
        'avgPoints': { '$avg': '$data.holeScores.points' },
        'avgPuts': { '$avg': '$data.holeScores.puts' },
        'avgPars': { '$avg': '$data.roundHoles.par' },
        'sumScrambles': { 
            '$size': {
                '$filter': {
                    'input': '$data.holeScores',
                    'cond': '$$this.scrambled'
                }
            }
        }
    } }
])

Upvotes: 2

Elvis
Elvis

Reputation: 1143

Ok, Firstly, you can't get the sum of boolean values, you can only get the sum of numbers. So, I'm assuming you want to add 1 whenever the scrambled value is true, in that case, you can use '$cond', which allows you to write conditions.

You can try something like this,

sumScrambles: { $sum: { $cond: [ { $eq: [ "$data.holeScores.scrambled", true ] }, 1, 0 ] } }

Upvotes: 2

Related Questions