I am trying to perform a mongodb aggregation using Java and unable to figure out how to sum 2 fields individually. Below is my document structure:
{
"_id" : ObjectId["59b6b96423b65d0a04de128d"],
"itemCount": 25,
"defectiveItemCount": 5,
"time": ISODate["x"]
},
{
"_id" : ObjectId["59b6b96423b65d0a04de128d"],
"itemCount": 20,
"defectiveItemCount": 7,
"time": ISODate["x"]
}
I am trying to sum using:
Aggregation pipeline = newAggregation[
match[Criteria.where["time"].gt[time]],
group[].sum["itemCount"].as["total"],
group[].sum["defectiveItemCount"].as["defective"],
project["total"].and["defective"].previousOperation[]
];
I also tried:
Aggregation pipeline = newAggregation[
match[Criteria.where["time"].gt[time]],
group[].sum["itemCount"].as["total"].sum["defectiveItemCount"].as["defective"],
project["total"].and["defective"].previousOperation[]
];
When I run this aggregation, the result of defective is always NULL.
I want the result as:
{
"itemCount": 45,
"defectiveItemCount": 12
}
Is the above possible or should I perform 2 separate aggregations?
Docs Home → MongoDB Manual
$sum
Changed in version 5.0.
Calculates and returns the collective sum of numeric values. $sum
ignores non-numeric values.
$sum
is available in these stages:
$addFields
[Available starting in MongoDB 3.4]$bucket
$bucketAuto
$group
$match
stage that includes an$expr
expression$project
$replaceRoot
[Available starting in MongoDB 3.4]$replaceWith
[Available starting in MongoDB 4.2]$set
[Available starting in MongoDB 4.2]$setWindowFields
[Available starting in MongoDB 5.0]
In MongoDB 3.2 and earlier, $sum
is available in the
$group
stage only.
When used in the $bucket
, $bucketAuto
,
$group
, and $setWindowFields
stages, $sum
has this syntax:
When used in
other supported stages, $sum
has one of two syntaxes:
$sum
has one specified expression as its operand:$sum
has a list of specified expressions as its operand:{ $sum: [ , ... ] }
For more information on expressions, see Expressions.
The result will have the same type as the input except when it cannot be represented accurately in that type. In these cases:
A 32-bit integer will be converted to a 64-bit integer if the result is representable as a 64-bit integer.
A 32-bit integer will be converted to a double if the result is not representable as a 64-bit integer.
A 64-bit integer will be converted to double if the result is not representable as a 64-bit integer.
If used on a field that contains
both numeric and non-numeric values, $sum
ignores the non-numeric values and returns the sum of the numeric values.
If used on a field that does not exist in any document in the collection, $sum
returns
0
for that field.
If all operands are non-numeric, $sum
returns 0
.
In the $group
stage, if the expression resolves to an array,
$sum
treats the operand as a non-numerical value.
In the other supported stages:
With a single expression as its operand, if the expression resolves to an array,
$sum
traverses into the array to operate on the numerical elements of the array to return a single value.With a list of expressions as its operand, if any of the expressions resolves to an array,
$sum
does not traverse into the array but instead treats the array as a non-numerical value.
Consider a
sales
collection with the following documents:
{ "_id" : 1, "item" : "abc", "price" : 10, "quantity" : 2, "date" : ISODate["2014-01-01T08:00:00Z"] } { "_id" : 2, "item" : "jkl", "price" : 20, "quantity" : 1, "date" : ISODate["2014-02-03T09:00:00Z"] } { "_id" : 3, "item" : "xyz", "price" : 5, "quantity" : 5, "date" : ISODate["2014-02-03T09:05:00Z"] } { "_id" : 4, "item" : "abc", "price" : 10, "quantity" : 10, "date" : ISODate["2014-02-15T08:00:00Z"] } { "_id" : 5, "item" : "xyz", "price" : 5, "quantity" : 10, "date" : ISODate["2014-02-15T09:05:00Z"] }
Grouping the documents by the day and the year of the date
field, the following operation uses the $sum
accumulator to compute the total amount and the count for each group of documents.
db.sales.aggregate[ [ { $group: { _id: { day: { $dayOfYear: "$date"}, year: { $year: "$date" } }, totalAmount: { $sum: { $multiply: [ "$price", "$quantity" ] } }, count: { $sum: 1 } } } ] ]
The operation returns the following results:
{ "_id" : { "day" : 46, "year" : 2014 }, "totalAmount" : 150, "count" : 2 } { "_id" : { "day" : 34, "year" : 2014 }, "totalAmount" : 45, "count" : 2 } { "_id" : { "day" : 1, "year" : 2014 }, "totalAmount" : 20, "count" : 1 }
Using $sum
on a non-existent field returns a value of 0
. The following operation attempts to $sum
on qty
:
db.sales.aggregate[ [ { $group: { _id: { day: { $dayOfYear: "$date"}, year: { $year: "$date" } }, totalAmount: { $sum: "$qty" }, count: { $sum: 1 } } } ] ]
The operation returns:
{ "_id" : { "day" : 46, "year" : 2014 }, "totalAmount" : 0, "count" : 2 } { "_id" : { "day" : 34, "year" : 2014 }, "totalAmount" : 0, "count" : 2 } { "_id" : { "day" : 1, "year" : 2014 }, "totalAmount" : 0, "count" : 1 }
The
$count
aggregation accumulator can be used in place of { $sum : 1 }
in the $group
stage.
Tip
See also:
A collection students
contains
the following documents:
{ "_id": 1, "quizzes": [ 10, 6, 7 ], "labs": [ 5, 8 ], "final": 80, "midterm": 75 } { "_id": 2, "quizzes": [ 9, 10 ], "labs": [ 8, 8 ], "final": 95, "midterm": 80 } { "_id": 3, "quizzes": [ 4, 5, 5 ], "labs": [ 6, 5 ], "final": 78, "midterm": 70 }
The following example uses the $sum
in the $project
stage to calculate the total quiz scores, the total lab scores, and the total of the final and the midterm:
db.students.aggregate[[ { $project: { quizTotal: { $sum: "$quizzes"}, labTotal: { $sum: "$labs" }, examTotal: { $sum: [ "$final", "$midterm" ] } } } ]]
The operation results in the following documents:
{ "_id" : 1, "quizTotal" : 23, "labTotal" : 13, "examTotal" : 155 } { "_id" : 2, "quizTotal" : 19, "labTotal" : 16, "examTotal" : 175 } { "_id" : 3, "quizTotal" : 14, "labTotal" : 11, "examTotal" : 148 }
New in version 5.0.
Create a cakeSales
collection that contains cake sales in the states of California [CA
] and Washington [WA
]:
db.cakeSales.insertMany[ [ { _id: 0, type: "chocolate", orderDate: new Date["2020-05-18T14:10:30Z"], state: "CA", price: 13, quantity: 120 }, { _id: 1, type: "chocolate", orderDate: new Date["2021-03-20T11:30:05Z"], state: "WA", price: 14, quantity: 140 }, { _id: 2, type: "vanilla", orderDate: new Date["2021-01-11T06:31:15Z"], state: "CA", price: 12, quantity: 145 }, { _id: 3, type: "vanilla", orderDate: new Date["2020-02-08T13:13:23Z"], state: "WA", price: 13, quantity: 104 }, { _id: 4, type: "strawberry", orderDate: new Date["2019-05-18T16:09:01Z"], state: "CA", price: 41, quantity: 162 }, { _id: 5, type: "strawberry", orderDate: new Date["2019-01-08T06:12:03Z"], state: "WA", price: 43, quantity: 134 } ] ]
This example uses $sum
in the
$setWindowFields
stage to output the sum of the quantity
of cakes sold in each state
:
db.cakeSales.aggregate[ [ { $setWindowFields: { partitionBy: "$state", sortBy: { orderDate: 1 }, output: { sumQuantityForState: { $sum: "$quantity", window: { documents: [ "unbounded", "current" ] } } } } } ] ]
In the example:
partitionBy: "$state"
partitions the documents in the collection bystate
. There are partitions forCA
andWA
.sortBy: { orderDate: 1 }
sorts the documents in each partition byorderDate
in ascending order [1
], so the earliestorderDate
is first.
output
sets thesumQuantityForState
field to the sum of thequantity
values using$sum
that is run in a documents window.The window contains documents between an
unbounded
lower limit and thecurrent
document in the output. This means$sum
returns the sum of thequantity
values for the documents between the beginning of the partition and the current document.
In this output, the sum of the quantity
values for CA
and WA
is shown in the sumQuantityForState
field:
{ "_id" : 4, "type" : "strawberry", "orderDate" : ISODate["2019-05-18T16:09:01Z"], "state" : "CA", "price" : 41, "quantity" : 162, "sumQuantityForState" : 162 } { "_id" : 0, "type" : "chocolate", "orderDate" : ISODate["2020-05-18T14:10:30Z"], "state" : "CA", "price" : 13, "quantity" : 120, "sumQuantityForState" : 282 } { "_id" : 2, "type" : "vanilla", "orderDate" : ISODate["2021-01-11T06:31:15Z"], "state" : "CA", "price" : 12, "quantity" : 145, "sumQuantityForState" : 427 } { "_id" : 5, "type" : "strawberry", "orderDate" : ISODate["2019-01-08T06:12:03Z"], "state" : "WA", "price" : 43, "quantity" : 134, "sumQuantityForState" : 134 } { "_id" : 3, "type" : "vanilla", "orderDate" : ISODate["2020-02-08T13:13:23Z"], "state" : "WA", "price" : 13, "quantity" : 104, "sumQuantityForState" : 238 } { "_id" : 1, "type" : "chocolate", "orderDate" : ISODate["2021-03-20T11:30:05Z"], "state" : "WA", "price" : 14, "quantity" : 140, "sumQuantityForState" : 378 }