How to get sum of specific number of nested objects in an array sorted by date with aggregation pipeline, MongoDB

I need to get the sum of the "points" of the 3 most recent entries/objects under members array based on date. So, the total points should be 3000. (Person B 500, Person C 1000, and Person D 1500).

How do I do that with my current aggregation pipeline? Do I use slice or something?

Here's the document

[{
  "_id": { "$oid": "00001" },
  "leaderId": 001,
  "leader": "Josh",
  "members": [
    {
      "name": "Person A",
      "points": 500,
      "date": 2023-05-30T18:00:00.000+00:00
    },
    {
      "name": "Person B",
      "points": 500,
      "date": 2023-05-30T19:10:00.000+00:00
    },
    {
      "name": "Person C",
      "points": 1000,
      "date": 2023-05-30T19:20:00.000+00:00
    },
    {
      "name": "Person D",
      "points": 1500,
      "date": 2023-05-30T19:30:00.000+00:00
    }]
  }]

Here's my aggregation pipeline that returns 3500 because it gets the sum of points from all objects under members array.

db.users.aggregate([{ $match: { leader: "Josh" } },
{$unwind: "$members"},
{$sort: {"members.date": -1}},
{$group: {_id: "$leaderId", latestThreePoints: {$sum: "$members.points"}}}])

From your current work, you just need an extra $limit stage after the $sort

db.collection.aggregate([
  {
    $match: {
      leader: "Josh"
    }
  },
  {
    $unwind: "$members"
  },
  {
    $sort: {
      "members.date": -1
    }
  },
  {
    $limit: 3
  },
  {
    $group: {
      _id: "$leaderId",
      latestThreePoints: {
        $sum: "$members.points"
      }
    }
  }
])

Mongo Playground

I agree with @ray's answer that you can achieve your desired result from the starting pipeline by adding a $limit stage.

That said, it seems as if the _id field is related leaderId field. If so, that means that the $unwind -> processing -> $group on leaderId approach being used is an antipattern as it unnecessarily introduces a blocking stage.

Instead, consider processing the documents in place. You mentioned $slice which seems like the appropriate solution to me. If we know that the members array is always in ascending order, then the following should compute what you want:

  {
    "$project": {
      _id: "$leaderId",
      "latestThreePoints": {
        "$reduce": {
          "input": {
            "$slice": [
              "$members",
              -3
            ]
          },
          "initialValue": 0,
          "in": {
            $sum: [
              "$$this.points",
              "$$value"
            ]
          }
        }
      }
    }
  }

Demo.

If the array is not always sorted, but you are on version 6.0 or higher, then you can use the $sortArray operator. The stage would look as follows:

  {
    "$project": {
      _id: "$leaderId",
      "latestThreePoints": {
        "$reduce": {
          "input": {
            "$slice": [
              {
                $sortArray: {
                  input: "$members",
                  sortBy: {
                    date: -1
                  }
                }
              },
              3
            ]
          },
          "initialValue": 0,
          "in": {
            $sum: [
              "$$this.points",
              "$$value"
            ]
          }
        }
      }
    }
  }

Demo.

Also of note, you can use $addFields instead of $project if you would like to add this new field to the existing fields in the document as opposed to removing all of the others.