My Notes on Mongo DB

07/14/2020

I need document DB for a project, and Mongo DB fits the bill perfectly. The only problem is that I do not remember it anymore. I need a refresher. What better way than noting it down on my blog while I get back to it? It will help me if I need the refresher again, and in turn may help someone else requiring it too.

The first thing is to download the latest version of community edition from here. I use a Mac, so I downloaded a 64 bit tgz file and extracted it to a directory. Just for convenience, I created a data and a log directory where I can easily find it. Starting up is as easy as running the following command (start up mongo daemon).

% ./bin/mongod --directoryperdb --dbpath ./<mydbpath> --logpath ./<mylogs> &

This is the no install version of startup; you can of course add this as a service to startup during boot. After startup, we can go to console and test the version of MongoDB.

% mongo --host 127.0.0.1:27017
> version()
4.2.6
>

New Database and Admin User creation 

# What are available DBs
> show dbs
admin   0.000GB
config  0.000GB
local   0.000GB
# Switch user to Admin
> use admin
switched to db admin
# Create a user with all DB access
> db.createUser({user: "suser", pwd: "Password", roles: [ "userAdminAnyDatabase" ]})
Successfully added user: { "user" : "suser", "roles" : [ "userAdminAnyDatabase" ] }

Mongo DB community edition uses SCRAM (Salted Challenge Response Authentication) as the default mechanism for password authentication. Specification document for SCRAM-SHA-256 is available here. This is visible when you get details for the user.

> show users
{
	"_id" : "admin.suser",
	"userId" : UUID("c6dfda95-ea2f-1cd0-8bcb-edb43a581195"),
	"user" : "suser",
	"db" : "admin",
	"roles" : [
		{
			"role" : "userAdminAnyDatabase",
			"db" : "admin"
		}
	],
	"mechanisms" : [
		"SCRAM-SHA-1",
		"SCRAM-SHA-256"
	]
}

Create Database and Users

You do not create database explicitly in MongoDB. Switching to a new database will create that database on first operation. If you see the code below, we have switched to books database. However, this database is not created yet.

> use books
switched to db books
> db
books

// --> Show all DBs
> show dbs
admin     0.000GB
config    0.000GB
local     0.000GB

// --> Create new User
> db.createUser({user:"suturf", pwd:"suturf", roles:["readWrite", "dbAdmin"]});
Successfully added user: {"user":"suturf", "roles":["readWrite", "dbAdmin"]}

Next step is to create a collection. We will create a collection on books; because books are awesome. We will build the following normalized format.

Insert Data

publisher
    _id
    imprint_name
    publishing_house
    founded_year
    revenue

book
    _id
    name
    authors
    format
    price
    publish_date
    isbn
    publisher

Let’s insert the first publisher and book.

> pub = {
... "_id": "shambala",
... "imprint_name": "Shambala Publications, Inc",
... "publishing_house": "Penguin Random House",
... "founded_year": "2013",
... "revenue": "$3.3 billion"
... }
{
	"_id" : "shambala",
	"imprint_name" : "Shambala Publications, Inc",
	"publishing_house" : "Penguin Random House",
	"founded_year" : "2013",
	"revenue" : "$3.3 billion"
}
> show collections
> db.publishers.save(pub)
WriteResult({ "nMatched" : 0, "nUpserted" : 1, "nModified" : 0, "_id" : "shambala" })
> db.publishers.find().pretty()
{
	"_id" : "shambala",
	"imprint_name" : "Shambala Publications, Inc",
	"publishing_house" : "Penguin Random House",
	"founded_year" : "2013",
	"revenue" : "$3.3 billion"
}
> bok = {
... "name": "Finding the Still Point",
... "name2": "A BEGINNER'S GUIDE TO ZEN MEDITATION",
... "authors": [ "John Daido Loori" ],
... "format": "paperback",
... "price": 15.95,
... "publish_date": ISODate("2020-07-07"),
... "isbn": "9781611808452",
... "publisher": "shambala"
... }
{
	"name" : "Finding the Still Point",
	"name2" : "A BEGINNER'S GUIDE TO ZEN MEDITATION",
	"authors" : [
		"John Daido Loori"
	],
	"format" : "paperback",
	"price" : 15.95,
	"publish_date" : ISODate("2020-07-07T00:00:00Z"),
	"isbn" : "9781611808452",
	"publisher" : "shambala"
}
> db.books.save(bok)
WriteResult({ "nInserted" : 1 })
> db.books.find().pretty()
{
	"_id" : ObjectId("5f0cfba80bd1610291a9aa9d"),
	"name" : "Finding the Still Point",
	"name2" : "A BEGINNER'S GUIDE TO ZEN MEDITATION",
	"authors" : [
		"John Daido Loori"
	],
	"format" : "paperback",
	"price" : 15.95,
	"publish_date" : ISODate("2020-07-07T00:00:00Z"),
	"isbn" : "9781611808452",
	"publisher" : "shambala"
}
>

We have not provided an _id column for books, so Mongo created an _id for us. We will just add few more books to this so that we can do some more operations. Other way of inserting data will be to use: db.books.insert(<json>).

Fetching Data

We have already seen a simple way of getting data. Mongo function pretty() can be used to print formatted data. Let’s fetch all data from books collection.

> db.books.find().pretty()
{
	"_id" : ObjectId("5f0cfba80bd1610291a9aa9d"),
	"name" : "Finding the Still Point",
	"name2" : "A BEGINNER'S GUIDE TO ZEN MEDITATION",
	"authors" : [
		"John Daido Loori"
	],
	"format" : "paperback",
	"price" : 15.95,
	"publish_date" : ISODate("2020-07-07T00:00:00Z"),
	"isbn" : "9781611808452",
	"publisher" : "shambala"
}
{
	"_id" : ObjectId("5f0cff8a0bd1610291a9aa9e"),
	"name" : "Hideaway",
	"authors" : [
		"Nora Roberts"
	],
	"format" : "hardcover",
	"price" : 28.99,
	"publish_date" : ISODate("2020-02-26T00:00:00Z"),
	"isbn" : "9781250207104",
	"publisher" : "martins"
}
{
	"_id" : ObjectId("5f0cffe50bd1610291a9aa9f"),
	"name" : "Hello Summer",
	"authors" : [
		"Mary Kay Andrews"
	],
	"format" : "hardcover",
	"price" : 28.99,
	"publish_date" : ISODate("2020-05-05T00:00:00Z"),
	"isbn" : "9781250256928",
	"publisher" : "martins"
}
{
	"_id" : ObjectId("5f0d00780bd1610291a9aaa0"),
	"name" : "Outsider",
	"authors" : [
		"Linda Castillo"
	],
	"format" : "hardcover",
	"price" : 27.99,
	"publish_date" : ISODate("2020-07-07T00:00:00Z"),
	"isbn" : "9781250142894",
	"publisher" : "minotaur"
}
{
	"_id" : ObjectId("5f0d087cbdf5d40dfd96e1e1"),
	"name" : "The Golem and the Jinni",
	"authors" : [
		"Helen Wecker"
	],
	"format" : "paperback",
	"price" : 10,
	"publish_date" : ISODate("2020-06-10T00:00:00Z"),
	"isbn" : "9780063036574",
	"publisher" : "harp_perennial"
}
{
	"_id" : ObjectId("5f0d08bebdf5d40dfd96e1e2"),
	"name" : "Blindsighted",
	"authors" : [
		"Karin Slaughter"
	],
	"format" : "paperback",
	"price" : 9.99,
	"publish_date" : ISODate("2020-06-30T00:00:00Z"),
	"isbn" : "9780062385383",
	"publisher" : "harp_marrow"
}
>
###############################
<<<< Find Book Named 'Hideaway'
###############################
> db.books.find({"name": "Hideaway"}).pretty()
{
	"_id" : ObjectId("5f0cff8a0bd1610291a9aa9e"),
	"name" : "Hideaway",
	"authors" : [
		"Nora Roberts"
	],
	"format" : "hardcover",
	"price" : 28.99,
	"publish_date" : ISODate("2020-02-26T00:00:00Z"),
	"isbn" : "9781250207104",
	"publisher" : "martins"
}

######################################
<<<< Paperbacks less than equal to $10
######################################
> db.books.find({$and: [{"format": "paperback"}, {"price": {$lte: 10}}]}).pretty()
{
	"_id" : ObjectId("5f0d087cbdf5d40dfd96e1e1"),
	"name" : "The Golem and the Jinni",
	"authors" : [
		"Helen Wecker"
	],
	"format" : "paperback",
	"price" : 10,
	"publish_date" : ISODate("2020-06-10T00:00:00Z"),
	"isbn" : "9780063036574",
	"publisher" : "harp_perennial"
}
{
	"_id" : ObjectId("5f0d08bebdf5d40dfd96e1e2"),
	"name" : "Blindsighted",
	"authors" : [
		"Karin Slaughter"
	],
	"format" : "paperback",
	"price" : 9.99,
	"publish_date" : ISODate("2020-06-30T00:00:00Z"),
	"isbn" : "9780062385383",
	"publisher" : "harp_marrow"
}
>

Next up, we will try some aggregate operators. All aggregate operators for Mongo are defined on this page.

Aggregation

##################################################
<<<< Sort by publish_date and limit 1 (oldest book)
##################################################
> db.books.aggregate([ { $sort: {"publish_date": 1}}, {$limit: 1} ]).pretty()

{
	"_id" : ObjectId("5f0cff8a0bd1610291a9aa9e"),
	"name" : "Hideaway",
	"authors" : [
		"Nora Roberts"
	],
	"format" : "hardcover",
	"price" : 28.99,
	"publish_date" : ISODate("2020-02-26T00:00:00Z"),
	"isbn" : "9781250207104",
	"publisher" : "martins"
}
>

########################
<<<< What date was this?
########################
> db.books.aggregate([{ $group: {_id: null, Date: {$min: "$publish_date"}} }])
{ "_id" : null, "Date" : ISODate("2020-02-26T00:00:00Z") }
>

##############################################
<<<< Get count of publishers founded each year
##############################################
> db.publishers.aggregate([{$group: {_id: "$founded_year", count: { $sum: 1 }}}, {$sort: { "count": 1, "founded_year": 1 }} ]).pretty()
{ "_id" : "2013", "count" : 1 }
{ "_id" : "2015", "count" : 2 }
{ "_id" : "2018", "count" : 2 }
>

#############################
<<<< Break up ISBN into parts
#############################
> db.books.aggregate([{ $project: {name: 1, price: 1, publish_date: 1, isbn: { prefix: { $substr: [ "$isbn", 0, 3 ] }, group: { $substr: [ "$isbn", 3, 2 ] }, publisher: { $substr: [ "$isbn", 5, 4 ] }, title: { $substr: [ "$isbn", 9, 3 ] }, checkDigit: { $substr: [ "$isbn", 12, 1] }}} }]).pretty()
{
	"_id" : ObjectId("5f0cfba80bd1610291a9aa9d"),
	"name" : "Finding the Still Point",
	"price" : 15.95,
	"publish_date" : ISODate("2020-07-07T00:00:00Z"),
	"isbn" : {
		"prefix" : "978",
		"group" : "16",
		"publisher" : "1180",
		"title" : "845",
		"checkDigit" : "2"
	}
}
{
	"_id" : ObjectId("5f0cff8a0bd1610291a9aa9e"),
	"name" : "Hideaway",
	"price" : 28.99,
	"publish_date" : ISODate("2020-02-26T00:00:00Z"),
	"isbn" : {
		"prefix" : "978",
		"group" : "12",
		"publisher" : "5020",
		"title" : "710",
		"checkDigit" : "4"
	}
}
{
	"_id" : ObjectId("5f0cffe50bd1610291a9aa9f"),
	"name" : "Hello Summer",
	"price" : 28.99,
	"publish_date" : ISODate("2020-05-05T00:00:00Z"),
	"isbn" : {
		"prefix" : "978",
		"group" : "12",
		"publisher" : "5025",
		"title" : "692",
		"checkDigit" : "8"
	}
}
{
	"_id" : ObjectId("5f0d00780bd1610291a9aaa0"),
	"name" : "Outsider",
	"price" : 27.99,
	"publish_date" : ISODate("2020-07-07T00:00:00Z"),
	"isbn" : {
		"prefix" : "978",
		"group" : "12",
		"publisher" : "5014",
		"title" : "289",
		"checkDigit" : "4"
	}
}
{
	"_id" : ObjectId("5f0d087cbdf5d40dfd96e1e1"),
	"name" : "The Golem and the Jinni",
	"price" : 10,
	"publish_date" : ISODate("2020-06-10T00:00:00Z"),
	"isbn" : {
		"prefix" : "978",
		"group" : "00",
		"publisher" : "6303",
		"title" : "657",
		"checkDigit" : "4"
	}
}
{
	"_id" : ObjectId("5f0d08bebdf5d40dfd96e1e2"),
	"name" : "Blindsighted",
	"price" : 9.99,
	"publish_date" : ISODate("2020-06-30T00:00:00Z"),
	"isbn" : {
		"prefix" : "978",
		"group" : "00",
		"publisher" : "6238",
		"title" : "538",
		"checkDigit" : "3"
	}
}
>

Updates

We have looked at inserting and fetching data from mongo tables. Now we will look at some update commands. These are not comprehensive, we will only cover some basic commands. I will add one more record for this test to the books table.

> bok = {
... "name": "C++ Fundamentals",
... "authors": [ "Antonio Mallia", "Francesco Zoffoli" ],
... "format": "paperback",
... "price": 29.99,
... "publish_date": ISODate("2019-03-15"),
... "isbn": "9781789801491",
... "publisher": "packt",
... "genre": [ "computer", "c++", "IT" ]
... }
{
	"name" : "C++ Fundamentals",
	"authors" : [
		"Antonio Mallia",
		"Francesco Zoffoli"
	],
	"format" : "paperback",
	"price" : 29.99,
	"publish_date" : ISODate("2019-03-15T00:00:00Z"),
	"isbn" : "9781789801491",
	"publisher" : "packt",
	"genre" : [
		"computer",
		"c++",
		"IT"
	]
}
> db.books.save(bok)
WriteResult({ "nInserted" : 1 })
>
##################################
<<<< Change publisher to packt_pub
##################################
> db.books.update({"name": "C++ Fundamentals"}, {$set: {"publisher": "packt_pub"}}, {multi: true, upsert: true})
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
> db.books.find({"name": "C++ Fundamentals"}).pretty()
{
	"_id" : ObjectId("5f0e1b18a04927c7da202300"),
	"name" : "C++ Fundamentals",
	"authors" : [
		"Antonio Mallia",
		"Francesco Zoffoli"
	],
	"format" : "paperback",
	"price" : 29.99,
	"publish_date" : ISODate("2019-03-15T00:00:00Z"),
	"isbn" : "9781789801491",
	"publisher" : "packt_pub",
	"genre" : [
		"computer",
		"c++",
		"IT"
	]
}
>

###################################
<<<< Change IT in genre to InfoTech
###################################
> db.books.update({"name": "C++ Fundamentals", "genre": "IT"}, {$set: {"genre.$": "InfoTech"}})
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
> db.books.find({"name": "C++ Fundamentals"}).pretty()
{
	"_id" : ObjectId("5f0e1b18a04927c7da202300"),
	"name" : "C++ Fundamentals",
	"authors" : [
		"Antonio Mallia",
		"Francesco Zoffoli"
	],
	"format" : "paperback",
	"price" : 29.99,
	"publish_date" : ISODate("2019-03-15T00:00:00Z"),
	"isbn" : "9781789801491",
	"publisher" : "packt_pub",
	"genre" : [
		"computer",
		"c++",
		"InfoTech"
	]
}
>

###############################
<<<< Remove InfoTech from genre
###############################
> db.books.update({"name": "C++ Fundamentals"}, {$pull: {"genre": "InfoTech"}})
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
> db.books.find({"name": "C++ Fundamentals"}).pretty()
{
	"_id" : ObjectId("5f0e1b18a04927c7da202300"),
	"name" : "C++ Fundamentals",
	"authors" : [
		"Antonio Mallia",
		"Francesco Zoffoli"
	],
	"format" : "paperback",
	"price" : 29.99,
	"publish_date" : ISODate("2019-03-15T00:00:00Z"),
	"isbn" : "9781789801491",
	"publisher" : "packt_pub",
	"genre" : [
		"computer",
		"c++"
	]
}
>
####################
<<<< Add IT to genre
####################
> db.books.update({"name": "C++ Fundamentals"}, {$push: {"genre": "IT"}})
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
> db.books.find({"name": "C++ Fundamentals"}).pretty()
{
	"_id" : ObjectId("5f0e1b18a04927c7da202300"),
	"name" : "C++ Fundamentals",
	"authors" : [
		"Antonio Mallia",
		"Francesco Zoffoli"
	],
	"format" : "paperback",
	"price" : 29.99,
	"publish_date" : ISODate("2019-03-15T00:00:00Z"),
	"isbn" : "9781789801491",
	"publisher" : "packt_pub",
	"genre" : [
		"computer",
		"c++",
		"IT"
	]
}
>
#######################
<<<< Remove this Record
#######################
> db.books.remove({"name": "C++ Fundamentals"})
WriteResult({ "nRemoved" : 1 })
> db.books.find({"name": "C++ Fundamentals"})
>

Final Notes

I just added the key commands here for reference. There is a lot more to mongo than what is here. I also used mongo from Java for my project. But I will leave that for a later discussion. Ciao for now.