$group and sum + add all greater than

[Florian_Walther]

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;
});

[Florian_Walther]

I found out that I can do this by using $setWindowFields:

yearCounts: [
    { $group: { _id: "$year", count: { $sum: 1 } } },
    {
        $setWindowFields: {
            sortBy: { _id: -1 },
            output: {
                count: {
                    $sum: '$count',
                    window: { documents: ['unbounded', 'current'] }
                }
            }
        }
    }
],

However, I’m getting an error that I can’t find any results for in Google:

MongoServerError: PlanExecutor error during aggregation :: caused by :: Requested document not in SpillableCache. Expected range was 0--1 but got 0
[...]
code: 5643004,
codeName: 'Location5643004'

[Florian_Walther]

yearCounts is part of a facet. There are two more facet stages coming after it. The error disappears when I put yearCounts after these two. But why is that happening? I thought these facet stages are independent of each other?

facet({
    // If yearcounts is placed here, I get an error
    yearCounts: [ 
    { $group: { _id: "$year", count: { $sum: 1 } } },
    {
        $setWindowFields: {
            sortBy: { _id: -1 },
            output: {
                count: {
                    $sum: '$count',
                    window: { documents: ['unbounded', 'current'] }
                }
            }
        }
    }
],
uniqueLanguages: [
    { $match: { approved: true } },
    { $unwind: '$languages' },
    { $group: { _id: null, all: { $addToSet: "$languages" } } }
],
languageCounts: [
    ...matchFilters.filter(matchFilter => !matchFilter.$match.languages),
    { $unwind: '$languages' },
    { $group: { _id: "$languages", count: { $sum: 1 } } }
]
})