MongoDB - M001 Chapter 4 - Advanced CRUD operation

Note

MQL syntax: {< field >: {< operator >: < value >}}
Aggregation syntax: {< operator >: {< field >: < value >}}

Basic query operator

db.collection_name.find()

Comparison operator

Common operators:

operator	meaning
$gt	greater than
$lt	less than
$gte	Greater than or equal to
$lte	Less than or equal to
$eq	Matches a value equal to the specified value
$ne	Matches all values that are not equal to the specified value.

Syntax format: {"< field >": {"< operator >": < value >}}
Eg: sample_ How many documents in the training.zip collection have fewer than 1000 people in the pop field?
- db.zips.find({ "pop" : { $lt: 1000 } } ).count()
Find the document of birth year between 1988 and 1999 (without boundary)
- db.collection_name.find({ "birth year" : { $gt: 1998, $lt: 1998 } } ); // value1 < field < value

Find all documents whose triptime is less than or equal to 30 seconds and whose type is not Subscriber:

db.trips.find({ "triptime": { "$lte" : 30 },
			          "type": { "$ne": "Subscriber" } }).pretty()

Logical operator

Common operators:

operator	meaning
$and	All documents matching A and B
$or	All documents matching A or B
$nor	Returns all documents with filtered query criteria
$not	Returns all documents that do not match the query

Syntax format: Eg:

db.collection_name.find({ "$or": [ { "$or" :[ {  },{ }] }]})

$and eg: how many companies in the inspections dataset have A result of A and A sector of B?

db.inspections.find({"$and":[{"result":"A"},{"sector":"B"}]}).count()
perhaps
db.inspections.find({"result":"A","sector":"B" }).count()

$or Eg: find all documents of aircraft A or B taking off or landing at airport C:

db.routes.find({ "$and": [ { "$or" :[ { "dst_airport": "C" },
                                    { "src_airport": "C" }
                                  ] },
                          { "$or" :[ { "airplane": "A" },
                                     { "airplane": "B" } ] }
                         ]}).pretty()

$nor Eg: query documents whose name is not Bob and age is not 20:
- db.test.find( { $nor:[ { "name'': "Bob"}, {age: 3} ] } )

$expr

Syntax format: {$expr: {< expression >}}
effect:
1. Indicates when operators are used
2. Indicates that you are viewing the value of the field, not the field itself
3. We must use the \ (expr operator to compare two field values in each document. Using the \) expr operator is why we must use aggregate syntax for the comparison operator.
4. You do not need to specify the value of each field separately
Eg1: we are comparing the values of two fields in the same document to see if they are equal, using the aggregation syntax of the \ (access field values and use the \) eq operator we saw in the course.
- db.companies.find({ "$expr": { "$eq": [ "$permalink", "$twitter_username" ]}}).count()

Eg2:

// Find all documents where the journey starts and ends at the same station:
 db.trips.find({ "$expr": { "$eq": [ "$end station id", "$start station id"] }
              }).count()

// Find all documents that have a journey duration of more than 1200 seconds and start and end at the same site:
db.trips.find({ "$expr": { "$and": [ { "$gt": [ "$tripduration", 1200 ]},
                         { "$eq": [ "$end station id", "$start station id" ]}
                       ]}}).count()

Eg3: employee > year

db.companies.find(
  { "$expr": { "$gt": [ "$number_of_employees", "$founded_year" ] }}).count()

Array Operations

$push
- effect
  1. Convert field to array field
  2. Adding elements to an array

$size

effect
- Returns all documents whose specified array field is exactly the given length

Eg:

// Find all documents that contain exactly 20 amenities, including all amenities listed in the query array:
db.listingsAndReviews.find({ "amenities": {
                                  "$size": 20, // Document with 20 elements
                                  "$all": [ "Internet", "Wifi",  "Kitchen",
                                           "Heating", "Family/kid friendly",
                                           "Washer", "Dryer", "Essentials",
                                           "Shampoo", "Hangers",
                                           "Hair dryer", "Iron",
                                           "Laptop friendly workspace" ]
                                         }
                            }).pretty()


// You can use the $size operator to select only documents with exactly 50 elements in the comment field
wrong : db.listingsAndReviews.find({ "reviews":{ "$eq": 50 }}).count()

right : db.listingsAndReviews.find({ "reviews": {"$size": 50}}).count()

$all

effect
- Returns all documents that contain at least these specified elements in the specified collection

Eg:

// return all listings that have "A", "B"as part of their amenities, and at least 2 C in the abc collection?
db.abc.find(
  { "amenities":
      { "$all": [ "A", "B" ] }, "C": { "$gte":  2 } } ).pretty()

Array operators and projections

Add a projection for the find() query method to view only the fields of interest
Syntax: dB. < collection_ name>.find({ <query> }, {<projection> })
- 1 specify the fields to view 0 specify the fields you do not want to view
- Note: however, 1 and 0 cannot be used together in a single projection query
  - Except for {"_id": 0, < field >: 1}, because_ ID exists by default

Eg1: find all documents containing exactly 20 amenities, including all amenities listed in the query array, and display their prices and addresses:

db.listingsAndReviews.find({ "amenities":
        { "$size": 20, "$all": [ "Internet", "Wifi",  "Kitchen", "Heating",
                                 "Family/kid friendly", "Washer", "Dryer",
                                 "Essentials", "Shampoo", "Hangers",
                                 "Hair dryer", "Iron",
                                 "Laptop friendly workspace" ] } },
                            {"price": 1, "address": 1}).pretty()

Eg2: find all documents with Wifi as one of the amenities, and only include the price and address in the result cursor:

db.listingsAndReviews.find({ "amenities": "Wifi" },
                           { "price": 1, "address": 1, "_id": 0 }).pretty()

$elemMatch

Function: $elemMatch can be used to accurately find when there is too much data in embedded / containing array documents, so as to avoid inaccurate data. This operator qualifies a set of conditions to match a single document in the array

Eg1: find all files where students in class 431 get more than 85 points in any type of homework: scores is an array

db.grades.find({ "class_id": 431 },
               { "scores": { "$elemMatch": { "score": { "$gt": 85 } } }}).pretty()

Eg2: for example, there is a set of data. john's scores in several courses are 1,2,3 and bob's scores in several courses are 3,4,5. We want to find out which course john got a grade of 3
- ```
db.grades.find({"class.name":"john", "class.score":9});  
//The data found in this way will have all john's grades and bob's courses with a grade of 3.

VS

db.grades.find({"class":{"$elemMatch":{"name":"joe", "score":3}}});  
// This is the correct way to find it
```
- reference resources: https://blog.csdn.net/weixin_49485080/article/details/117651217?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522163819938216780271572022%2522%252C%2522scm%2522%253A%252220140713.130102334..%2522%257D&request_id=163819938216780271572022&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2allbaidu_landing_v2~default-4-117651217.first_rank_v2_pc_rank_v29&utm_term=%24elemMatch&spm=1018.2226.3001.4187
Eg3: sample_ How many companies in the training.companies collection have offices in Seattle? Offices is an array with different fields and values, such as city:Seattle, etc
- db.companies.find({"offices":{"$elemMatch":{"city":"Seattle"}}}).count();

Array operators and subdocuments

For example, if there are elements a, b and C under an array a, we need to ask the element b, then we need to express it as: A.b.

Eg1:

db.companies.find({ "relationships.0.person.first_name": "Mark",
                    "relationships.0.title": { "$regex": "CEO" } },
                  { "name": 1 }).count()
// Use the $regex operator to set the regular expression that matches the string