Cara menggunakan mongodb subtract two fields

Question

I’d love to create a question in metabase using MongoDB as backend. I do have a document with the following structure:

Table of Contents Show

Syntax of the $subtract operator:
Subtract Numbers
Subtract Two Dates
Subtract Milliseconds from a Date

{ "change": { "added": 5, "removed": 2 } }

Now like to do SUM(added) - SUM(removed), but I can’t figure out how since I’m not able to combine the two expressions in any way

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.

Example 1: Subtract two numbers using the $subtract operator

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

In MongoDB, you can use the

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

5 aggregation pipeline operator to subtract numbers and/or dates.

Specifically,

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

5 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

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

5 operator accepts exactly two arguments. Passing the wrong number of arguments results in an error.

MongoDB provides different types of arithmetic expression operators that are used in the aggregation pipeline stages and an $subtract operator is one of them. This operator is used to subtract two numbers and return the difference in the numbers or to subtract two dates and return the difference in the milliseconds, or subtracts date and number in milliseconds and returns the date.

Syntax:

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

Here, the given arguments must be a valid expression like numbers or a date, and the second argument is subtracted from the first argument. If you are subtracting a number from a date, then the first argument of this operator is a date.

Examples:

In the following examples, we are working with:

Database: GeeksforGeeks

Collection: Employee
Document: four documents that contain the details of the employees in the form of field-value pairs.

Using $subtract operator subtract two numbers:

In this example, we are going to subtract 5*24*60*60000 milliseconds (i.e., 5 days) from the value of projectEndDate field using a $subtract operator.

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 expression has the following syntax:

copy

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

The second argument is subtracted from the first argument.

The arguments can be any valid 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 .

Behavior

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

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

1 collection with the following documents:

copy

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 expression to compute the

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

3 by subtracting the

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

4 from the subtotal of

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

5 and

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

6.

copy

db.sales.aggregate( [ { $project: { item: 1, total: { $subtract: [ { $add: [ "$price", "$fee" ] }, "$discount" ] } } } ] )

The operation returns the following results:

copy

{ "_id" : 1, "item" : "abc", "total" : 7 }
{ "_id" : 2, "item" : "jkl", "total" : 19 }

Subtract Two Dates

The following aggregation uses the expression to subtract

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

8 from the current date, using the system (available starting in 4.2) and returns the difference in milliseconds:

copy

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

Alternatively, you can use the for the current date:s

copy

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

Both operations return documents that resemble the following:

copy

{ "_id" : 1, "item" : "abc", "dateDifference" : NumberLong("186136746187") }
{ "_id" : 2, "item" : "jkl", "dateDifference" : NumberLong("186133146187") }

Subtract Milliseconds from a Date

The following aggregation uses the expression to subtract 5 * 60 * 1000 milliseconds (5 minutes) from the “$date” field:

copy

db.sales.aggregate( [ { $project: { item: 1, dateDifference: { $subtract: [ "$date", 5 * 60 * 1000 ] } } } ] )

The operation returns the following results:

copy

{ "_id" : 1, "item" : "abc", "dateDifference" : ISODate("2014-03-01T07:55:00Z") }
{ "_id" : 2, "item" : "jkl", "dateDifference" : ISODate("2014-03-01T08:55:00Z") }