MongoDB Conditional Sum of Array Elements for a collection of documents

user_7489488 0
Viewed 739

I have the following MongoDB collection of documents, each containing a field called "history", which contains an array of sub-documents with fields "date" and "points".

[{
    history: [{
        date: "2019-20-20",
        points: 1,
    }, {
        date: "2019-20-21",
        points: 1,
    }, {
        date: "2019-20-22",
        points: 1,
    }, {
        date: "2019-20-23",
        points: 1,
    }],
}, {
    history: [{
        date: "2019-20-20",
        points: 1,
    }, {
        date: "2019-20-21",
        points: 2,
    }, {
        date: "2019-20-22",
        points: 3,
    }, {
        date: "2019-20-23",
        points: 4,
    }],
}]

I'm not sure what is the best way to construct a query that produces the below output. For the following example, the date range (inclusive) is "2019-20-21" to "2019-20-22". "totalPoints" is a new field, which contains the sum of all the points in the "history" field across that date range.

[{
    history: [{
        date: "2019-20-20",
        points: 1,
    }, {
        date: "2019-20-21",
        points: 1,
    }, {
        date: "2019-20-22",
        points: 1,
    }, {
        date: "2019-20-23",
        points: 1,
    }],
    totalPoints: 2,
}, {
    history: [{
        date: "2019-20-20",
        points: 1,
    }, {
        date: "2019-20-21",
        points: 2,
    }, {
        date: "2019-20-22",
        points: 3,
    }, {
        date: "2019-20-23",
        points: 4,
    }],
    totalPoints: 5,
}]

Below is a general idea of what I'm trying to do:

User.aggregate([{
    $addFields: {
        totalPoints: { $sum: points in "history" field if date range between "2019-20-21" and "2019-20-22" } ,
    }
}]);

The reason I want to create a new "totalPoints" field is because eventually I want to sort via the "totalPoints" field.

mongoose aggregation-framework mongodb
1 Answers

For a single pipeline, you can combine $reduce with $filter to get the sum as follows:

var startDate = "2019-20-21";
var endDate = "2019-20-22";
User.aggregate([
    { "$addFields": { 
        "totalPoints": {
            "$reduce": {
                "input": {
                    "$filter": {
                        "input": "$history",
                        "as": "el",
                        "cond": {
                            "$and": [
                                { "$gte": ["$$el.date", startDate] },
                                { "$lte": ["$$el.date", endDate ] },
                            ]
                        }
                    }
                },
                "initialValue": 0,
                "in": { "$add": [ "$$value", "$$this.points" ] }
            }
        }
    } }
]);

Another alternative is having two pipeline stages where you start your aggregation with a filtered array which contains only the elements that match the date range query. Combine $addFields with $filter for this and your filter condition uses the conditional operator $and with the comparison operators $gte and $lte. The following pipeline shows this:

{ "$addFields": { 
    "totalPoints": {
        "$filter": {
            "input": "$history",
            "cond": {
                "$and": [
                    { "$gte": ["$$this.date", "2019-20-21"] },
                    { "$lte": ["$$this.date", "2019-20-22"] },
                ]
            }
        }
    }
} },

On getting the filtered array you can then get the sum easily in the next pipeline with $sum, so your complete pipeline becomes

var startDate = "2019-20-21";
var endDate = "2019-20-22";
User.aggregate([
    { "$addFields": { 
        "totalPoints": {
            "$filter": {
                "input": "$history",
                "cond": {
                    "$and": [
                        { "$gte": ["$$this.date", startDate] },
                        { "$lte": ["$$this.date", endDate ] },
                    ]
                }
            }
        }
    } },
    { "$addFields": { 
        "totalPoints": { "$sum": "$totalPoints.points" }
    } }
])
user_1220050
Related