Explain the conditional query and sorting of mongodb in detail

Time:2022-3-20

Find method

The basic format of query is dB collection. Find ({condition}), where the condition is optional, similar to the where condition of MySQL. Examples are as follows:

//Find all documents
db.collection.find({});
//Find documents with specified criteria
db.collection.find({key: value});
//Find a user nicknamed "code farmer on the island"
db. users. Find ({nickname: 'code farmer on the island'});

Condition combination

You can use $and, $or, and $not to set combination conditions.

//And query format
db.collection.find({
  $and: [
    {key1: value1}, {key2: value2}
  ]
});

//Or query format
db.collection.find({
  $or: [
    {key1: value1}, {key2: value2}
  ]
});

//Not query format
db.collection.find(
    {key: {$not: {conditional expression}}
);

For example, the following users dataset:

[
  {nickname: 'code farmer on the island', score: 90, gender: 'male'}, 
  {nickname: 'Mary', score: 98, gender: 'female'}, 
  {nickname: 'Tom', score: 76, gender: 'male'}
]

Find users with a score of 90 or 76:


db.users.find({
  $or: [
    {score: 90}, {score: 76}
  ]
});

Find users whose gender is not male and users whose score is higher than 80:


db.users.find({
  gender: {$not: {$eq: 'male'}}
});

db.users.find({
  score: {$not: {$lt: 80}}
});

Where $EQ is equal condition and $lt is less than condition. Conditions can be nested, for example, and and and or, which means (a | b) & & (C | d). The format is as follows:

//And and or nested query format
db.collection.find({
  $and: [
    {$or: [{key1: value1}, {key2: value2}]},
    {$or: [{key3: value1}, {key4: value2}]},
  ]
});
//Find a user whose nickename is a code farmer or Mary on the island and whose score is 90 or 76
db.users.find({
  $and: [
    {$or: [{nickname: 'cannon on the island'}, {nickname: 'Mary'}]},
    {$or: [{score: 90}, {score: 76}]}
  ]
});
//Find users whose nickname is an island coder with a score of 90, or whose nickname is Mary with a score of 76
db.users.find({
  $or: [
    {$and: [{nickname: 'code farmer on the island'}, {score: 90}]},
    {$and: [{nickname: 'Mary'}, {score: 76}]}
  ]
});
//Find users whose nickname is a code farmer on the island and whose score is no less than 80
db.users.find({
  $and: [
    {nickname: 'yard farmer on the island'},
    {score: {$not:{$lt: 80}}}
  ]
});

Comparison operator

Mongodb provides the following comparison operators in the format of {key: {$OP: value}}:

  • $EQ: equality operator, i.e. a = = B;
  • $GT: greater than, i.e. a > b;
  • $GTE: greater than or equal to, i.e. a > = B;
  • $LT: less than, i.e. a < B;
  • $LTE: less than or equal to, i.e. a < = B.

In query

The format of the in query is similar to that of the comparison operator, except that the corresponding value is an array, i.e


db.collection.find({key: {$in: [...]}});

For example, you need to find users with scores of 90 and 76:


db.users.find({score: {$in: [76, 90]}});

In query can also be combined with other conditions, such as and query:


db.users.find({
 $and: [
   {score: {$in: [76, 90]}},
   {gender: 'male'}
   ]
});

Limit the number of returned results and skip data

The find method will find all the data matching the criteria. Therefore, when the data set is large, it will lead to slow speed and generate a lot of disk I / O. if it is determined that there is only one piece of data, findone can be used. If you want to limit the number of queries, limit or skip can be used. Limit is the result returned after the specified quantity is queried, while skip is the result of skipping the previously specified quantity.

//Find n pieces of data of documents with specified conditions
db.collection.find({key: value}).limit(n);
//Example: query 3 users with more than 80 points
db.users.find({score: {$gt: 80}}).limit(3);
//Find the of documents with specified conditions and skip the first n data
db.collection.find({key: value}).skip(n);
//Example: query users with more than 80 points and skip the first three
db.users.find({score: {$gt: 80}}).skip(3);

sort

The sorting format is as follows:

db. collection. Find ({condition}) sort({key: 1});

Where 1 indicates ascending order and – 1 indicates descending order. For example, we need to sort in descending order by score:


db.users.find().sort({score: -1});

summary

This article introduces the conditional query operation of mongodb, limiting the number of returned items and sorting. It can be seen that although the syntax of mongodb operation is different from SQL, it has corresponding functions to assist query, which is also very convenient for query.

The above is a detailed explanation of the conditional query and sorting of mongodb. For more information about the conditional query and sorting of mongodb, please pay attention to other relevant articles of developeppaer!