Shulou(Shulou.com)06/01 Report--

And operation:

Implicit and operation:

Db.getCollection ("the_table") .find ({"age": {"$gt": 20}, "sex": "male"}) / / A pair of query conditions for both age and sex fields need to be met at the same time.

Explicit and operation:

Db.getCollection ("the_table") .find ({"$and": [{"age": {"$gte": 20}}, {"address": "the inner world"}]})

Explicit and implicit mixing:

Db.getCollection ("the_table") .find

"id": {"$lt": 10}

"$and": [{"age": {"$gt": 20}}, {"sex": "male"}]

})

Cannot be written as an example of an implicit and operation:

Db.getCollection ("the_table") .find

"$and": [

{"$or": [{"age": {"$gt": 28}}, {"salary": {"$gt": 9900}}]}

{"$or": [{"sex": "male"}, {"id": {"$lt": 20}}]}

]

})

Or operation:

Older than 28, or earning more than 9900:

Db.getCollection ("the_table") .find

"$or": [{"age": {"$gt": 28}}, {"salary": {"$gt": 9900}}]

})

Note: mongodb follows a "short circuit principle" when performing or operations: as long as the previous condition is met, the latter condition is skipped directly. If age is greater than 28, there is no need to check what the value of salary is. Check the value of age only if the salary does not meet the query criteria)

OR operations must be explicit, there is no implicit OR operation

And here you can show both sides of the or:

Db.getCollection ("the_table") .find ()

.

{"_ id": ObjectId ("5d10400adc4728bc5f52f3ca"), "username": "Zhang San"}

{"_ id": ObjectId ("5d104011dc4728bc5f52f3cb"), "username": "Li Si"}

> db.getCollection ("the_table") .find ({"$or": [{"username": "Zhang San"}, {"username": "Li Si"}]})

{"_ id": ObjectId ("5d10400adc4728bc5f52f3ca"), "username": "Zhang San"}

{"_ id": ObjectId ("5d104011dc4728bc5f52f3cb"), "username": "Li Si"}

>

Query for embedded documents:

Embedding a field is just one more step in positioning. In addition, embedded fields are no different from normal fields.

Query all data whose followed is greater than 10:

Db.getCollection ("the_table") .find ({"user.followed": {"$gt": 10}})

If you need to display only part of the embedded document in the returned query results, you can also use a period to achieve it. For example, only "name" and "user_id" are returned, and the query statement:

Db.getCollection ("the_table"). Find

{"user.followed": {"$gt": 10}}

{"_ id": 0, "user.name": 1, "user.user_id": 1}

)

Include or not include:

Find out all data that size contains M:

Db.getCollection ("the_table") .find ({"size": "M"})

Find out all data that size does not contain M:

Db.getCollection ("the_table") .find ({"size": {"$ne": "M"}})

There are elements in the array in another range space:

Db.getCollection ("the_table") .find ({"price": {"$lt": 300, "$gt": 200}})

Array applications:

Query data based on array length:

Query all records with price length 2 from the dataset the_table:

Db.getCollection ("the_table") .find ({"price": {"$size": 2}})

Query the data according to the index (the index starts at 0):

Query the first (index 0) record of all "size" with the data "S". The query statement is:

Db.getCollection ("the_table") .find ({"size.0": "S"})

The size can also be compared using the index. For example, query "price" for all records whose first data is greater than 500:

Db.getCollection ("the_table") .find ({"price.0": {"$gt": 500}})

Advanced updates:

It is best to use $push or $addToSet, both of which add data to the array, but $addToSet is unique and prevents duplicate data

Db.getCollection ("the_table"). Update

{"favorites.movies": "Cassablanca"}

{"$addToSet": {"favorites.movies": "the maltese falcon"}}

False

True

)

First parameter: matches the user who contains Cassablanca in the movie list

Second parameter: add the maltese falcon to the list using $addToSet

The third parameter, false, controls whether upsert is allowed. This command tells the update operation whether to insert a document when it does not exist, depending on whether the update operation is an operator update or a replacement update

The fourth parameter, true, indicates whether to update multiple updates. By default, mongodb updates are only for the first matching document. If you want to update all matching documents, you must display the specified parameter.

Delete data:

Db.user.remove ({"favorites.cities": "Cheyenne"})

Note: the remove () operation does not delete the collection, it only deletes a document in the collection. We can compare it to the delete command in sql. If you want to delete the collection and accompanying index data, you can use the drop () method:

Db.user.drop ()

Aggregate query for MongoDB:

With aggregation, you can let MongoDB process the data directly. The aggregation function can put the data into the conveyor belt, first filter the original data according to certain rules, and then process the data through several different data processing stages, and finally output a summary result.

The command for the aggregate operation is "aggregate", and the basic format is: collection.aggregate ([stage 1, stage 2, stage 3,... , stage N])

Aggregation operations can have 0, l, or more stages. If there are 0 phases, the query command is written as: collection.aggregate (). Then its function is the same as collection.find ().

If there is at least one phase of aggregation, then each stage is a dictionary. Different stages are responsible for different things, and each stage has a keyword. There are phases "$match'3 for filtering data," $pr sentence ect "for data segments, and" $group "for data grouping. There are dozens of different phase keywords for aggregation operations.

In general, not all data needs to be processed, so most of the time the first stage of aggregation is data filtering. Just like find (), select some data that meets the criteria for further processing later. The keyword for data filtering is $match, and its usage is: collection.aggregate ([{"$match": {query expression exactly like find}}])

From the the_table dataset, query all records with age greater than or equal to 27 and sex is "female":

Db.getCollection ("the_table"). Aggregate

{"$match": {"age": {"$gte": 27}, "sex": "female"}}

])

Judging from the query results, the effect of this aggregate query statement is exactly the same as:

Db.getCollection ("the_table") .find ({"age": {"$gte": 27}, "sex": "female"})

The core query statements {"age": {"$gte": 27} and "sex": "female"} are exactly the same. In the aggregate query operation, {"$match": {exactly the same query expression as find}} "," $match "as the Key of a dictionary, the Value of the dictionary is exactly the same as the first parameter of" find () "." Find () "can be written here as the first parameter can be written.

For example, if you query all records whose age is greater than 28 or whose sex is male, the aggregate query can be written as follows:

Db.getCollection ("the_table"). Aggregate

{"$match": {"$or": [{"age": {"$gt": 28}}, {"sex": "male"}]}}

])

In terms of effect, using an aggregate query is exactly the same as using "from nd ()" directly, and using an aggregate query requires a few more keystrokes, so what are its benefits? The advantage of aggregation operation is "combination". Next, we will talk about more aggregation keywords, which can be combined to reflect the power of aggregation operations.

Filter and modify fields:

$project to implement an existing function that returns only part of the field (the field filtering statement here is exactly the same as the second parameter of find ())

Db.getCollection ("the_table"). Aggregate

{"$project": {"_ id": 0, "sex": 1, "age": 1}}

])

Filter the records first, and then filter the fields:

Db.getCollection ("the_table"). Aggregate

{"$match": {"age": {"$gt": 28}

{"$project": {"_ id": 0, "sex": 1, "age": 1}}

])

Add a new field:

Db.getCollection ("the_table"). Aggregate

{"$project": {"_ id": 0, "sex": 1, "age": 1, "newfield": "hello world"}}

{"$match": {"age": {"$gt": 28}

])

(newfield is a field that was not previously available)

Copy an existing field:

Db.getCollection ("the_table"). Aggregate

{"$match": {"age": {"$gt": 28}

{"$project": {"_ id": 0, "sex": 1, "age": 1, "newfield": "$age"}}

])

Modify the data of an existing field:

Db.getCollection ("the_table"). Aggregate

{"$match": {"age": {"$gt": 28}

{"$project": {"_ id": 0, "sex": 1, "age": 1, "newfield": "this is newfield"}}

])

Note: this does not change the data in the database, just the output

(the data has not changed)

Extract nested fields:

The above: add a new field, copy an existing field, modify the data of an existing field, etc., do not seem to be useful, and the following example is useful:

If you use find () and want to return user_id and name, the query statement is:

Db.getCollection ("the_table") .find ({}, {"user.name": 1, "user.user_id": 1})

The returned result is:

(obviously, nested fields are not convenient to deal with)

Now extract the contents of the nested fields with $project:

Db.getCollection ("the_table"). Aggregate

{"$project": {"name": "$user.name", "user_id": "$user.user_id"}}

])

Handle field special values:

What if you want to add a field whose value is the number "1"?

What if you add a field whose value is a normal string, but happens to start with "$"?

Keyword: $literal

Db.getCollection ("the_table"). Aggregate

{"$match": {"age": {"$gt": 28}

{"$project": {"_ id": 0, "id": 1, "hello": {"$literal": "$normalstring"}, "abcd": {"$literal": 1}

])

Grouping operation:

Deduplicated format: db.getCollection ("the_table") .aggregate ([{"$group": {"_ id": "$deduplicated field name"}}])

Db.getCollection ("the_table"). Aggregate

{"$group": {"_ id": "$name"}}

])

Although the grouping operation can also realize the deduplication operation, the data format returned by the grouping operation is different from that of the distinct function. The distinct function returns an array, while the grouping operation returns several records

Remove the weight and count:

Db.getCollection ("the_table"). Aggregate

{"$group": {"_ id": "$name", "max_age": {"$max": "$age"}, "min_age": {"$min": "$age"}, "avg_age": {"$avg": "$age"}, "sum_age": {"$sum": "$age"}

])

In principle, the values of the fields corresponding to the values of $sum and $avg should be numeric. If you force a field with a non-numeric value, $sum returns 0 and $avg returns null. While strings can be compared in size, $max and $min can be applied to string fields normally.

You can also count the number of records using the value of $sum as the number 1:

Db.getCollection ("the_table"). Aggregate

{"$group": {"_ id": "$name", "doc_count": {"$sum": 1}, "max_age": {"$max": "$age"}, "min_age": {"$min": "$age"}, "avg_age": {"$avg": "$age"}, "sum_age": {"$sum": "$age"}

])

Grouping / deduplication, the latest piece of data:

Db.getCollection ("the_table"). Aggregate

{"$group": {"_ id": "$name", "age": {"$last": "$age"}, "address": {"$last": "$address"}

])

You can take the latest data, and naturally you can take the earliest data:

Db.getCollection ("the_table"). Aggregate

{"$group": {"_ id": "$name", "age": {"$first": "$age"}, "address": {"$first": "$address"}

])

Split score group (with keyword: $unwind):

Format: collection.aggregate ([{"$unwind": "$field name"}])

Db.getCollection ("the_table"). Aggregate

{"$unwind": "$size"}

])

You can also split the price array:

Db.getCollection ("the_table"). Aggregate

{"$unwind": "$size"}

{"$unwind": "$price"}

])

Union collection query:

Main collection. Collection ([

{

"$lookup": {

"from": "searched aggregate name"

"localField": "Fields of the main collection"

"foreignField": "Fields of the checked set"

"as": "the field name in which the query results are saved"

}

}

])

The "main set" and "checked set" need to be clarified. If the order is reversed, the result will be different.

For example, now you need to query the user information in the blog collection, then the main collection is the Weibo collection, and the checked set is the user collection. So the query statement can be written as follows:

Db.getCollection ("example_post"). Aggregate

{"$lookup": {

"from": "example_user"

"localField": "user_id"

"foreignField": "id"

"as": "user_info"

}}

])

The reason why the user_info field is an array here is that there may be multiple records in the queried set that meet the criteria, and only an array can be used to save them. Because each record in the user set is unique, this array has only one element)

Join collection queries and beautify the results:

Db.getCollection ("example_post"). Aggregate

{"$lookup": {

"from": "example_user"

"localField": "user_id"

"foreignField": "id"

"as": "user_info"

}}

{"$unwind": "$user_info"}

])

Of course, you can also return specific content from the split result of the join collection query:

Db.getCollection ("example_post"). Aggregate

{"$lookup": {

"from": "example_user"

"localField": "user_id"

"foreignField": "id"

"as": "user_info"

}}

{"$unwind": "$user_info"}

{"$project": {

"content": 1

"post_time": 1

"name": "$user_info.name"

"work": "$user_info.work"

}}

])

Join collection queries on a user-based basis:

Db.getCollection ("example_user"). Aggregate

{"$lookup": {

"from": "example_post"

"localField": "id"

"foreignField": "user_id"

"as": "weibo_info"

}}

{"$unwind": "$weibo_info"}

{"$project": {

"name": 1

"work": 1

"content": "$weibo_info.name"

"post_time": "$weibo_info.work"

}}

])

You can also specify to check only someone's:

Db.getCollection ("example_user"). Aggregate

{"$match": {"name": "Zhang San Mad"}}

{"$lookup": {

"from": "example_post"

"localField": "id"

"foreignField": "user_id"

"as": "weibo_info"

}}

{"$unwind": "$weibo_info"}

{"$project": {

"content": 1

"post_time": 1

"name": "$weibo_info.name"

"work": "$weibo_info.work"

}}

])

(in terms of performance, it is recommended that you put $match first so that you can make full use of mongodb's index.)

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.