# <center><i class="fa fa-edit"></i> Navigating MongoDB: Advanced CRUD Operators </center>
###### tags: `Internship`
:::info
**Goal:**
To gain a basic understanding of MongoDB fundamentals. Focus on advanced CRUD operators.
- [x] Comparison Operators
- [x] Logic Operators
- [x] Expressive Query Operator
- [x] Array Operators and Projections
- [x] Querying Ararys and Sub-Documents
**Resources:**
[MongoDB Basics Online Course](https://university.mongodb.com/courses/M001/about)
[MongoDB 常用Query指令](/b2Y7lgR1RaeQyMHp9RGHdQ)
[Database Commands](https://www.mongodb.com/docs/manual/reference/command/)
:::
## Comparison Operators
**Operators**
Syntax: `{<field>: {<operator>: <value>} }`
- `$eq`: equal to
- `$ne`: not equal to
- `$gt`: greater than
- `$lt`: less than
- `$gte`: greater than or equal to
- `$lte`: less than or equal to
**Procedures**
- Connect to Atlas Cluster
```
mongo "mongodb+srv://<username>:<password>@<cluster>.mongodb.net/admin"
```
- Navigate to database
```
use sample_training
```
- Find all documents where the `tripduration` was less than or equal to `70` seconds and the `usertype `was not `Subscriber`:
```
db.trips.find({ "tripduration": { "$lte" : 70 },
"usertype": { "$ne": "Subscriber" } }).pretty()
```
- Find all documents where the `tripduration` was less than or equal to `70` seconds and the `usertype` was `Customer` using a redundant equality operator:
```
db.trips.find({ "tripduration": { "$lte" : 70 },
"usertype": { "$eq": "Customer" }}).pretty()
```
- Find all documents where the `tripduration` was less than or equal to `70` seconds and the `usertype` was `Customer` using the implicit equality operator:
```
db.trips.find({ "tripduration": { "$lte" : 70 },
"usertype": "Customer" }).pretty()
```
### Logic Operators
**Operators**
- `$and`: match all specified query clauses
- `$or`: at least one of the query clasues is matched
- `$nor`: fail to match both given clauses
Syntax: `{<operator>: [{statement 1}, {statement 2}, {statement 3}, ...]}`
- `$not`: negates the query requirement
Syntax: `{$not: {statement}}`
**Procedures**
- Connect to the Atlas Cluster
```
mongo "mongodb+srv://<username>:<password>@<cluster>.mongodb.net/admin"
```
- Navigate to database
```
use sample_training
```
- Find all documents where airplanes `CR2` or `A81` left or landed in the `KZN` airport:
```
db.routes.find({ "$and": [ { "$or" :[ { "dst_airport": "KZN" },
{ "src_airport": "KZN" }
] },
{ "$or" :[ { "airplane": "CR2" },
{ "airplane": "A81" } ] }
]}).pretty()
```
### Expressive Query Operator
**Operators**
- `$`: denotes the use of an operator and addresses the field value
- `$expr`: allows the use of aggregation expressions within the query language (*variables and conditional statements*)
Syntax: `{$expr: { <expressions> } }`
**Procedures**
- Connect to Atlas CLuster
```
mongo "mongodb+srv://<username>:<password>@<cluster>.mongodb.net/admin"
```
- Navigate to database
```
use sample_training
```
- Find all documents where the trip started and ended at the same station:
```
db.trips.find({ "$expr": { "$eq": [ "$end station id", "$start station id"] }
}).count()
```
- Find all documents where the trip lasted longer than `1200` seconds, and started and ended at the same station:
```
db.trips.find({ "$expr": { "$and": [ { "$gt": [ "$tripduration", 1200 ]},
{ "$eq": [ "$end station id", "$start station id" ]}
]}}).count()
```
### Array Operators and Projection
- Projection: allows us to decide which document fields will be part of the resulting cursor
Syntax: `db.<collection>.find({ <query> }, {<projection>})`
`1` - include the field
`0 `- exclude the field
:::danger
**CANNOT USE BOTH IN A PROJECTION. ONLY 1s OR ONLY 0S.**
:::
```
db.<collection>.find({query}, {<field1>: 1, <field2>: 1})
```
```
db.<collection>.find({query}, {<field1>: 1, <field2>: 1})
```
:::warning
By default, the `_id` values will be included in the resulting cursor. We can fix this with the following exception:
```
db.<collection>.find({query}, {<field1>: 1, "_id": 0})
```
:::
**Operators**
- `$push`: allows us to add an element to an array. When the field is not an array, the operation will fail. However, if the field is absent in the document to update, $push adds the array field with the value as its element.
**Procedures**
- Connect to Atlas CLuster
```
mongo "mongodb+srv://<username>:<password>@<cluster>.mongodb.net/admin"
```
- Navigate to database
```
use sample_airbnb
```
- Find all documents with exactly `20` amenities which include all the amenities listed in the query array:
```
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" ]
}
}).pretty()
```
**With Projections**
- Find all documents that have `Wifi` as one of the amenities only include `price` and `address` in the resulting cursor:
```
db.listingsAndReviews.find({ "amenities": "Wifi" },
{ "price": 1, "address": 1, "_id": 0 }).pretty()
```
- Find all documents that have `Wifi` as one of the amenities only include `price` and `address` in the resulting cursor, also exclude ``"maximum_nights"``. **This will be an error:*
```
db.listingsAndReviews.find({ "amenities": "Wifi" },
{ "price": 1, "address": 1,
"_id": 0, "maximum_nights":0 }).pretty()
```
- Navigate to different database
```
use sample_training
```
- Get one document from the collection:
```
db.grades,findOne()
```
- Find all documents where the student in class `431` received a grade higher than `85` for any type of assignment:
```
db.grades.find({ "class_id": 431 },
{ "scores": { "$elemMatch": { "score": { "$gt": 85 } } }
}).pretty()
```
- Find all documents where the student had an extra credit score:
```
db.grades.find({ "scores": { "$elemMatch": { "type": "extra credit" } }
}).pretty()
```
### Querying Arrays and Sub-Documents
MQL uses dot notation to specify the elements in the document.
Syntax: `db.<collection>.find({"field1.other field.also a field": "value"})`
**Operators**
- `$regex`: specifies regular expressions
**Procedures**
- Connect to Atlas CLuster
```
mongo "mongodb+srv://<username>:<password>@<cluster>.mongodb.net/admin"
```
```
use sample_training
db.trips.findOne({ "start station location.type": "Point" })
db.companies.find({ "relationships.0.person.last_name": "Zuckerberg" },
{ "name": 1 }).pretty()
db.companies.find({ "relationships.0.person.first_name": "Mark",
"relationships.0.title": { "$regex": "CEO" } },
{ "name": 1 }).count()
db.companies.find({ "relationships.0.person.first_name": "Mark",
"relationships.0.title": {"$regex": "CEO" } },
{ "name": 1 }).pretty()
db.companies.find({ "relationships":
{ "$elemMatch": { "is_past": true,
"person.first_name": "Mark" } } },
{ "name": 1 }).pretty()
db.companies.find({ "relationships":
{ "$elemMatch": { "is_past": true,
"person.first_name": "Mark" } } },
{ "name": 1 }).count()
```
Sign in with Wallet
Connect another wallet