Of Stack

Summary of basic query and index operation methods of MongoDB

  • 2020-06-12 10:53:34
  • OfStack

Query operation

1. Query all records 


db.userInfo.find();

Is equivalent to: 


select* from userInfo;

2, query the deleted duplicate data of a column in the current aggregate collection 


db.userInfo.distinct("name");

The same data in name is filtered out

Is equivalent to: 


select disttince name from userInfo;

3. Query the record of age = 22 


db.userInfo.find({"age": 22});

Is equivalent to: 


select * from userInfo where age = 22;

4. Query age > The record of 22 


db.userInfo.find({age: {$gt: 22}});

Is equivalent to: 


select * from userInfo where age >22;

5, query age < The record of 22 


db.userInfo.find({age: {$lt: 22}});

Is equivalent to: 


select * from userInfo where age <22;

6. Query age > = 25 records 


select* from userInfo;
0

Is equivalent to: 


select * from userInfo where age >= 25;

7. Query age < = 25 records 


db.userInfo.find({age: {$lte: 25}});

Is equivalent to: 


select* from userInfo;
3

8. Query age > = 23 and age < = 26 


select* from userInfo;
4

Is equivalent to: 


select* from userInfo;
5

9. Query the data contained in mongo 


select* from userInfo;
6

Is equivalent to: 


select* from userInfo;
7

10. Query name starting with mongo 


select* from userInfo;
8

Is equivalent to: 


select* from userInfo;
9

11. Query the data of the specified columns name and age 


db.userInfo.find({}, {name: 1, age: 1});

Is equivalent to: 


select name, age from userInfo;

Of course name can also use true or false, when using ture river name:1 effect 1, if using false is to exclude name, display name other column information.

12. Query the specified columns name, age data, age > 25 


db.userInfo.find({age: {$gt: 25}}, {name: 1, age: 1});

Is equivalent to: 


select name, age from userInfo where age >25;

13. Rank them by age

Ascending order: 


db.userInfo.find().sort({age: 1});

Descending order: 


db.userInfo.find().sort({age: -1});

14. Query the first 5 data 


db.userInfo.distinct("name");
6

Is equivalent to: 


db.userInfo.distinct("name");
7

15. Query the data after 10 pieces 


db.userInfo.distinct("name");
8

Is equivalent to:

select * from userInfo where id not in (select id from (select * from userInfo) where  and rownum < 11);

16. Query data between 5 and 10 

db.userInfo.distinct("name");
9

Can be used for pagination, limit is pageSize, skip is page *pageSize

17. or and query 


db.userInfo.find({$or: [{age: 22}, {age: 25}]});

Is equivalent to: 


select disttince name from userInfo;
1

18. Query the data in article 1 


select disttince name from userInfo;
2

Is equivalent to: 


select disttince name from userInfo;
3

19. The number of records in a query result set 


select disttince name from userInfo;
4


The index

1. Create indexes 


select disttince name from userInfo;
5

In MongoDB, we can also create composite indexes as follows: 


select disttince name from userInfo;
6

Once the index is created, username and age based queries will use the index, or ES170en-based queries will use the index, but age-based queries alone will not use the composite index. So it can be said that if you want to use a composite index, you must include the first N index columns in the composite index in the query criteria. However, if the order of key values in the query condition and the order of creation in the composite index are different, MongoDB can intelligently help us adjust the order so that the composite index can be used by the query. Such as: 


select disttince name from userInfo;
7

For the query criteria in the above example, MongoDB dynamically adjusts the order of the query criteria documents before retrieving them so that the query can use the composite index just created.

2. Create only one index

None of the indexes created by default are unique. The following example creates a unique index, such as: 


select disttince name from userInfo;
8

If a duplicate userid document is inserted again, MongoDB will report an error to prompt for the duplicate key, such as: 


db.test.insert({"userid":5})

db.test.insert({"userid":5})



db.userInfo.find({"age": 22});
0

If the inserted document does not contain the userid key, then the value of the key in the document is null. If the similar document is inserted multiple times, MongoDB will report the same error, such as: 


db.userInfo.find({"age": 22});
1 

db.userInfo.find({"age": 22});
2

If duplicates already exist when creating the only index, we can use the following command to help us eliminate duplicates when creating the only index and keep only the first document found, such as:

Delete the only index you just created. 


db.userInfo.find({"age": 22});
3

Insert test data to ensure duplicate keys exist in the collection. 


db.userInfo.find({"age": 22});
4

-- Create only one index and eliminate duplicates. 


db.userInfo.find({"age": 22});
5

-- The query results confirm that the duplicate key was indeed dropped when the index was created. 


db.userInfo.find({"age": 22});
6

We can also create a compound index with only 1, that is, make sure that the compound key value is only 1. Such as: 


db.userInfo.find({"age": 22});
7

3. Query all indexes of the current collection 


db.userInfo.find({"age": 22});
8

4. View the total index record size 


db.userInfo.find({"age": 22});
9

5. Read all index information of the current collection 


select * from userInfo where age = 22;
0

6. Delete the specified index 


select * from userInfo where age = 22;
1

7. Delete all indexes 


select * from userInfo where age = 22;
2

Related articles: