How can I find the sum and average of a document array?

Question

Currently, I have the following document structure. The range field holds sub JSON objects as an array.

{
  "_id" : ObjectId("62f60ba0ed0f1a1a0v"),
  "userId" : "1431",
  "range" : [
     {
        "index" : 0,
        "clubType" : "driver", 
        "swingSize" : "full", 
        "distance" : 200,
        "createdAt" : "2022-08-12T08:13:20.435+00:00"
     },
     {
        "index" : 0,
        "clubType" : "driver", 
        "swingSize" : "full", 
        "distance" : 150,
        "createdAt" : "2022-08-12T08:13:20.435+00:00"
     },
     {
        "index" : 0,
        "clubType" : "wood", 
        "swingSize" : "full", 
        "distance" : 180,
        "createdAt" : "2022-08-12T08:13:20.435+00:00"
     }     
  ]
}

In the above document, I want to sum and average the indexes with the same clubType and swingSize. So I used mongoose Aggregate like below.

result = await ClubRangeResultSchema.aggregate([
   {
      $match : {
        userId : "1431",
        range  : {
           $elemMatch : {
              $and : [
                 {
                    createdAt : { $gte : lastDate }
                 },
                 {
                    createdAt : { $lte : lastDate }
                 }
              ]
           }
        }
      }
   },
   {
      $group : {
         '_id' : {
            'clubName' : '$range.clubName',
            'swingSize' : '$range.swingSize'
         },
         'totalDistance' : { $sum : { $sum : '$range.distance' }}
      }
   }
]);

The result of the above query is all duplicate field names, and the total is also extracted for all data.

How should I modify the query?

Tom Slabbaert · Accepted Answer · 2022-08-15 14:22:55Z

1

You're close but need to do a couple of changes:

you want to $unwind the range array, $group doesn't flattern the array so when you use $range.clubType you are basically grouping the array itself as the value.
You want an additional match after the $unwind, the $elemMatch you use does not filter the range object, it does matches the initial document.

After the changes the pipeline should look like this:

db.collection.aggregate([
  {
    $match: {
      userId: "1431",
      range: {
        $elemMatch: {
          createdAt: "2022-08-12T08:13:20.435+00:00"
        }
      }
    }
  },
  {
    $unwind: "$range"
  },
  {
    $match: {
      "range.createdAt": "2022-08-12T08:13:20.435+00:00"
    }
  },
  {
    $group: {
      "_id": {
        "clubName": "$range.clubType",
        "swingSize": "$range.swingSize"
      },
      "totalDistance": {
        $sum: "$range.distance"
      },
      avgDistance: {
        $avg: "$range.distance"
      }
    }
  }
])

Mongo Playground

answered Aug 15, 2022 at 14:22

Tom Slabbaert

22.5k10 gold badges35 silver badges45 bronze badges

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

1 Comment

Minwoo Kim Over a year ago

Thank you for helping us resolve this. We have rechecked the functionality for $unwind. I think we can write a more detailed query through the query above.

Collectives™ on Stack Overflow

How can I find the sum and average of a document array?

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