Navigation

$subtract (aggregation)

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 Expressions.

Behavior

Starting in MongoDB 4.0.22, 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.

Examples

Consider a sales collection with the following documents:

{ "_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 and returns the difference in milliseconds:

db.sales.aggregate( [ { $project: { item: 1, dateDifference: { $subtract: [ new Date(), "$date" ] } } } ] )

The operation returns the following results:

{ "_id" : 1, "item" : "abc", "dateDifference" : NumberLong("11713985194") }
{ "_id" : 2, "item" : "jkl", "dateDifference" : NumberLong("11710385194") }

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