Shulou(Shulou.com)05/31 Report--

This article is about how to achieve addition, subtraction, multiplication and division in MongoDB. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article.

1.$add operator (+)

1.1 introduction to grammar and functions

The $add operator is mainly used to add a set of numbers; it can also be used to add a certain interval to a specified time. The interval is in milliseconds (milliseconds).

Syntax for the $add operator:

{$add: [,...]}

This operator takes one or more expressions as parameters, adding them together.

1.2 case demonstration

For example, we have a collection of sales with commodity category field, commodity price field, and merchandise and service charge field.

{"_ id": 1, "item": "abc", "price": 10, "fee": 2, date: ISODate ("2014-03-01T08:00:00Z")} {"_ id": 2, "item": "jkl", "price": 20, "fee": 1, date: ISODate ("2014-03-01T09:00:00Z")} {"_ id": 3, "item": "xyz" "price": 5, "fee": 0, date: ISODate ("2014-03-15T09:00:00Z")}

If there is a demand to calculate the total cost of the goods (price + fee), the statistical SQL is as follows:

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

The results are as follows:

{"_ id": 1, "item": "abc", "total": 12}

{"_ id": 2, "item": "jkl", "total": 21}

{"_ id": 3, "item": "xyz", "total": 5}

Now let's take a look at the $add operation on time. For example, in the collection above, we want to see the effective date of the item if the valid date of the item is + 3 days in the existing date field data (note that the unit of the time parameter is milliseconds).

If the validity date is named expire_date, the corresponding SQL is as follows:

Db.sales.aggregate ([{$project: {item: 1, expire_date: {$add: ["$date", 3'24'60'60 '1000]}])

The results are as follows:

{"_ id": 1, "item": "abc", "expire_date": ISODate ("2014-03-04T08:00:00Z")}

{"_ id": 2, "item": "jkl", "expire_date": ISODate ("2014-03-04T09:00:00Z")}

{"_ id": 3, "item": "xyz", "expire_date": ISODate ("2014-03-18T09:00:00Z")}

Note: this operation of time in this case is implemented in the SQL Server database through the function DATEADD ().

2.$subtract operator (-)

2.1 introduction to grammar and functions

The $subtract operator is the inverse of $add, which calculates the difference between two numeric values, calculates the interval between two dates, and subtracts a specified interval from a specified date. With regard to the calculation of time, the unit of parameters is milliseconds (milliseconds).

Syntax of the $subtract operator

{$subtract: [,]}

Take two expressions as parameters and subtract the second expression from the first expression as a result.

2.2 case demonstration

Empty the collection sales of case 1 and insert the following data

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

New data, added the field of discounted goods. If you count the total cost of goods at this time, you need commodity price field + service fee field-discount field

The SQL statement is as follows

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

The statistical results are as follows

{"_ id": 1, "item": "abc", "total": 7}

{"_ id": 2, "item": "jkl", "total": 19}

There are two scenarios for the subtraction of time

Scenario 1: calculate the time interval between two dates (in milliseconds)

For example, we calculate the time that the item has been generated in the sales (current time-the date field in the collection)

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

The result is shown as

{"_ id": 1, "item": "abc", "dateDifference": NumberLong ("154747094624")}

{"_ id": 2, "item": "jkl", "dateDifference": NumberLong ("154743494624")}

Note: this operation on time in this case is implemented in the SQL Server database through the function DATEDiff ().

Scenario 2: subtract a certain time interval from a specified date

If the items in the sales collection are generally specified to package 3 hours in advance (the date field-3 hours), then let's calculate the package_date

The statement to implement SQL is as follows

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

The results are shown as follows

{"_ id": 1, "item": "abc", "dateDifference": ISODate ("2014-03-01T05:00:00Z")}

{"_ id": 2, "item": "jkl", "dateDifference": ISODate ("2014-03-01T06:00:00Z")}

Note: this operation on time in this case is implemented in the SQL Server database through the function DATEADD ().

3.$multiply operator (*)

3.1 introduction to grammar and functions

The $multiply operator is mainly used to multiply a set of numeric values.

Syntax of the $multiplyt operator

{$multiply: [,...]}

Accept one or more expressions and multiply them.

3.2 case demonstration

Empty the collection sales of case 2 and insert the following data

{"_ id": 1, "item": "abc", "price": 10, "quantity": 2, date: ISODate ("2014-03-01T08:00:00Z")}

{"_ id": 2, "item": "jkl", "price": 20, "quantity": 1, date: ISODate ("2014-03-01T09:00:00Z")}

{"_ id": 3, "item": "xyz", "price": 5, "quantity": 10, date: ISODate ("2014-03-15T09:00:00Z")}

In this commodity sales collection, there is a commodity price field and a commodity quantity field, and we need to calculate the total amount of each commodity (commodity price * commodity data).

Statistical SQL statement:

Db.sales.aggregate ([{$project: {item: 1, total_price: {$multiply: ["$price", "$quantity"]}])

The results are as follows:

{"_ id": 1, "item": "abc", "total_price": 20}

{"_ id": 2, "item": "jkl", "total_price": 20}

{"_ id": 3, "item": "xyz", "total_price": 50}

4.$divide operator (/)

4.1 introduction to grammar and functions

The $divide operator is mainly used to divide two numbers.

The syntax of the $divide operator is:

{$divide: [,]}

Accept two expressions, dividing the first expression by the quotient of the second expression as a result.

4.2 case demonstration

For example, we have a collection of project plans, planning, in which we have the time-consuming field hours, in the following data format.

{"_ id": 1, "name": "A", "hours": 80, "resources": 7}

{"_ id": 2, "name": "B", "hours": 40, "resources": 4}

The above data are counted according to working hours. If we count working hours (working hours / 8), the SQL statement is as follows:

Db.planning.aggregate ([{$project: {name: 1, workdays: {$divide: ["$hours", 8]}])

The results are as follows:

{"_ id": 1, "name": "A", "workdays": 10}

{"_ id": 2, "name": "B", "workdays": 5}

5 congruence operation ($mod)

When it comes to division, you will naturally think of the remainder operation (%). Let's briefly introduce the following remainder operator $mod.

5.1 introduction to grammar and functions

Syntax:

{$mod: [,]}

Accept two expressions and divide the first expression by the remainder of the second expression as a result.

5.2 case demonstration

It is also the collection of task plans above, with man-hour fields and task fields. The data format is as follows:

{"_ id": 1, "project": "A", "hours": 80, "tasks": 7}

{"_ id": 2, "project": "B", "hours": 40, "tasks": 4}

After we count the average of each task, the remaining time, the SQL statement is as follows:

Db.planning.aggregate ([{$project: {remainder: {$mod: ["$hours", "$tasks"]}])

The results are as follows:

{"_ id": 1, "remainder": 3}

{"_ id": 2, "remainder": 0}

6. Case analysis

In the last section of the article, we use an example to consolidate the above learning and introduce another knowledge point-logical expression.

6.1 case study

If a university professor wants to rate his students through some complicated calculation: attendance accounts for 10%, daily test scores account for 30%, and final exam scores account for 60%, but if they are the teacher's favorite students, the score is 100.

The data format for the student students collection is as follows

{"_ id": 1, "sno": 201501010001, "name": "xiaoming", "attendanceAvg": 90, "quizzAvg": 95, "testAvg": 98, "teacherPet": true} {"_ id": 2, "sno": 201501010002, "name": "xiaoli", "attendanceAvg": 100, "quizzAvg": 90, "testAvg": 90, "teacherPet": false} {"_ id": 3 "sno": 201501010003, "name": "xiaohong", "attendanceAvg": 100, "quizzAvg": 80, "testAvg": 100, "teacherPet": false}

The query SQL statement to meet the needs of this professor is as follows:

Db.students.aggregate ([{$project: {"sno": 1, "name": 1, grade: {$cond: ["$teacherPet", 100,{ "$add": [{"$multiply": ["$attendanceAvg", .1]}, {"$multiply": ["$quizzAvg", .3]}, {"$multiply": ["$testAvg") .6]}}])

The results of the query are as follows

{"_ id": 1, "sno": 201501010001, "name": "xiaoming", "grade": 100}

{"_ id": 2, "sno": 201501010002, "name": "xiaoli", "grade": 91}

{"_ id": 3, "sno": 201501010003, "name": "xiaohong", "grade": 94}

In this example, we use not only the mathematical expression $add,$$multiply, but also the logical expression $cond (teacher if's favorite student is 100, otherwise the score is calculated.) Next, let's take a rough look at the logical expressions about MongoDB.

6.2 commonly used logical expressions

Category operator syntax uses the Boolean expression $and {$and: [,...]} if all expressions have a value of true, it returns true, otherwise it returns false. $or {$or: [,...]} returns true as long as any expression has a value of true, otherwise it returns false. $not {$not: []} inverts expression. The control expression $cond {$cond: {if:, then:, else:} or {$cond: [,]} returns true-case if the value of boolean-expression is true, otherwise it returns false-case. $ifNull {$ifNull: [,]} if expression is null, then return replacement-expression-if-null, otherwise return expression. The comparison expression $cmp {$cmp: [,]} compares expression1 and expression2, returning 0 if equal, a positive number 1 if the former is greater than the latter, and a positive number 1 if the former is less than the latter. Returns a negative number-1. Refinement of $strcasecmp {$strcasecmp: [,]} $cmp. Used to compare expression1 and expression2; case-sensitive, mainly for ASCII characters. Returns 0 if equal, a positive number of 1 if the former is greater than the latter, and a positive number of 1 if the former is less than the latter. Returns a negative number-1. Eq/$ne/$gt/$gte/$lt/$lte$eq/$ne/$gt/$gte/$lt/$lte: [,] compare expression1 and expression2 and return the structure of the comparison (true or false). The above is how to achieve addition, subtraction, multiplication and division in MongoDB. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.