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?

CodePudding user response：

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

1. 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.

2. 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