CODEWITHSUNDEEP
pythonmongodb
Michael K
Michael K

Reputation: 13

How do I craft this MongoDB Query?

Context: I have collected some golf data and would like to perform some analytics.

Question:

I have a collection where each document contains the hole number and total strokes of the hole.

{'_id': ObjectId('5f8db03cba60405c610878d8'), 'scorecardDetails': [{'scorecard': {'holes': [{'number': 1, 'strokes': 4}, {'number': 2, 'strokes': 4}, {'number': 3, 'strokes': 3}, {'number': 4, 'strokes': 5}, {'number': 5, 'strokes': 3}, {'number': 6, 'strokes': 3}, {'number': 7, 'strokes': 5}, {'number': 8, 'strokes': 7}, {'number': 9, 'strokes': 5}, {'number': 10, 'strokes': 5}, {'number': 11, 'strokes': 3}, {'number': 12, 'strokes': 4}, {'number': 13, 'strokes': 3}, {'number': 14, 'strokes': 3}, {'number': 15, 'strokes': 6}, {'number': 16, 'strokes': 4}, {'number': 17, 'strokes': 4}, {'number': 18, 'strokes': 3}]}}]}

How can I find the average of total strokes for each hole number? My final output should look something like this:

{'_id': ObjectId('5f8db03cba60405c610878d8'), 'scorecardDetails': [{'scorecard': {'holes': [{'number': 1, 'strokes': 4.1}, {'number': 2, 'strokes': 4.2}, {'number': 3, 'strokes': 3.1}, {'number': 4, 'strokes': 5.1}, ... ]}}]}

Upvotes: 0

Views: 50

Answers (1)

David Chen
David Chen

Reputation: 56

Use aggregate functions, $unwind to expand the array items one by one and $group combine them by hole number. Your data structure contains 2 level arrays so need $unwind twice.

Here's how

db.getCollection('test').aggregate([
{$unwind: "$scorecardDetails"}, 
{$unwind: "$scorecardDetails.scorecard.holes"}, 
{$group: {
  _id: "$scorecardDetails.scorecard.holes.number",
  average: {
    $avg: "$scorecardDetails.scorecard.holes.strokes"
    }, 
}} , 
{'$sort': {"_id": 1}}
])

Upvotes: 1

Related Questions