I have an aggregation query producing an array containing array elements. How can I $sum the values and produce output similar to the following:
{ "additionalReminders": 7 }
This is an example of the data structure. Notice some of the array elements are empty and should be ignored or counted as 0:
{
"additionalReminders": [
[
"1",
"1"
],
[],
[],
[
"1"
],
[],
[
"1"
],
[],
[
"1"
],
[],
[
"1"
],
[],
[],
[
"1"
]
]
}
Mongo Playground: https://mongoplayground.net/p/BjSU_oYC8KG
Here is what you're looking for:
db.collection.aggregate([
{
"$unwind": "$additionalReminders"
},
{
"$unwind": "$additionalReminders"
},
{
$group: {
_id: null,
"additionalReminders": {
$sum: {
"$toInt": "$additionalReminders"
}
}
}
},
{
$project: {
_id: false,
additionalReminders: true
}
}
])
Playground: https://mongoplayground.net/p/JInbHAmc_yV
The idea is to unwind your field a couple of times so as to get this output:
...
{
"_id": ObjectId("5a934e000102030405000000"),
"additionalReminders": "1"
},
{
"_id": ObjectId("5a934e000102030405000000"),
"additionalReminders": "1"
},
{
"_id": ObjectId("5a934e000102030405000000"),
"additionalReminders": "1"
},
...
Then it's just a simple matter of summing up the $additionalReminders field, but not before we cast it to an Int.