MongoDB $subtract Operator

MongoDB provides a variety of arithmetic expression operators. The $subtract operator is one of those operators. This operator is used to subtract two numbers or dates and returns the difference.

If the argument is the two numbers, the result will come in a number.
If the argument is the two dates, the result will come in milliseconds.
If the argument is a date and a number in milliseconds, the result will come in date.

Syntax of the $subtract operator:

Note: The arguments must be a valid expression, such as a number or a date. In this operator, the second argument is subtracted by the first argument. If you want to subtract a number from a date, the first argument should always be a date.

Examples:

Suppose we have a collection of the students with the following documents.

>db.students.find().pretty()
{
         {
             "_id" : 1,
             "std_name" : "John",
             "father_name" : "Mick",
             "department" : "MCA",
             "semester_fee" : 6000,
             "annual_fee" : 10000,
             "start_date" : ISODate("2019-07-03T08:00:00Z"),
             "end_date" : ISODate("2021-05-26T09:00:00Z")
         }
         {
             "_id" : 2,
             "std_name" : "Oliver",
             "father_name" : "Thomas",
             "department" : "BCA",
             "semester_fee" : 4000,
             "annual_fee" : 6000,
             "start_date" : ISODate("2020-07-03T08:00:00Z"),
             "end_date" : ISODate("2023-05-01T09:00:00Z")
         }
         {
             "_id" : 3,
             "std_name" : "Jack",
             "father_name" : "James",
             "department" : "MCA",
             "semester_fee" : 7000,
             "annual_fee" : 12500,
             "start_date" : ISODate("2020-07-11T00:00:00Z"),
             "end_date" : ISODate("2022-05-25T09:00:00Z")
         }
         {
             "_id" : 4,
             "std_name" : "Robert",
             "father_name" : "David",
             "department" : "Btech",
             "fees" : {
                        "semester_fee" : 15000,
                        "annual_fee" : 22500
                      }
             "start_date" : ISODate("2018-07-11T08:00:00Z"),
             "end_date" : ISODate("2022-05-25T09:00:00Z")
         }
         {
             "_id" : 5,
             "std_name" : "Richard",
             "father_name" : "William",
             "department" : "BCA",
             "semester_fee" : 11500,
             "annual_fee" : 20000,
             "start_date" : ISODate("2020-07-03T08:00:00Z"),
             "end_date" : ISODate("2023-05-01T09:00:00Z")
         }
         {
             "_id" : 6,
             "std_name" : "Daniel",
             "father_name" : "Paul",
             "department" : "MCA",
             "semester_fees" : 12500,
             "annual_fee" : 25000,
             "start_date" : ISODate("2018-07-11T08:00:00Z"),
             "end_date" : ISODate("2020-05-25T09:00:00Z")
         }
}

Example 1: Subtract two numbers using the $subtract operator

In this example, we are going to subtract the semester_fee field from the annual_fee field using the $subtract operator.

db.students.aggregate(
 [
   {$match: { department : "MCA" }},
      {
          $project: 
              {
                std_name : 1,
                father_name : 1,
                semester_fee : 1,
                annual_fee : 1,
                result : { $subtract : [ "$annual_fee", "$semester_fee" ] }
               }
      }
 ]
)

Output:

{
    "_id" : 1,
    "std_name" : "John",
    "father_name" : "Mick",
    "semester_fee" : 6000,
    "annual_fee" : 10000,
    "result" : 4000
}    
{
    "_id" : 3,
    "std_name" : "Jack",
    "father_name" : "James",
    "semester_fee" : 7000,
    "annual_fee" : 12500,
    "result" : 5500
}
{
    "_id" : 6,
    "std_name" : "Daniel",
    "father_name" : "Paul",
    "semester_fee" : 12500,
    "annual_fee" : 25000,
    "result" : 12500
}

Example 2: Subtract two dates using the $subtract operator

In this example, we are going to subtract the "start_date" field from the "end_date" field using the $subtract operator.

db.students.aggregate(
 [
   {$match: { department : "BCA" }},
      {
          $project: 
              {
                std_name : 1,
                father_name : 1,
                start_date : 1,
                end_date : 1,
                result : { $subtract : [ "$end_date ", "$start_date" ] }
               }
      }
 ]
)

Output:

{
      "_id" : 2,
      "std_name" : "Oliver",
      "father_name" : "Thomas",
      "start_date" : ISODate("2020-07-03T08:00:00Z"),
      "end_date" : ISODate("2023-05-01T09:00:00Z"),
      "result" : NumberLong("18644531576187")
}
{
      "_id" : 5,
      "std_name" : "Richard",
      "father_name" : "William",
      "start_date" : ISODate("2019-07-03T08:00:00Z"),
      "end_date" : ISODate("2022-05-01T09:00:00Z"),
      "result" : NumberLong("18644531576187")
}

Example 3: Subtract a number from a date using the $subtract operator

In this example, we are going to subtract the 250 from the "start_date" field using the $subtract operator.

db.students.aggregate(
 [
   {$match: { std_name : "Jack" }},
      {
          $project: 
              {
                std_name : 1,
                father_name : 1,
                department : 1,
                start_date : 1,
                result : { $subtract : [ "$start_date", 250 ] }
               }
      }
 ]
)

Output:

{
             "_id" : 3,
             "std_name" : "Jack",
             "father_name" : "James",
             "department" : "MCA",
             "start_date" : ISODate("2020-07-11T00:00:00Z"),
             "result" : ISODate("2020-07-10T23:59:59.750Z") 
}

We can see that 250 milliseconds has been subtracted from the "start_date" field.

What happens if we change the position of the arguments?

db.students.aggregate(
 [
   {$match: { std_name : "Jack" }},
      {
          $project: 
              {
                std_name : 1,
                father_name : 1,
                department : 1,
                start_date : 1,
                result : { $subtract : [ 250, "$start_date" ] }
               }
      }
 ]
)

Output:

uncaught exception : Error : command failed : {
	"ok": 0,
	"errmsg": "cant $subtract a date 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

As you can see, we get an error if the first argument is a number and the second argument is a date.

Next TopicMongoDB $trunc Operator

← prev next →