MQL
MongoDB
- Data is stored in documents
- Documents are stored in Collections
- Document here refers to JSON
- Redundant copies of data are stored in replica set
- JSON is stored as BSON internally in MongoDB
# FOR BSON, use dump (backup) and restore (restore backup)
mongodump --uri "mongodb+srv://<your username>:<your password>@<your cluster>.mongodb.net/<database name>"
# drop will delete the stuff already in and create the new object from restore
mongorestore --uri "mongodb+srv://<your username>:<your password>@<your cluster>.mongodb.net/<database name>" --drop dump
# FOR JSON, use export (backup) and import (import backup)
# collection to specify which collection
# out to specify the file name to export to
mongoexport --uri="mongodb+srv://<your username>:<your password>@<your cluster>.mongodb.net/<database name>" --collection=sales --out=sales.json
mongoimport --uri="mongodb+srv://<your username>:<your password>@<your cluster>.mongodb.net/<database name>" --drop sales.json
# to look at all databases available
show dbs
# to connect to a database
use sample_training
# to look at collections inside a database
show collections
Queries
Find
db.zips.find({"state": "NY"})
# Use 'it' : iterates through a cursor
# cursor : A pointer to a result set of query
# pointer : A direct address of memory location
db.zips.find({"state": "NY"}).count()
db.zips.find({"state": "NY", "city": "ALBANY"})
db.zips.find({"state": "NY", "city": "ALBANY"}).pretty()
# get random one
db.inspections.findOne()
- Each Document has a unique object
_idwhich is set by default if not specfied
Insert
db.inspections.insert({
"_id" : ObjectId("56d61033a378eccde8a8354f"),
"id" : "10021-2015-ENFO",
"certificate_number" : 9278806,
"business_name" : "ATLIXCO DELI GROCERY INC.",
"date" : "Feb 20 2015",
"result" : "No Violation Issued",
"sector" : "Cigarette Retail Dealer - 127",
"address" : {
"city" : "RIDGEWOOD",
"zip" : 11385,
"street" : "MENAHAN ST",
"number" : 1712
}
})
db.inspections.insert({
"id" : "10021-2015-ENFO",
"certificate_number" : 9278806,
"business_name" : "ATLIXCO DELI GROCERY INC.",
"date" : "Feb 20 2015",
"result" : "No Violation Issued",
"sector" : "Cigarette Retail Dealer - 127",
"address" : {
"city" : "RIDGEWOOD",
"zip" : 11385,
"street" : "MENAHAN ST",
"number" : 1712
}
})
db.inspections.find(
{"id" : "10021-2015-ENFO", "certificate_number" : 9278806}
).pretty()
Insert conflicts
# Insert three test documents
db.inspections.insert([ { "test": 1 }, { "test": 2 }, { "test": 3 } ])
# Insert three test documents but specify the _id values
# Error in 2 docs
# Insert operation halts when an error is in-countered
db.inspections.insert([{ "_id": 1, "test": 1 },{ "_id": 1, "test": 2 },
{ "_id": 3, "test": 3 }])
# Find the documents with _id: 1
db.inspections.find({ "_id": 1 })
# Insert multiple documents specifying the _id values,
# and using the "ordered": false option
# Ordered False will allow to insert all docs where id doesnt match,
# and give errors for those which failed
db.inspections.insert([{ "_id": 1, "test": 1 },{ "_id": 1, "test": 2 },
{ "_id": 3, "test": 3 }],{ "ordered": false })
# Insert multiple documents with _id: 1 with the default "ordered": true setting
db.inspection.insert([{ "_id": 1, "test": 1 },{ "_id": 3, "test": 3 }])
Updates
https://docs.mongodb.com/manual/reference/operator/update/#id1
# Find all documents in the zips collection
# where the zip field is equal to "12434".
db.zips.find({ "zip": "12534" }).pretty()
# Find all documents in the zips collection
# where the city field is equal to "HUDSON".
db.zips.find({ "city": "HUDSON" }).pretty()
# Update all documents in the zips collection
# where the city field is equal to "HUDSON"
# by adding 10 to the current value of the "pop" field.
# Increment Operation
db.zips.updateMany({ "city": "HUDSON" }, { "$inc": { "pop": 10 } })
# Update a single document in the zips
# collection where the zip field is
# equal to "12534" by setting the value
# of the "pop" field to 17630.
# Update / Set operation
db.zips.updateOne({ "zip": "12534" }, { "$set": { "pop": 17630 } })
# Update a single document in the zips
# collection where the zip field is equal
# to "12534" by setting the value of
# the "popupation" field to 17630.
# Update / Set operation
db.zips.updateOne({ "zip": "12534" }, { "$set": { "population": 17630 } })
# Find all documents in the grades
# collection where the student_id
# field is 151 , and the class_id field is 339.
db.grades.find({ "student_id": 151, "class_id": 339 }).pretty()
# Update one document in the grades
# collection where the student_id is
# `250` *, and the class_id field is 339,
# by adding a document element to the "scores" array.
db.grades.updateOne({ "student_id": 250, "class_id": 339 },
{ "$push": { "scores": { "type": "extra credit",
"score": 100 }
}
})
db.grades.find({ "student_id": 250, "class_id": 339 })
Upsert
db.iot.updateOne({ "sensor": r.sensor, "date": r.date,
"valcount": { "$lt": 48 } },
{ "$push": { "readings": { "v": r.value, "t": r.time } },
"$inc": { "valcount": 1, "total": r.value } },
{ "upsert": true })
Delete
# Look at all the docs that have test field equal to 1.
db.inspections.find({ "test": 1 }).pretty()
# Delete all the documents that have test field equal to 1.
db.inspections.deleteMany({ "test": 1 })
# Delete one document that has test field equal to 3.
db.inspections.deleteOne({ "test": 3 })
# Inspect what is left of the inspection collection.
db.inspection.find().pretty()
# View what collections are present in the sample_training collection.
show collections
# Drop the inspection collection.
db.inspection.drop()
Operators
# Find all documents where the tripduration
# was less than or equal to 70 seconds and
# the usertype was not Subscriber:
# LESS THAN EQUAL
# NOT EQUAL
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:
# LESS THAN EQUAL
# EQUAL
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:
# LESS THAN EQUAL
db.trips.find({ "tripduration": { "$lte" : 70 },
"usertype": "Customer" }).pretty()
# Find all documents where airplanes CR2 or A81
# left or landed in the KZN airport:
# AND, OR operator
db.routes.find({ "$and": [ { "$or" :[ { "dst_airport": "KZN" },
{ "src_airport": "KZN" }
] },
{ "$or" :[ { "airplane": "CR2" },
{ "airplane": "A81" } ] }
]}).pretty()
- AND operator is present in your qureies when not specified
EXPR
# Find all documents where the trip started
# and ended at the same station:
# here $ denotes the value of the field specified
db.trips.find(
{ "$expr": { "$eq": [ "$end station id", "$start station id"] }
}).count()
# replacing id with name
db.trips.find(
{ "$expr": { "$eq": [ "$end station name", "$start station name"]}
}).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
# using ALL operator
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()
Array operators and Projection
# Find all documents with exactly 20 amenities which include
# all the amenities listed in the query array,
# and display their price and address:
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()
# 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()
# nested projection
db.listingsAndReviews.find({ "amenities": "Wifi" }, { "price": 1, "address": { "country" : 1 }, "_id": 0 }).pretty()
# Switch to this database:
use sample_training
# Get one document from the collection:
db.grades.findOne()
# Elematch Example
# 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()
Elematch: matches documents that contains an array field with at least one element that matches the specified query creteriaElematch: Projects only the array elements with at least one element that matches the specified criteria
Array Operators and Sub-Documents
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()
Sort and Limit
- Use sort before limit always