MongoDB $subtract

In MongoDB, you can use the $subtract aggregation pipeline operator to subtract numbers and/or dates.

Specifically, $subtract can do the following three things:

• Subtract two numbers to return the difference
• Subtract a number (in milliseconds) from a date and return the resulting date
• Subtract two dates to return the difference in milliseconds

The $subtract operator accepts the values as arguments. 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..

Sample Data

Suppose we have a collection called data with the following document:

{
	"_id" : 1,
	"a" : 20000,
	"b" : 250,
	"start" : ISODate("2021-01-03T00:00:00Z"),
	"end" : ISODate("2021-01-03T23:30:15.100Z")
}

Subtract Numbers

We can use the $subtract operator to subtract the a field from the b field (or vice-versa).

Example:

db.data.aggregate(
   [
     { $project: { 
       _id: 0,
       a: 1, 
       b: 1, 
       result: { 
         $subtract: [ "$a", "$b" ] } } 
         }
   ]
)

Result:

{ "a" : 20000, "b" : 250, "result" : 19750 }

Subtract a Number from a Date

If the first argument is a date and the second argument is a number, the $subtract operator subtracts the number from the date in milliseconds.

Example:

db.data.aggregate(
   [
     { $project: { 
       _id: 0,
       b: 1, 
       start: 1, 
       result: { 
         $subtract: [ "$start", "$b" ] } } 
         }
   ]
).pretty()

Result:

{
	"b" : 250,
	"start" : ISODate("2021-01-03T00:00:00Z"),
	"result" : ISODate("2021-01-02T23:59:59.750Z")
}

We can see that 250 milliseconds has been subtracted from the date.

When we subtract a number from a date, the date must be the first argument with the number as the second argument.

Here’s what happens if we switch the arguments around:

db.data.aggregate(
   [
     { $project: { 
       _id: 0,
       b: 1, 
       start: 1, 
       result: { 
         $subtract: [ "$b", "$start" ] } } 
         }
   ]
).pretty()

Result:

uncaught exception: Error: command failed: {
	"ok" : 0,
	"errmsg" : "cant $subtract adate from a double",
	"code" : 16556,
	"codeName" : "Location16556"
} : aggregate failed :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
doassert@src/mongo/shell/assert.js:18:14
_assertCommandWorked@src/mongo/shell/assert.js:618:17
assert.commandWorked@src/mongo/shell/assert.js:708:16
DB.prototype._runAggregate@src/mongo/shell/db.js:266:5
DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1046:12
@(shell):1:1

We get an error telling us that it can’t subtract a date from a double.

Return the Difference Between Two Dates

If both arguments are dates, then the $subtract operator returns the difference between the two dates in milliseconds.

Example:

db.data.aggregate(
   [
     { $project: { 
       _id: 0,
       start: 1, 
       end: 1,
       result: { 
         $subtract: [ "$end", "$start" ] } } 
         }
   ]
).pretty()

Result:

{
	"start" : ISODate("2021-01-03T00:00:00Z"),
	"end" : ISODate("2021-01-03T23:30:15.100Z"),
	"result" : NumberLong(84615100)
}

If we switch the dates around, the result becomes a negative value:

db.data.aggregate(
   [
     { $project: { 
       _id: 0,
       start: 1, 
       end: 1,
       result: { 
         $subtract: [ "$start", "$end" ] } } 
         }
   ]
).pretty()

Result:

{
	"start" : ISODate("2021-01-03T00:00:00Z"),
	"end" : ISODate("2021-01-03T23:30:15.100Z"),
	"result" : NumberLong(-84615100)
}

Passing the Wrong Number of Arguments

The $subtract operator accepts exactly two arguments. Passing the wrong number of arguments results in an error.

Example:

db.data.aggregate(
   [
     { $project: { 
       result: { 
         $subtract: [ "$a" ] } } 
         }
   ]
)

Result:

uncaught exception: Error: command failed: {
	"ok" : 0,
	"errmsg" : "Invalid $project :: caused by :: Expression $subtract takes exactly 2 arguments. 1 were passed in.",
	"code" : 16020,
	"codeName" : "Location16020"
} : aggregate failed :
_getErrorWithCode@src/mongo/shell/utils.js:25:13
doassert@src/mongo/shell/assert.js:18:14
_assertCommandWorked@src/mongo/shell/assert.js:618:17
assert.commandWorked@src/mongo/shell/assert.js:708:16
DB.prototype._runAggregate@src/mongo/shell/db.js:266:5
DBCollection.prototype.aggregate@src/mongo/shell/collection.js:1046:12
@(shell):1:1

Passing Null Values

Passing two null values results in null being returned.

Example:

db.data.aggregate(
   [
     { $project: { 
       result: { 
         $subtract: [ null, null ] } } 
         }
   ]
)

Result:

{ "_id" : 1, "result" : null }