MONGODB Aggregate query to give $min value of array field by element

Question

Hi I am converting my existing website from php / mysql to node / mongodb , it is a golf society site which I use to log scores for each members rounds and provide results and statistics into their games. The main collection contains an array of 18 scores which is great for my results queries but I am having a problem with the statistical side, ie Avg score by Hole by Course, Lowest score ever by Hole by Course (eclectic) . I have come up with this aggregate query which works and gives me the result I require but it is ugly !! I am sure there must be a more elegant solution out there and feel I am missing a trick somewhere, I have looked at $map as I thought that might help but don't think it will. I would appreciate it if someone could offer any suggestions in tidying this code up, Thx.

{ _id: 
   { date_played: 2019-06-21T00:00:00.000Z,
     course_played: 1,
     player_id: 1 },
  score: [ 8, 4, 7, 4, 7, 1, 7, 5, 6, 4, 5, 7, 6, 4, 7, 5, 6, 7 ],
  handicap: 23,
  cash_won: 0,
  sort_order: 2,
  gross_score: 100,
  gross_sfpts: 31,
  skins_group: 1,
  score_differential: 26.2,
  pcc_adjustment: 0 }
{ _id: 
   { date_played: 2016-08-14T00:00:00.000Z,
     course_played: 1,
     player_id: 1},
  score: [ 5, 4, 5, 6, 5, 4, 8, 6, 1, 3, 3, 4, 3, 6, 3, 6, 4, 5 ],
  handicap: 18,
  cash_won: 14,
  sort_order: 4,
  gross_score: 81,
  gross_sfpts: 44,
  skins_group: 1,
  score_differential: 12.1,
  pcc_adjustment: 0 }

[
  {
    '$match': {
      '_id.course_played': 1
    }
  }, {
    '$project': {
      'player_name': 1, 
      'hole01': {
        '$arrayElemAt': [
          '$score', 0
        ]
      }, 
      'hole02': {
        '$arrayElemAt': [
          '$score', 1
        ]
      }, 
      'hole03': {
        '$arrayElemAt': [
          '$score', 2
        ]
      }, 
      'hole04': {
        '$arrayElemAt': [
          '$score', 3
        ]
      }, 
      'hole05': {
        '$arrayElemAt': [
          '$score', 4
        ]
      }, 
      'hole06': {
        '$arrayElemAt': [
          '$score', 5
        ]
      }, 
      'hole07': {
        '$arrayElemAt': [
          '$score', 6
        ]
      }, 
      'hole08': {
        '$arrayElemAt': [
          '$score', 7
        ]
      }, 
      'hole09': {
        '$arrayElemAt': [
          '$score', 8
        ]
      }, 
      'hole10': {
        '$arrayElemAt': [
          '$score', 9
        ]
      }, 
      'hole11': {
        '$arrayElemAt': [
          '$score', 10
        ]
      }, 
      'hole12': {
        '$arrayElemAt': [
          '$score', 11
        ]
      }, 
      'hole13': {
        '$arrayElemAt': [
          '$score', 12
        ]
      }, 
      'hole14': {
        '$arrayElemAt': [
          '$score', 13
        ]
      }, 
      'hole15': {
        '$arrayElemAt': [
          '$score', 14
        ]
      }, 
      'hole16': {
        '$arrayElemAt': [
          '$score', 15
        ]
      }, 
      'hole17': {
        '$arrayElemAt': [
          '$score', 16
        ]
      }, 
      'hole18': {
        '$arrayElemAt': [
          '$score', 17
        ]
      }
    }
  }, {
    '$sort': {
      '_id.player_id': 1
    }
  }, {
    '$group': {
      '_id': '$_id.player_id', 
      'name': {
        '$first': '$player_name'
      }, 
      'hole1': {
        '$min': '$hole01'
      }, 
      'hole2': {
        '$min': '$hole02'
      }, 
      'hole3': {
        '$min': '$hole03'
      }, 
      'hole4': {
        '$min': '$hole04'
      }, 
      'hole5': {
        '$min': '$hole05'
      }, 
      'hole6': {
        '$min': '$hole06'
      }, 
      'hole7': {
        '$min': '$hole07'
      }, 
      'hole8': {
        '$min': '$hole08'
      }, 
      'hole9': {
        '$min': '$hole09'
      }, 
      'hole10': {
        '$min': '$hole10'
      }, 
      'hole11': {
        '$min': '$hole11'
      }, 
      'hole12': {
        '$min': '$hole12'
      }, 
      'hole13': {
        '$min': '$hole13'
      }, 
      'hole14': {
        '$min': '$hole14'
      }, 
      'hole15': {
        '$min': '$hole15'
      }, 
      'hole16': {
        '$min': '$hole16'
      }, 
      'hole17': {
        '$min': '$hole17'
      }, 
      'hole18': {
        '$min': '$hole18'
      }, 
      'rounds': {
        '$sum': 1
      }
    }
  }, {
    '$addFields': {
      'total': {
        '$add': [
          '$hole1', '$hole2', '$hole3', '$hole4', '$hole5', '$hole6', '$hole7', '$hole8', '$hole9', '$hole10', '$hole11', '$hole12', '$hole13', '$hole14', '$hole15', '$hole16', '$hole17', '$hole18'
        ]
      }
    }
  }, {
    '$sort': {
      'total': 1
    }
  }, {
    '$limit': 10
  }
]

Which gives this as an example when run against the total database, which is the result I want but I would like all the "hole" fields to be returned in an Array as per the original score field.

{ _id: 1,
    hole1: 5,
    hole2: 4,
    hole3: 5,
    hole4: 4,
    hole5: 5,
    hole6: 2,
    hole7: 3,
    hole8: 3,
    hole9: 3,
    hole10: 3,
    hole11: 2,
    hole12: 3,
    hole13: 4,
    hole14: 2,
    hole15: 3,
    hole16: 3,
    hole17: 3,
    hole18: 3,
    rounds: 562,
    total: 53 }

Joe · Accepted Answer · 2021-03-22 05:19:12Z

1

You might $unwind the scores array, keeping the index as the hole number, then $group by player, course, and hole to get the score for each hole, $sort by hole number to make sure of the order, and then $group by player and course, pushing the scores back into an array.

db.collection.aggregate([
  {$match: {"_id.player_id": 1}},
  {$unwind: {
      path: "$score",
      includeArrayIndex: "hole"
  }},
  {$group: {
      _id: {
        course_played: "$_id.course_played",
        player_id: "$_id.player_id",
        hole: "$hole"
      },
      minScore: {$min: "$score"},
      rounds: {$sum: 1}
  }},
  {$sort: {"_id.hole": 1}},
  {$group: {
      _id: {
        course_played: "$_id.course_played",
        player_id: "$_id.player_id"
      },
      score: {$push: "$minScore"},
      total: {$sum: "$minScore"},
      rounds: {$first: "$rounds"}
  }}
])

Playground

answered Mar 22, 2021 at 5:19

Joe

29k4 gold badges25 silver badges50 bronze badges

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

1 Comment

mal Over a year ago

Thx you so much, exactly was I was looking for a very elegant solution, you have opened my eyes to a different way of looking at aggregation.

Collectives™ on Stack Overflow

MONGODB Aggregate query to give $min value of array field by element

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related