Advanced mongodb 1 advanced query and advanced mongodb Query

Advanced mongodb 1 advanced query and advanced mongodb Query

In the previous article, we talked about the basic operation http://blog.csdn.net/stronglyh/article/details/46812579 of mongodb crud.

In this article, we will talk about advanced mongodb ------------ advanced query.

I. Various queries

1: Conditional Operators

<, <=,>,> = This operator does not need to be explained. It is the most commonly used and simplest.

Db. collection. find ({"field": {$ gt: value}); // greater than: field> value
Db. collection. find ({"field": {$ lt: value}); // less than: field <value
Db. collection. find ({"field": {$ gte: value}); // greater than or equal to: field> = value
Db. collection. find ({"field": {$ lte: value}); // less than or equal to: field <= value
If you want to satisfy multiple conditions at the same time, you can do so.

Db. collection. find ({"field": {$ gt: value1, $ lt: value2}); // value1 <field <value2

To practice, we need to insert a few pieces of data first.

Db. users. insert ([{name: "Tom", age: 20, sex: "m" },{ name: "Lily", age: 40, sex: "f "}, {name: "Suby", age: 18}]);

2: $ all matches all

This operator is similar to the SQL syntax in, but the difference is that in only needs to satisfy a value in (), and $ all must satisfy all values in [], for example:

Db. users. find ({age: {$ all: [6, 8]});

{Name: 'David ', age: 26, age: [6, 8, 9]}

However, no {name: 'David ', age: 26, age: [6, 7, 9]} can be found.

3: $ exists

Query data with a field age

> Db. c1.find ({age: {$ exists: true }});
{"_ Id": ObjectId ("4fb4a773afa87dc1bed9432d"), "age": 20, "length": 30}

4: null Value Processing

The processing of Null values is a bit strange. For details, refer to the following sample data:

> Db. c2.find ()
{"_ Id": ObjectId ("4fc34bb81d8a39f01cc17ef4"), "name": "Lily", "age": null}
{"_ Id": ObjectId ("4fc34be01d8a39f01cc17ef5"), "name": "Jacky", "age": 23}
{"_ Id": ObjectId ("4fc34c1e1d8a39f01cc17ef6"), "name": "Tom", "addr": 23}
Here, the "Lily" age field is empty, and Tom does not have the age field. We want to find the row with the age empty, as shown below:

> Db. c2.find ({age: null })
{"_ Id": ObjectId ("4fc34bb81d8a39f01cc17ef4"), "name": "Lily", "age": null}
{"_ Id": ObjectId ("4fc34c1e1d8a39f01cc17ef6"), "name": "Tom", "addr": 23}
The strange thing is that we thought we could only find "Lily", but "Tom" was also found, because "null" not only matches the document with a key value of null, it also matches documents that do not contain this key. So how can we only find Lily? We can use exists to limit it.

5: $ mod modulo operation
Query the data of age modulo 10 or 0

Db. student. find ({age: {$ mod: [10, 1]})

Example:

The data in table C1 is as follows:

Db. c1.find ()
{"_ Id": ObjectId ("4fb4af85afa87dc1bed94330"), "age": 7, "length_1": 30}
{"_ Id": ObjectId ("4fb4af89afa87dc1bed94331"), "age": 8, "length_1": 30}
{"_ Id": ObjectId ("4fb4af8cafa87dc1bed94332"), "age": 6, "length_1": 30}
Query the data of age modulo 6 equal to 1

> Db. c1.find ({age: {$ mod: [6, 1]})
{"_ Id": ObjectId ("4fb4af85afa87dc1bed94330"), "age": 7, "length_1": 30}
It can be seen that only data with the age modulo of 6 or 1 is displayed, and other data that does not comply with the rules is not displayed.

6: $ ne is not equal
Query data whose x value is not 3

Db. things. find ({x :{$ ne: 3 }});

Example:

The data in table C1 is as follows:

> Db. c1.find ()
{"_ Id": ObjectId ("4fb4af85afa87dc1bed94330"), "age": 7, "length_1": 30}
{"_ Id": ObjectId ("4fb4af89afa87dc1bed94331"), "age": 8, "length_1": 30}
{"_ Id": ObjectId ("4fb4af8cafa87dc1bed94332"), "age": 6, "length_1": 30}
Query data whose age value is not equal to 7

> Db. c1.find ({age: {$ ne: 7 }});
{"_ Id": ObjectId ("4fb4af89afa87dc1bed94331"), "age": 8, "length_1": 30}
{"_ Id": ObjectId ("4fb4af8cafa87dc1bed94332"), "age": 6, "length_1": 30}
We can see that only data with age not 7 is displayed, and other data that does not comply with the rules is not displayed.

7: $ in contains
The same as the standard SQL syntax, that is, the query is within the range of a series of enumerated values.

Query the data of x in the range of 2, 4, and 6.

Db. things. find ({x :{$ in: [2, 4, 6]});

Example:

The data in table C1 is as follows:

> Db. c1.find ()
{"_ Id": ObjectId ("4fb4af85afa87dc1bed94330"), "age": 7, "length_1": 30}
{"_ Id": ObjectId ("4fb4af89afa87dc1bed94331"), "age": 8, "length_1": 30}
{"_ Id": ObjectId ("4fb4af8cafa87dc1bed94332"), "age": 6, "length_1": 30}
Query the data of age in the range of 7 to 8.

> Db. c1.find ({age: {$ in: []});
{"_ Id": ObjectId ("4fb4af85afa87dc1bed94330"), "age": 7, "length_1": 30}
{"_ Id": ObjectId ("4fb4af89afa87dc1bed94331"), "age": 8, "length_1": 30}
We can see that only data with age equal to 7 or 8 is displayed, and other data that does not comply with the rules is not displayed.

"$ In" is very flexible. You can specify different types of conditions and values.

8: $ nin does not contain
The same as the standard SQL syntax, that is, the data to be queried is out of the range of a series of enumerated values.

Query data with the x value out of the range of 2, 4, and 6.

Db. things. find ({x :{$ nin: [2, 4, 6]});

Example:

The data in table C1 is as follows:

> Db. c1.find ()
{"_ Id": ObjectId ("4fb4af85afa87dc1bed94330"), "age": 7, "length_1": 30}
{"_ Id": ObjectId ("4fb4af89afa87dc1bed94331"), "age": 8, "length_1": 30}
{"_ Id": ObjectId ("4fb4af8cafa87dc1bed94332"), "age": 6, "length_1": 30}
Query data with age values out of the range of 7 or 8.

> Db. c1.find ({age: {$ nin: []});
{"_ Id": ObjectId ("4fb4af8cafa87dc1bed94332"), "age": 6, "length_1": 30}
We can see that only data with age not equal to 7 or 8 is displayed, and other data that does not comply with the rules is not displayed.

9: $ size array element count
For {name: 'David', age: 26, favorite_number: [6, 7, 9]} records

Match db. users. find ({favorite_number :{$ size: 3 }});

Does not match db. users. find ({favorite_number: {$ size: 2 }});

"$ Size" is also very useful for querying arrays. As the name suggests, you can use it to query arrays of a specific length. For example:

> Db. users. find ({favorite_number: {$ size: 3 }})
Getting a document within the length range is a common query. "$ Size" cannot be combined with other query conditions (such as "$ gt"), but this query can be achieved by adding a "size" Key to the document. In this way, each time an element is added to a specified array, the value of "size" is added. For example, the original update:

> Db. users. update (criteria, {"$ push": {"favorite_number": "1 "}})
It will be changed to the following:

> Db. users. update (criteria,
... {"$ Push": {"favorite_number": "1"}, "$ inc": {"$ size": 1 }})
Auto-increment operations are very fast, so there is little impact on performance. After storing the document, you can query it as follows:

> Db. users. find ({"$ size": {"$ gt": 3 }})
Unfortunately, this technique cannot be used together with the "$ addToSet" operator.

10: Regular Expression matching
Query records that do not match name = B *

Db. users. find ({name: {$ not:/^ B .*/}});

Example:

The data in table C1 is as follows:

> Db. c1.find ();
{"_ Id": ObjectId ("4fb5faaf6d0f9d8ea3fc91a8"), "name": "Tony", "age": 20}
{"_ Id": ObjectId ("4fb5fab96d0f9d8ea3fc91a9"), "name": "Joe", "age": 10}

11: javascript query and $ where Query
To query data greater than 3, the following query methods are similar.

Db. c1.find ({a :{$ gt: 3 }});

Db. c1.find ({$ where: "this. a> 3 "});

Db. c1.find ("this. a> 3 ");

F = function () {return this. a> 3;} db. c1.find (f );

12: count
Count

Db. users. find (). count ();

The number of all records in the user table is not 5.

Db. users. find (). skip (10). limit (5). count ();

If you want to return the number of records after the limit, use count (true) or count (not 0)

Db. users. find (). skip (10). limit (5). count (true );

Example:

The data in table C1 is as follows:

> Db. c1.find ()
{"_ Id": ObjectId ("4fb5faaf6d0f9d8ea3fc91a8"), "name": "Tony", "age": 20}
{"_ Id": ObjectId ("4fb5fab96d0f9d8ea3fc91a9"), "name": "Joe", "age": 10}
Query the data volume of table c1

> Db. c1.count ()
2
We can see that the table contains two data entries.

Ii. cursor
1: The find command does not directly return results, but returns an iterator of the result set, that is, a cursor.

Like most database products, MongoDB uses a cursor to process each result data cyclically. The specific syntax is as follows:

> For (var c = db. t3.find (); c. hasNext ();){
... Printjson (c. next ());
...}
{"_ Id": ObjectId ("4fb8e4838b2cb86417c9423a"), "age": 1}
{"_ Id": ObjectId ("4fb8e4878b2cb86417c9423b"), "age": 2}
{"_ Id": ObjectId ("4fb8e4898b2cb86417c9423c"), "age": 3}
{"_ Id": ObjectId ("4fb8e48c8b2cb86417c9423d"), "age": 4}
{"_ Id": ObjectId ("4fb8e48e8b2cb86417c9423e"), "age": 5}

2: MongoDB has another way to process the cursor.

> Db. t3.find (). forEach (function (u) {printjson (u );});
{"_ Id": ObjectId ("4fb8e4838b2cb86417c9423a"), "age": 1}
{"_ Id": ObjectId ("4fb8e4878b2cb86417c9423b"), "age": 2}
{"_ Id": ObjectId ("4fb8e4898b2cb86417c9423c"), "age": 3}
{"_ Id": ObjectId ("4fb8e48c8b2cb86417c9423d"), "age": 4}

3: skip limit the start point of the returned record
Returns 5 Records (limit 3, 5) starting from 3rd records)

Db. users. find (). skip (3). limit (5 );

Example:

The data in table C1 is as follows:

> Db. c1.find ()
{"_ Id": ObjectId ("4fb5faaf6d0f9d8ea3fc91a8"), "name": "Tony", "age": 20}
{"_ Id": ObjectId ("4fb5fab96d0f9d8ea3fc91a9"), "name": "Joe", "age": 10}

4: sort sorting
Asc in ascending order of age

Db. users. find (). sort ({age: 1 });

Desc in descending order of age

Db. users. find (). sort ({age:-1 });

The data in table C1 is as follows:

> Db. c1.find ()
{"_ Id": ObjectId ("4fb5faaf6d0f9d8ea3fc91a8"), "name": "Tony", "age": 20}
{"_ Id": ObjectId ("4fb5fab96d0f9d8ea3fc91a9"), "name": "Joe", "age": 10}
Query c1 tables in ascending order by age

> Db. c1.find (). sort ({age: 1 });
{"_ Id": ObjectId ("4fb5fab96d0f9d8ea3fc91a9"), "name": "Joe", "age": 10}
{"_ Id": ObjectId ("4fb5faaf6d0f9d8ea3fc91a8"), "name": "Tony", "age": 20}
1st rows are age = 10, and the result set is sorted in ascending order.

Iii. Stored Procedure

The stored procedure of a relational database is described as follows: a set of SQL statements for specific functions are stored in the database after compilation, you can run a stored procedure by specifying its name and providing parameters (if the stored procedure has parameters.

MongoDB provides a series of solutions for many problems. For the features of other databases, it is still not weak, and the performance is extraordinary. MongoDB also supports stored procedures, but mongoDB is written in javascript, which is just the charm of mongoDB.

1: stored procedure for converting SQL udfs to MongoDB
MongoDB also supports stored procedures. The first thing you need to know about a stored procedure is that it is written in javascript. Maybe this will make you wonder why it is written in javascript, but in fact it will make you very satisfied that the MongoDB stored procedure is stored in db. system. in the js table, we imagine a simple SQL custom function as follows:

Function addNumbers (x, y ){
Return x + y;
}
Next we will convert this SQL user-defined function to the MongoDB storage process:

> Db. system. js. save ({_ id: "addNumbers", value: function (x, y) {return x + y ;}});:

2: Query stored procedures
The stored procedure can be viewed, modified, and deleted. Therefore, we can use find to check whether the stored procedure has been created.

> Db. system. js. find ()
{"_ Id": "addNumbers", "value": function cf _ 1 _ f _ (x, y ){
Return x + y;
}}
>

3: eval function
The above looks good. I think the actual calling of this stored procedure is as follows:

> Db. eval ('addnumbers (3, 4.2 )');
7.2
>
This operation method is too simple. Maybe this is the charm of MongoDB.

Db. eval () is a strange thing. We can directly store the Stored Procedure logic and call it at the same time without the logic of the stored procedure to be declared in advance.

> Db. eval (function () {return 3 + 3 ;});
6
>
It can be seen from the above that the MongoDB storage process can easily Complete Arithmetic Operations, but other database products can process some internal tasks in the database during the storage process, for example, how can I retrieve the data volume of a table and perform other operations? The answer is yes. MongoDB can be easily implemented. Let's look at the following example:

> Db. system. js. save ({_ id: "get_count", value: function () {return db. c1.count ();}});
> Db. eval ('get _ count ()')
2
You can see that the stored procedure allows you to operate tables easily in the stored procedure.

Thank you for your support at http://hubwiz.com.

Zookeeper

Copyright Disclaimer: This article is an original article by the blogger and cannot be reproduced without the permission of the blogger.