Aug 9, 2016

Query to subtract in MongoDB

We can use $subtract operator to subtract tow numbers. $subtract is arithmetic aggregation operator which subtract two number and returns the difference.

$subtract can be use to get differences between dates means we can subtract date also, $subtract treat to second number as millisecond if first number is date type.

Syntax:


{ $subtract: [ <expression1>, <expression2> ] }


Example: Suppose we have a collection “Inventory” as below and before show the details of product on our website we need to calculate total price in our query.


{
            _id:1,
            Title:"Title1",
            SKU:"1234567890123",
            Price:20.50,
            ShippingCharge:4.80,
            Discount:2.00,
            CreatedDate:ISODate("2016-08-09T013:00:00Z")
}
{
            _id:2,
            Title:"Title2",
            SKU:"1234567890121",
            Price:30.50,
            ShippingCharge:4.80,
            Discount:2.00,
            CreatedDate:ISODate("2016-08-09T013:00:00Z")
}
{
            _id:3,
            Title:"Title3",
            SKU:"1234567890122",
            Price:50.50,
            ShippingCharge:4.80,
            Discount:2.00,
            CreatedDate:ISODate("2016-08-09T013:00:00Z")
}


See the following query here we calculating the discounted price. We are adding Price and ShippingCharge and subtract discounted price.


db.Inventory.aggregate(
[
            { $project:
                        { Title: 1,SKU:1, Discounted_Price:
                                    { $subtract:
                                                [ { $add: [ "$Price", "$ShippingCharge" ] }, "$Discount" ]
                                    }
                        }
            }
] )


Result:


/* 1 */
{
    "_id" : 1,
    "Title" : "Title1",
    "SKU" : "1234567890123",
    "Discounted_Price" : 23.3
}

/* 2 */
{
    "_id" : 2,
    "Title" : "Title2",
    "SKU" : "1234567890121",
    "Discounted_Price" : 33.3
}

/* 3 */
{
    "_id" : 3,
    "Title" : "Title3",
    "SKU" : "1234567890122",
    "Discounted_Price" : 53.3
}


Same like that if you want to subtract date you can use $subtract operator.

Subtract one day from CreatedDate
See following query which is subtracted one day from CreatedDate.


db.Inventory.aggregate(
[
            { $project:
                        { Title: 1,SKU:1, Sub_Date:
                                    { $subtract:
                                                [ "$CreatedDate", 24*60*60*1000 ]
                                    }
                        }
            }
] )


Result:


/* 1 */
{
    "_id" : 1,
    "Title" : "Title1",
    "SKU" : "1234567890123",
    "Sub_Date" : ISODate("2016-08-08T01:00:00.000Z")
}

/* 2 */
{
    "_id" : 2,
    "Title" : "Title2",
    "SKU" : "1234567890121",
    "Sub_Date" : ISODate("2016-08-08T01:00:00.000Z")
}

/* 3 */
{
    "_id" : 3,
    "Title" : "Title3",
    "SKU" : "1234567890122",
    "Sub_Date" : ISODate("2016-08-08T01:00:00.000Z")
}