$subtract (aggregation)
On this page
Definition
$subtract
Subtracts two numbers to return the difference, or two dates to return the difference in milliseconds, or a date and a number in milliseconds to return the resulting date.
The
$subtract
expression has the following syntax:{ $subtract: [ <expression1>, <expression2> ] } The second argument is subtracted from the first argument.
The arguments can be any valid expression as long as they resolve to numbers and/or dates. To subtract a number from a date, the date must be the first argument. For more information on expressions, see Expression Operators.
Behavior
When input types are mixed, $subtract
promotes the smaller input
type to the larger of the two. A type is considered larger when it
represents a wider range of values. The order of numeric types from
smallest to largest is: integer → long → double → decimal
The larger of the input types also determines the result type unless
the operation overflows and is beyond the range represented by that
larger data type. In cases of overflow, $subtract
promotes the
result according to the following order:
If the larger input type is
integer
, the result type is promoted tolong
.If the larger input type is
long
, the result type is promoted todouble
.If the larger type is
double
ordecimal
, the overflow result is represented as + or - infinity. There is no type promotion of the result.
When mixing Date
and non-integer operands,
$subtract
rounds the non-integer value to the nearest integer
before performing the operation.
Examples
Consider a sales
collection with the following documents:
db.sales.insertMany([ { "_id" : 1, "item" : "abc", "price" : 10, "fee" : 2, "discount" : 5, "date" : ISODate("2014-03-01T08:00:00Z") }, { "_id" : 2, "item" : "jkl", "price" : 20, "fee" : 1, "discount" : 2, "date" : ISODate("2014-03-01T09:00:00Z") } ])
Subtract Numbers
The following aggregation uses the $subtract
expression
to compute the total
by subtracting the discount
from the
subtotal of price
and fee
.
db.sales.aggregate( [ { $project: { item: 1, total: { $subtract: [ { $add: [ "$price", "$fee" ] }, "$discount" ] } } } ] )
The operation returns the following results:
{ "_id" : 1, "item" : "abc", "total" : 7 } { "_id" : 2, "item" : "jkl", "total" : 19 }
Subtract Two Dates
The following aggregation uses the $subtract
expression
to subtract $date
from the current date, using the system
NOW
and returns the difference in milliseconds:
db.sales.aggregate( [ { $project: { item: 1, dateDifference: { $subtract: [ "$$NOW", "$date" ] } } } ] )
Alternatively, you can use the Date()
for the current date:s
db.sales.aggregate( [ { $project: { item: 1, dateDifference: { $subtract: [ new Date(), "$date" ] } } } ] )
Both operations return documents that resemble the following:
{ "_id" : 1, "item" : "abc", "dateDifference" : NumberLong("186136746187") } { "_id" : 2, "item" : "jkl", "dateDifference" : NumberLong("186133146187") }
Subtract Milliseconds from a Date
The following aggregation uses the $subtract
expression
to subtract 5 * 60 * 1000 milliseconds (5 minutes) from the "$date"
field:
db.sales.aggregate( [ { $project: { item: 1, dateDifference: { $subtract: [ "$date", 5 * 60 * 1000 ] } } } ] )
The operation returns the following results:
{ "_id" : 1, "item" : "abc", "dateDifference" : ISODate("2014-03-01T07:55:00Z") } { "_id" : 2, "item" : "jkl", "dateDifference" : ISODate("2014-03-01T08:55:00Z") }