MongoDB Skills Essential Exercise
- By : Mydatahack
- Category : DBA, Infrastructure
- Tags: Basic Skills, Exercise, MongoDB
MongoDB is probably the most prevalent NoSQL database choice for the backend of web applications. It is an open source and relatively easy to use. It works well with the modern web applications written in Node.js or Python Django. Enterprise-level web application platforms like Sitecore (works on .NET) also use MongoDB as the backend database.
Working with MongoDB is quite different from relational databases as its query language is completely different from SQL. Good news is that it is not as complicated as it seems. It does the same CURD operation, flat file upload functions, conditional queries and aggregation functions as the relational world. With MongoDB, you don’t need to worry about joining (because it does no join!). If you spend a few hours, you will become pretty good at it.
I created a series of exercises that cover the basic operations that you can execute with the Mongo Shell. It will give you the solid foundation to develop applications on MongoDB.
EXERCISE
(1) Create Database
1-1. Create a database called usermanaged, drop it and create it again. Check which database you are currently in.
(2) Create a Collection & Insert a Record
1-2. Create a collection called customers in usermanaged created in Exercise 1 and insert the document below. Check if the document is inserted correctly.
"lastName":"West",
"email":"john.west@mail.com",
"phone":"032345432134",
"BusinessType": ["Sell", "Sugar", "Drinks"],
"Reference":100,
"Company":"Coca-Cola"}
(3) Bulk Load JSON File
3-1. Create a collection called transactions in usermanaged (drop if it already exists) and bulk load the data from a json file, transactions.json (see the data at the end of the questions).
3-2. Append the records with the same file, transactions.json
3-3. Upsert the record from the new file called transactions_upsert.json (see the data at the end of the questions.
(4) Bulk Load CSV File
4-1. Create a collection and load data from a CSV file will multiple rows. Define the keys from the header row.
(5) Query MongoDB with Conditions
This question uses the collection (transactions) created in Exercise 3.
5-1. Find any record where Name is Tom
5-2. Find any record where total payment amount (Payment.Total) is 400.
5-3. Find any record where price (Transaction.price) is greater than 400.
5-4. Find any record where Note is null or the key itself is missing.
5-5. Find any record where Note exists and its value is null.
5-6. Find any record where the Note key does not exist.
(6) Aggregation with MongoDB
This question uses the collection (transactions) created in Exercise 3.
6-1. Calculate the total transaction amount by adding up Payment.Total in all records.
6-2. Get the total price per record by adding up the price values in the Transaction array (Transaction.price).
6-3. Calculate total payments (Payment.Total) for each payment type (Payment.Type).
6-4. Find the max Id.
6-5. Find the max price (Transaction.price).
(7) CRUD Operations
This question uses the collection (transactions) created in Exercise 3. CRUD: Create, Read, Update and Delete.
7-1. Insert a record below.
7-2. Updating the new inserted record above. Make Name=’Updated Record’ & Note=’Updated!’
7-3. Delete the record inserted above by using Id.
(8) User Creation
8-1. Create a read only user who can query records from collections from all databases.
8-2. Create a writer user who can create collections and do CRUD operations in any collections.
8-3. Create a usermanaged user who can do the writer operation in the usermanaged database and read only for the rest of the databases.
JSON Data
Here is the transaction JSON for insert in Exercise 3-1.
{
"Id": 100,
"Name": "John",
"TransactionId": "tran1",
"Transaction": [
{
"ItemId":"a100",
"price": 200
},
{
"ItemId":"a110",
"price": 200
}
],
"Subscriber": true,
"Payment": {
"Type": "Credit-Card",
"Total": 400,
"Success": true
},
"Note": "1st Complete Record"
},
{
"Id": 101,
"Name": "Tom",
"TransactionId": "tran2",
"Transaction": [
{
"ItemId":"a100",
"price": 200
},
{
"ItemId":"a110",
"price": 200
}
],
"Subscriber": true,
"Payment": {
"Type": "Debit-Card",
"Total": 400,
"Success": true
},
"Note":null
},
{
"Id": 102,
"Name": "Margaret",
"TransactionId": "tran3",
"Transaction": [
{
"ItemId":"a100",
"price": 200
},
{
"ItemId":"a110",
"price": 200
}
],
"Subscriber": true,
"Payment": {
"Type": "Credit-Card",
"Total": 400,
"Success": true
}
},
{
"Id": 103,
"Name": "Dylan",
"TransactionId": "tran4",
"Transaction": [
{
"ItemId":"a100",
"price": 200
},
{
"ItemId":"a110",
"price": 200
}
],
"Subscriber": true,
"Payment": null,
"Note": "Payment is Null"
}
]
Here is the JSON file for upsert in Exercise 3-3.
{
"Id": 100,
"Name": "ZZZZZZ",
"TransactionId": "tran1",
"Transaction": [
{
"ItemId":"a100",
"price": 2000000000
},
{
"ItemId":"a110",
"price": 200
}
],
"Subscriber": true,
"Payment": {
"Type": "Credit-Card",
"Total": 400,
"Success": true
},
"Note": "1st Complete Record"
},
{
"Id": 101,
"Name": "Tom",
"TransactionId": "tran2",
"Transaction": [
{
"ItemId":"a100",
"price": 200
},
{
"ItemId":"a110",
"price": 200
}
],
"Subscriber": true,
"Payment": {
"Type": "Debit-Card",
"Total": 400,
"Success": true
},
"Note":null
},
{
"Id": 102,
"Name": "Margaret",
"TransactionId": "tran3",
"Transaction": [
{
"ItemId":"a100",
"price": 200
},
{
"ItemId":"a110",
"price": 200
}
],
"Subscriber": true,
"Payment": {
"Type": "Credit-Card",
"Total": 400,
"Success": true
}
},
{
"Id": 103,
"Name": "Dylan",
"TransactionId": "tran4",
"Transaction": [
{
"ItemId":"a100",
"price": 200
},
{
"ItemId":"a110",
"price": 200
}
],
"Subscriber": true,
"Payment": null,
"Note": "Payment is Null"
},
{
"Id": 104,
"Name": "Oliver",
"TransactionId": "tran5",
"Transaction": [
{
"ItemId":"a100",
"price": 200
},
{
"ItemId":"a110",
"price": 200
}
],
"Subscriber": true,
"Payment": {
"Total": 400,
"Success": true
},
"Note": "Payment Type is missing"
},
{
"Id": 105,
"Name": "Sarah",
"TransactionId": "tran6",
"Transaction": [
{
"ItemId":"a100",
"price": 200
},
{
"ItemId":"a110",
"price": 200
}
],
"Subscriber": true,
"Note": "Payment is missing"
}
]
ANSWERS
(1) Create Database
Use db_name will create a database if it doesn’t exist. You can drop the database with dropDatabase().
# to drop database
db.dropDatabase()
(2) Create a Collection & Insert a Record
It really doesn’t matter if you put double quote or not. Either format works.
db.customers.insert(
{ "firstName":"John",
"lastName":"West",
"email":"john.west@mail.com",
"phone":"032345432134",
"BusinessType": ["Sell", "Sugar", "Drinks"],
"Reference":100,
"Company":"Coca-Cola"})
# This works, too.
db.customers.insert(
{ firstName:'John',
lastName:'West',
email:'john.west@mail.com',
phone:'032345432134',
BusinessType: ['Sell', 'Sugar', 'Drinks'],
Reference:100,
Company:'Coca-Cola'})
# You can drop it like this
db.customers.drop()
(3) Bulk Load JSON File
You can import data from json, csv or tsv files with mongoimport and export data with mongoexport. Note that these are not for backups as you loose some data type information by writing it to flat files.
You need to quit the Mongo Shell session and use Terminal.
For multiple records, you need jsonArray option. Otherwise you get the error message below.
Failed: error unmarshaling bytes on document #0: JSON decoder out of sync – data changing underfoot?
mongoimport --db usermanaged --jsonArray --collection transactions --drop --file C:\tmp\transaction.json
# 2: Append to the exisiting collection
mongoimport --db usermanaged --jsonArray --collection transactions --mode insert --file C:\tmp\transaction.json
# 3: Upsert from the file
mongoimport --db usermanaged --jsonArray --collection transactions --mode upsert --upsertFields Id --file C:\tmp\transaction_upsert.json
Once imported, you can log into the database and check the data.
db.transactions.count()
db.transactions.find().pretty()
(4) Bulk Load CSV File
Keys will be created from the header row. –headerline and –type csv are required. You can use whatever csv available.
(5) Query MongoDB with Conditions
db.transactions.find({Name: 'Tom'})
# 2. Find a record in transactions where total payment amount is 400.
# When chaining key with dot, you need doublequote.
db.transactions.find({"Payment.Total": 400 })
db.transactions.find({"Payment.Total": {$eq: 400}})
# 3. Find a record in transactions collection where price is greater than 400
db.transactions.find({"Transaction.price": {$gt: 400} })
# can do equal or greater than, too.
db.transactions.find({"Transaction.price": {$gte: 400} })
# 4. Find a record in transacaions collection where note is null or missing
db.transactions.find({"Note": null})
# 5. Find a record where only Note key is missing
db.transactions.find({Note: {$exists: false } })
#6. Null only
db.transactions.find({Note: {$type: 10 } })
(6) Aggregation with MongoDB
db.transactions.aggregate({
$group: {
_id: '',
TotalRevenue: { $sum: '$Payment.Total' }
}
})
# 2. Aggregate per record by aggregating Transaction.price
db.transactions.aggregate([
{
$project: {
revenueTotal: { $sum: "$Transaction.price"},
}
}
])
# 3. Aggregate per payment type by adding up Payment.Total
db.transactions.aggregate([
{
$group:
{
_id: "$Payment.Type",
totalAmount: { $sum: "$Payment.Total" },
count: { $sum: 1 }
}
}
])
# 4. Find the max id
db.transactions.aggregate([
{
$group:
{
_id: '',
maxId: {$max: "$Id"}
}
}
])
# 5. Find the max Transaction.price
# Transaction.price is an array. So find the array containing maximum price and then extract
# the max price from the array with another $max.
db.transactions.aggregate([
{
$group:
{
_id: '',
maxPrice: {$max: {$max: "$Transaction.price"}}
}
}
])
(7) CRUD Operations
db.transactions.insert(
{
"Id": 110,
"Name": "Inserted Record",
"TransactionId": "tranNew1",
"Transaction": [
{
"ItemId":"c324",
"price": 456
},
{
"ItemId":"d456",
"price": 543
}
],
"Subscriber": false,
"Payment": {
"Type": "Debit-Card",
"Total": 999,
"Success": true
},
"Note":'Hello World'
})
# check the record
db.transactions.find({Id:110})
# 2. Updating the newly inserted record above.
db.transactions.update({Id:110},{$set:{Name:'Updated Record',Note:'Updated!'}})
# 3. Deleting record
db.transaction.remove({Id:110})
(8) User Creation
See User Management with MongoDB.
REFERENCE
