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

Mongodb – $group and sum + add all greater than

Let’s say my documents look like this:

[
  {
    name: "Example 1",
    year: "2012"
  },
  {
    name: "Example 2",
    year: "2012"
  },
  {
    name: "Example 3",
    year: "2013"
  },
  {
    name: "Example 4",
    year: "2014"
  }
]

Using an aggregation, is there a way to group by year and sum the document count, but additionally add the sum of all later years?

The result I want is this:

[
    {
        _id: "2012",
        count: 4 // years 2012-2014
    },
    {
        _id: "2013",
        count: 2 // years 2013-2014
    },
    {
        _id: "2014",
        count: 1 // only year 2014
    }
]

Right now, I’m using a normal $group + $sum, which gives me the counts for each year individually and then I sort them in JavaScript. I was hoping that there was a simpler way that gets rid of the additional JS code:

yearCounts: [           
    { $group: { _id: "$year", count: { $sum: 1 } } }
]

const yearCounts: { _id: string, count: number }[] = aggregationResult[0].yearCounts || [];
const yearCountsSummed = yearCounts.map((yearCount: { _id: string, count: number }) => {
    const yearsUntil = yearCounts.filter(year => year._id >= yearCount._id);
    const countSummed = yearsUntil.map(yearCount => yearCount.count).reduce((a, b) => a + b) || 0;
    return countSummed;
});
Tags:

2

Answers


  1. 0

    Yes, this was recently made quite easy using the $setWindowFields stage introduced in version 5.0 which allows us to do calculations over a "window" of documents (in your case all years), While in theory it’s possible to do it with older operators as well I recommend against it as not only the syntax very messy but also the performance of such query will be abysmal. It will be easier to just group by year and then do the rest in code with a simple loop.

    Anyways here is how to do it:

    db.collection.aggregate([
  {
    $group: {
      _id: "$year",
      sum: {
        $sum: 1
      }
    }
  },
  {
    $setWindowFields: {
      sortBy: {
        _id: -1
      },
      output: {
        sum: {
          $sum: "$sum",
          window: {
            documents: [
              "unbounded",
              "current"
            ]
          }
        }
      }
    }
  }
])

    Mongo Playground

    Login or Signup to reply.
  2. 0

    Someone has given the answer, even though I am trying to give the other way.
    As per desire result mentioned into the question. we can achieve result as below with aggregation with $group ,$sort pipeline.

    PlayGround

    enter image description here

    Hope, this solution might help or lead to solve your problem.

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

Back To Top
Search