After explaining pipeline operation symbols in the previous article, this article will explain pipeline expressions used in operation symbols, which allow us to perform calculations, comparisons, string modifications, and other analysis methods in pipelines.

  • Mathematical expression (mathematical expression)
  • Date expression (date expression)
  • String expressions (string expression)
  • Logical expression (logical expression)

~ Mathematical expression ~

Here is a list of some of the more commonly used mathematical expressions (all here).

expression instructions
$add Take multiple expressions and add them up.
$subtract Take two expressions and subtract the second from the first.
$multiply Take multiple expressions and multiply them.
$divide Take two expressions and divide them.
$mod Take an expression, and then divide and mod it.

We would like to know the total revenue of the order.

Let’s see how this works in practice. Suppose we have the following data, which is the order data.

{ "id" : 1 , "price" : 100 , "count" : 20, "discount" : 0 },
{ "id" : 2 , "price" : 200 , "count" : 20, "discount" : 100 },
{ "id" : 3 , "price" : 50 , "count" : 20, "discount" : 100 },
{ "id" : 4 , "price" : 10 , "count" : 210, "discount" : 200 },
{ "id" : 5 , "price" : 100 , "count" : 30, "discount" : 20 }
Copy the code

So in this application, we want to know what the total revenue is, and here’s the revenue formula.

Revenue per order = price * count-discountCopy the code

Then we break it down into the pipeline process, we use the step of first calculating the revenue of each order and then adding it up.

  1. Calculate and save the revenue from each orderincomeColumn.
  2. Send all ordersincomeAdd up the columns and place themtotalColumn.

According to the above steps we can generate the following aggregation method.

Db.order. Aggregate ({//1. Calculate the income of each order and place it in the 'income' column. "$project" : { "id" : 1 , "income" : { "$subtract" : [ { $multiply : [ "$price","$count"] } , "$discount"] } },{ 2. $group: {"_id" : null, "total" : {$sum: "$income"}}}})Copy the code

The results are as follows.

{ "_id" : null, "total" : 11680 }
Copy the code

Although this example only applies to a few expressions, in fact they are all used in the same way, so if you know one of them, you can use the others very well.

~ Date expression ~

Most of these types of expressions are used to convert a list of dates into units you want such as month, day, year, etc.

expression instructions The scope of
$year Transition to adult ex.2016. NO
$month Convert to month ex. 1 (for January) 1 ~ 12
$week Convert to week ex. 1 (representing the first week of the year) 0 ~ 53
$dayOfMonth Convert to the NTH day of the month ex. 1 (representing the first day of the month) 1-31
$dayOfYear Convert to the NTH day of the year ex. 1 (representing the first day of the year) 1-366.
$dayOfWeek Convert to a day of the week ex. 1 (Monday) 1(Sunday) to 7(Saturday)
$hour The hour to extract the time 0 ~ 23
$minute Extract the minutes of the time 0 ~ 59
$second Extract the seconds of the time 0 ~ 60

We want to get details about date.

Let’s look at the results of the actual transformation, assuming we have the following data.

{" _id ": 1, the" date ": ISODate (" the 2016-01-02 T08:10:20. 651 z")}Copy the code
db.test.aggregate({
    $project : {
        "year" : { $year : "$date" },
        "month" : { $month  : "$date"},
        "week" : { $week : "$date"},
        "dayOfMonth" : { "$dayOfMonth" : "$date" },
        "dayOfYear" : { "$dayOfYear" : "$date"},
        "dayOfWeek" : { "$dayOfWeek" : "$date"},
        "hour" : { "$hour" : "$date"},
        "minute" : { "$minute" : "$date"},
        "second" : { "$second" : "$date"}
    }
})
Copy the code

The result is as follows, where week is 0 because it starts from week 0, and dayOfWeek 7 means that the day is Saturday.

"2016-01-02T08:10:20.651z" {"_id" :1, "year" : 2016, "month" :1, "week" : 0, "dayOfMonth" :2, "dayOfYear" :2 2, "dayOfWeek" : 7, "hours" : 8, "minute" : 10, "second" : 20 }Copy the code

~ string expression ~

Is the aggregation of the string to do some miles QQ things.

expression instructions
$substr You can “only” get a range of a string.
$concat Concatenates the specified string.
$toLower Write less.
$toUpper Variable capital.
$strcasecmp Compares whether two strings are equal, 0 if they are equal, 1 if the ASCII code of the string is greater than the other string, and -1 otherwise.

Practical application ~ We want to get a document that starts with item B, and the describe output should be all lowercase.

Below is our information.

{ "item" : "ABC", "describe":"AAbbcc"},
{ "item" : "BCE" , "describe":"hello WorD"},
{ "item" : "CAA" , "describe":"BBCCaa"}
Copy the code

We can break it down into the following steps, depending on the problem.

  1. In eachitemThe first value of thetempIn the field.
  2. And eachtempwithBMake a comparison and put the comparison results inresultIn the field.
  3. selectresultfor0thedocument.
  4. Will thedocumentthedescribeThe field is converted to lowercase.

The following is a split aggregation method.

Db.test. aggregate({//1. Obtain the first value of each 'item' and store it in the 'temp' column. $project: {" describe ": 1," temp ": {" $substr" : [$item, 0, 1]}}}, {/ / 2. And each 'temp' is compared with 'B' and the comparison result is placed in the 'result' column. $project : { "describe" : 1, "result" : {"$strcasecmp":["$temp","B"]}} }, { //3. Select 'document' whose 'result' is' 0 '. $match : { "result" : 0 } }, { 4. Convert the 'describe' column of the 'document' to lowercase. $project : { "describe" : { "$toLower" : "$describe" } } } )Copy the code

The result is as follows:

{ 
    "_id" : ObjectId("58500f0d7fac2213af387c9c"), 
    "describe" : "hello word" 
}
Copy the code

~ Logical expression ~

Here is a list of some of the more commonly used logical expressions.

expression instructions use
$cmp Compare expr1 and 2, the same is 0,1 >2 is 1, and the opposite is -1. "$cmp":[expr1,expr2]
$eq Expr1 and 2 are compared as well, but return if they aretrueOtherwise, forfalse. "$eq":[expr1,expr2]
$lt, $lte Less than and less than or equal to "$lt" : value
$gt, $gte Greater than and greater than or equal to "$gt" : value
$and All expressions are zerotrue, the backtrue "$and":[expr1,expr2..]
$or One of the expressions is thetatrue, the backtrue "$or" : [expr1,expr2..]
$not Take the inverse value for the expression "$not" : expr
$cond It’s just a regular formulaifelse "$cond":[boolExpr,trueExpr,falseExpr]

Practical application ~ I want to calculate the actual income of each order, which will be 20% off when the quantity is more than 200. Finally, I will calculate the total income of each group by grouping them according to class.

First of all, let’s look at what we have, as follows.

{ "id":1,"class" : "1", "price" : 10,"count" : 180},
{ "id":1,"class" : "1" ,"price" : 10,"count" : 350},
{ "id":1,"class" : "2" ,"price" : 10,"count" : 90},
{ "id":1,"class" : "2" ,"price" : 10,"count" : 320},
{ "id":1,"class" : "2","price" : 10,"count" : 150}
Copy the code

Then based on the problem, we will break it down into the following pipeline steps.

  1. All orders shall be judged at the discount rate and stored indiscountIn the water.
  2. Calculate the revenue for each order and put it intotalIn the water.
  3. According to theclassGroup and calculate the total income of each group and store itresultIn the water.

The following is an aggregation method based on the steps.

Db.orders. Aggregate ({// 1. Judge the discount rate and put it in 'discount'. "$project" : { "class" : 1, "price" : 1, "count" : 1, "discount" : { "$cond" : [{ $gte: ["$count", 200]},0.8,1]}}}, {// 2. Calculate the revenue for each order and save it in 'total'. "$project" : { "class" :1, "total" : { "$multiply" : ["$price","$count","$discount"] } } }, { // 3. Group by 'class' and calculate the total income of each group, stored in 'result'. "$group" : { "_id" : "$class" , "result" : {"$sum" : "$total"} } })Copy the code

The results are as follows.

{ "_id" : "2", "result" : 4960 },
{ "_id" : "1", "result" : 4600 }
Copy the code

Conclusion ~ ~

This clause presents the expression of a lot of, also with the actual examples to illustrate how to use, but also only in very basic way, if you want to more skilled use of these expressions, the author suggested more instances of the practice, and in practice to also want to continue to think about is there a way to better, faster, believe that you don’t need how long time, can be very skilled.

~ References ~

  • Docs.mongodb.com/manual/refe…
  • Docs.mongodb.com/v3.2/refere…
  • Stackoverflow.com/questions/1…