Question 177845 in Mongodb Question posted in
The official documentation can be found here.

How to sum nested objects in MongoDB array sorted by date w/ aggregation pipeline?

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"}}}])
Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search