MongoDB query operations can achieve most of the common query operations of relational databases. This paper explains the common query operations of MongoDB.

Before reading this article, it is recommended to read MongoDB Installation and Documentation Basics.

Before the operation explanation, show the existing documents in MongoDB, set name article

Conditional size comparison operation

When querying documents, filter the size and scope of criteria. The following are common comparison operators

The operator	instructions
$eq	Query documents equal to conditional values, similar to relational database =
$ne	Query and condition value is not equal or do not exist documents, similar to the relational database! =
$gt	Query documents greater than a conditional value, similar to a relational database >
$gte	Query documents greater than or equal to a conditional value, similar to >= in a relational database
$lt	Query documents with less than a conditional value, similar to < in a relational database
$lte	Query documents that are less than or equal to a conditional value, similar to <= in a relational database
$in	Query $in data for the value of the document, similar to a relational database in
$nin	In contrast to a $IN query, not in is similar to a relational database

Since the greater than, less than, and equal relationships are almost the same, this is an example of using $gte to get a visitor greater than or equal to 150

db.article.find({"visitor": {$gte: 150}})Copy the code

Execution Result:

With $IN, you have to set conditional values with arrays, such as getting visitor values of 70 and 150

db.article.find({"visitor": {$in: [70, 150]}})Copy the code

Execution Result:

Logical operator

In multi-conditional queries, conditional concatenation symbols are called logical operators. Common operators:

The operator	instructions
$and	It is valid when all conditions are met
$nor	with$andConversely, this is true if all conditions are not met
$or	This is true as long as one condition is satisfied
$not	Indicates that the field exists and does not meet the condition

$and query author=ytao and visitor=150

db.article.find(
    {$and:[
      {"author": {$eq:"ytao"}},
      {"visitor": {$eq: 150]}}})Copy the code

$nor query documents that are not author=ytao and not visitor=170

db.article.find(
    {$nor:[
      {"author": {$eq:"ytao"}},
      {"visitor": {$eq: 170]}}})Copy the code

$or query the document where author=ytao or visitor=170

db.article.find(
    {$or:[
      {"author": {$eq:"ytao"}},
      {"visitor": {$eq: 170]}}})Copy the code

$not query documents that are not author=ytao

db.article.find(
    {"author": {$not: {$eq:"ytao"}}})Copy the code

Element operator

Operators on field elements are called element operators

The operator	instructions
$exists	Determine if a field exists in the document,trueFor the present,falseFor there is no
$type	Filters documents for the specified field type

$exists Queries a document where the author field exists

db.article.find(
    {"author": {$exists:true}})Copy the code

$type queries for documents whose author fields are arrays

db.article.find(
    {"author": {$type:"array"}})Copy the code

Regular expression

MongoDB supports regular expression matching documents, through the regular expression we can achieve fuzzy query of relational database, and more powerful matching rules, its use of syntax has three:

{ < field >: { $regex: /pattern/, $options:'<options>' } }
{ < field >: { $regex: 'pattern', $options:'<options>' } }
{ < field >: { $regex: /pattern/<options> } }
Copy the code

The parameters /pattern/ and ‘pattern’ both represent regular expressions and add strings directly to them for fuzzy queries. The $options parameter is optional and can be selected from four fixed values

The options options	instructions
i	The matching process ignores case
x	The matching process ignores Spaces
m	Matches multiple rows of data, but from the start and end of each row
s	Converts multiple lines to a single line, matching newline characters\nstring

Example of fuzzy query whose author is Tao:

db.article.find(
    {"author": {$regex:/Tao/, $options:'i'}})Copy the code

The query results

As can be seen from the above query results, the data format can also be matched to.

Aggregation operations

Aggregation operations can achieve grouping, sorting, paging, multi-set associated query, etc., using the syntax format:

Db.collection. aggregate([{aggregate 1}, {aggregate 2}])Copy the code

Conditions for screening

$match is used to filter conditions. You can use some conditions to query.

Syntax format:

db.article.aggregate([
    { $match}])Copy the code

Query document where author = ytao and visitor > 100

db.article.aggregate([
    { $match: {
        $and: [{"author": {$eq: "ytao"}},
            {"visitor": {$gt: 100}}]}}])Copy the code

Grouping operation

$group is the grouping operator, similar to the group by operation in a relational database. The syntax format is:

db.collection.aggregate([
    {
        $group: {"_id":$< group field name >, < field name to display results >:{< operator >:"$< field name evaluated by operator >"}}}])Copy the code

The operators are as follows:

The operator	instructions
$avg	The average of the current group
$sum	The sum of the current group
$min	The minimum value of the current group
$max	Maximum value of the current group
$first	The first value of the current group
$last	The last value of the current group
$push	Array displays the specified current group field values
$addToSet	The array form shows the non-repeating values of the specified current group field

Example of grouping the visitor average for each author

db.article.aggregate([
    {
        $group: {"_id":"$author"."avg_visitor": {$sum:"$visitor"}}}])Copy the code

Field shows

**$project** specifies that the default value of the field is 0, but the default value of the _id is 1.

db.collection.aggregate([
    {
        $project: {"< field name >": < > 0 or 1."< field name >":<0或1>
        }
    }
])
Copy the code

Show examples of title and visitor fields:

db.article.aggregate([
    {
        $project: {"_id": 0."title": 1,
            "visitor": 1}}])Copy the code

At the same time, $split $substr $concat $switch $toLower $toLower $toUpper $toUpper Grammar:

db.collection.aggregate([
    {
        $project: {"< field name >": {< operator >: < condition >},"< field name >": {< operator >: < condition >},}}])Copy the code

For example, convert all letters in title to uppercase

db.article.aggregate([
    {
        $project: {"titleField": {$toUpper:"$title"}}}])Copy the code

Returns the result

The sorting operation

六四事件sortSorting by1and-1 ‘indicates positive and reverse order.

Syntax format:

db.collection.aggregate([
    {
        $sort: {"< sort field name >": <1 or -1>}}])Copy the code

Sort by visitor field name in reverse order:

db.article.aggregate([
    {
        $sort: {"visitor": -1}}])Copy the code

Sorting result

paging

Paging usinglimitPaging is performed.$skipRepresents the number of skipped documents,$limitRepresents the number of documents returned, used by these two directives, similar to those in relational datalimit <start>, <size>Paging operations.

Syntax format:

db.collection.aggregate([
    {$skip: < number of documents skipped >}, {$limit: < number of documents returned >}])Copy the code

Two examples of querying data from page 2:

db.article.aggregate([
    {$skip: 2},
    {$limit: 2}])Copy the code

Returns the result

Count documents

$count is used to count the number of documents for conditional filtering.

Syntax format:

db.collection.aggregate([
    { $count: "< name of field to display quantity >"}])Copy the code

Total number of documents:

db.article.aggregate([
    { $count: "The number"}])Copy the code

Statistical results:

Multi-set associated query

$LOOKUP is used for associative query of multiple collections, similar to relational database linked table query.

Use grammar:

db.collection.aggregate([
    { 
        $lookup: {from: < table name >,localField: < current table associated Field >, foreignField: < associated table associated Field >, as: < other set embedded Field name >}}])Copy the code

Before the demonstration of multi-set associative query, add a collection person and add one item of data:

Select * from age where age = 18;

db.article.aggregate([
    { 
        $lookup: {
            from: "person".localField: "author",
            foreignField: "author",
            as: "person_info"}}, {$match: {"person_info.age": {$eq: 18}}}])Copy the code

Return result:

conclusion

After understanding the common query operation of MongoDB, we can find that it has many similar operation ideas with relational data operation. The use of these operations is relatively flexible, and the API provided is relatively powerful, which can almost meet the retrieval requirements of most use scenarios. By mastering these query operations, you can obtain documents in MongoDB more efficiently.

Recommended reading

MongoDB Installation and Documentation Basics

Implementation of Dubbo Load Balancing

JDK Dynamic Proxy and CGLIB Dynamic Proxy you must Know

Redis5 new feature Streams for message queuing