MongoDB

A NoSQL database that stores JSON objects.

Documents have dynamic schema, i.e. documents in the same collection do not need to have the same set of fields or structure, and common fields in a collection's documents may hold different types of data.

Collection

RDBMS MongoDB
Database Database
Table Collection
Tuple/Row Document
Column Field
Table Join Embedded Documents
Primary Key Default key _id provided by mongodb itself

There is a possibility of creating unique indices on fields that can enforce "primary key" constraint if necessary.

Sample MongoDB document:

{
  _id: ObjectId(7df78ad8902c)
  title: 'MongoDB Overview',
  description: 'MongoDB is no sql database',
  by: 'tutorials point',
  url: 'http://www.tutorialspoint.com',
  tags: ['mongodb', 'database', 'NoSQL'],
  likes: 100,
  comments: [
    {
      user:'user1',
      message: 'My first comment',
      dateCreated: new Date(2011,1,20,2,15),
      like: 0
    },
    {
      user:'user2',
      message: 'My second comments',
      dateCreated: new Date(2011,1,25,7,45),
      like: 5
    }
  ]
}

MongoDB commands

https://docs.mongodb.com/manual/

database commands

> show dbs
admin        0.000GB
classroomDB  0.000GB
companyDB    0.000GB
config       0.000GB
local        0.000GB
> use classroomDB
switched to db classroomDB
> db
classroomDB

create database, create collection, insert documents

> use colorsDB
switched to db colorsDB

> db.colors.insert({name:'red', primary:true})
WriteResult({ "nInserted" : 1 })

> db.colors.insert({name:'green', primary:true})
WriteResult({ "nInserted" : 1 })

> db.colors.insert({name:'blue', primary:true})
WriteResult({ "nInserted" : 1 })

> db.colors.insert({name:'purple', primary:false})
WriteResult({ "nInserted" : 1 })

> db.colors.insert({name:'orange', primary:false})
WriteResult({ "nInserted" : 1 })

> db.colors.insert({name:'yellow', primary:false})
WriteResult({ "nInserted" : 1 })

Note that there is no separate command to create a database; You simply "use" and first time the database is created.

Similarly for collections; the first time you insert into the collection, a new collection is created.

querying collections using "find"

> show collections
colors

> var cursor = db.colors.find()

> cursor.next()
{
    "_id" : ObjectId("604a9b77955d6894fe341698"),
    "name" : "red",
    "primary" : true
}
> cursor.next()
{
    "_id" : ObjectId("604a9b81955d6894fe341699"),
    "name" : "green",
    "primary" : true
}
> cursor.next()
{
    "_id" : ObjectId("604a9b89955d6894fe34169a"),
    "name" : "blue",
    "primary" : true
}

> cursor
{ "_id" : ObjectId("604a9b8f955d6894fe34169b"), "name" : "purple", "primary" : false }
{ "_id" : ObjectId("604a9b94955d6894fe34169c"), "name" : "orange", "primary" : false }
{ "_id" : ObjectId("604a9ba4955d6894fe34169d"), "name" : "yellow", "primary" : false }

iterate through a cursor with "forEach"

> db.colors.find().forEach(function(doc) {
    print(tojson(doc));
  });
{
    "_id" : ObjectId("604a9b77955d6894fe341698"),
    "name" : "red",
    "primary" : true
}
{
    "_id" : ObjectId("604a9b81955d6894fe341699"),
    "name" : "green",
    "primary" : true
}
{
    "_id" : ObjectId("604a9b89955d6894fe34169a"),
    "name" : "blue",
    "primary" : true
}
{
    "_id" : ObjectId("604a9b8f955d6894fe34169b"),
    "name" : "purple",
    "primary" : false
}
{
    "_id" : ObjectId("604a9b94955d6894fe34169c"),
    "name" : "orange",
    "primary" : false
}
{
    "_id" : ObjectId("604a9ba4955d6894fe34169d"),
    "name" : "yellow",
    "primary" : false
}
>

querying with "find" and filtering (where clause!)

> db.colors.find({name:'green'})
{ "_id" : ObjectId("604a9b81955d6894fe341699"), "name" : "green", "primary" : true }

> db.colors.find({primary:true}, {name:1})
{ "_id" : ObjectId("604a9b77955d6894fe341698"), "name" : "red" }
{ "_id" : ObjectId("604a9b81955d6894fe341699"), "name" : "green" }
{ "_id" : ObjectId("604a9b89955d6894fe34169a"), "name" : "blue" }

> db.colors.find({name:/l/})
{ "_id" : ObjectId("604a9b89955d6894fe34169a"), "name" : "blue", "primary" : true }
{ "_id" : ObjectId("604a9b8f955d6894fe34169b"), "name" : "purple", "primary" : false }
{ "_id" : ObjectId("604a9ba4955d6894fe34169d"), "name" : "yellow", "primary" : false }

sort, skip, limit

> db.colors.find({primary:true}).sort({name:1})
{ "_id" : ObjectId("604a9b89955d6894fe34169a"), "name" : "blue", "primary" : true }
{ "_id" : ObjectId("604a9b81955d6894fe341699"), "name" : "green", "primary" : true }
{ "_id" : ObjectId("604a9b77955d6894fe341698"), "name" : "red", "primary" : true }

> db.colors.find({primary:true}).sort({name:1}).limit(2)
{ "_id" : ObjectId("604a9b89955d6894fe34169a"), "name" : "blue", "primary" : true }
{ "_id" : ObjectId("604a9b81955d6894fe341699"), "name" : "green", "primary" : true }

> db.colors.find({primary:true}).sort({name:1}).skip(1).limit(1)
{ "_id" : ObjectId("604a9b81955d6894fe341699"), "name" : "green", "primary" : true }

more find/filter

> db.people.insert({name:'John', age:28})
WriteResult({ "nInserted" : 1 })
 
> db.people.insert({name:'Steve', age:29})
WriteResult({ "nInserted" : 1 })

> db.people.insert({name:'Steph', age:27})
WriteResult({ "nInserted" : 1 })

> db.people.find({age: {$gt: 27}})
{ "_id" : ObjectId("604aa34d955d6894fe34169e"), "name" : "John", "age" : 28 }
{ "_id" : ObjectId("604aa360955d6894fe34169f"), "name" : "Steve", "age" : 29 }

> db.people.find({age: {$lte: 27}})
{ "_id" : ObjectId("604aa36d955d6894fe3416a0"), "name" : "Steph", "age" : 27 }

comparisons

$gt
$gte
$lt
$lte
$ne
$in
$nin
$mod
$exists
> db.people.find({age: {$in: [27,28]}})
{ "_id" : ObjectId("604aa34d955d6894fe34169e"), "name" : "John", "age" : 28 }
{ "_id" : ObjectId("604aa36d955d6894fe3416a0"), "name" : "Steph", "age" : 27 }

> db.people.insert({name: "Tony", email:"tony@a.com"})
WriteResult({ "nInserted" : 1 })
> db.people.find({email: {$exists: true}})
{ "_id" : ObjectId("604aa9c4955d6894fe3416a1"), "name" : "Tony", "email" : "tony@a.com" }

> db.people.find({age: {$mod: [2,0]}})
{ "_id" : ObjectId("604aa34d955d6894fe34169e"), "name" : "John", "age" : 28 }

"or" queries

> db.people.find({$or: [{age: 28},{name: "Tony"}]})
{ "_id" : ObjectId("604aa34d955d6894fe34169e"), "name" : "John", "age" : 28 }
{ "_id" : ObjectId("604aa9c4955d6894fe3416a1"), "name" : "Tony", "email" : "tony@a.com" }

array queries

> db.papers.insert({title: "DB Systems", tags: ["data","system","join"]})
WriteResult({ "nInserted" : 1 })
> db.papers.insert({title: "Operating Systems", tags: ["process","system","file"]})
WriteResult({ "nInserted" : 1 })
> db.papers.insert({title: "Network Systems", tags: ["ip","system","tcp"]})
WriteResult({ "nInserted" : 1 })
> db.papers.insert({title: "Cybersecurity Systems", tags: ["ip","system","hack"]})
WriteResult({ "nInserted" : 1 })

> db.papers.find({tags: "ip"})
{ "_id" : ObjectId("604ace43955d6894fe3416a4"), "title" : "Network Systems", "tags" : [ "ip", "system", "tcp" ] }
{ "_id" : ObjectId("604ace77955d6894fe3416a5"), "title" : "Cybersecurity Systems", "tags" : [ "ip", "system", "hack" ] }

> db.papers.find({tags: {$all: ["ip","system"]}})
{ "_id" : ObjectId("604ace43955d6894fe3416a4"), "title" : "Network Systems", "tags" : [ "ip", "system", "tcp" ] }
{ "_id" : ObjectId("604ace77955d6894fe3416a5"), "title" : "Cybersecurity Systems", "tags" : [ "ip", "system", "hack" ] }

> db.papers.find({tags: ["ip","hack"]})

> db.papers.find({tags: ["ip"]})

> db.papers.find({tags: ["ip","system","hack"]})
{ "_id" : ObjectId("604ace77955d6894fe3416a5"), "title" : "Cybersecurity Systems", "tags" : [ "ip", "system", "hack" ] }

querying embedded documents

> db.people.insert({name: "Daniel", address: {street: "123 main", zip: 11111}})
WriteResult({ "nInserted" : 1 })
> db.people.insert({name: "Jonathan", address: {street: "123 main", zip: 11112}})
WriteResult({ "nInserted" : 1 })

> db.people.find({"address.zip": 11112})
{ "_id" : ObjectId("604ad09c955d6894fe3416a7"), "name" : "Jonathan", "address" : { "street" : "123 main", "zip" : 11112 } }

drop database

db.dropDatabase()

drops the current database.

Aggregation

project

similar to Relational Algebra "project"

> db.departments.aggregate({$project: {dnumber: 1, dname: 1}})
{ "_id" : ObjectId("604787996d7ef8d6ad0ad9c1"), "dname" : "Headquarters" }
{ "_id" : ObjectId("604787996d7ef8d6ad0ad9c2"), "dname" : "Administration" }
{ "_id" : ObjectId("604787996d7ef8d6ad0ad9c3"), "dname" : "Research" }
{ "_id" : ObjectId("604787996d7ef8d6ad0ad9c4"), "dname" : "Software" }
{ "_id" : ObjectId("604787996d7ef8d6ad0ad9c5"), "dname" : "Hardware" }
{ "_id" : ObjectId("604787996d7ef8d6ad0ad9c6"), "dname" : "Sales" }

match followed by project

> db.employees.aggregate([{$match: {salary: {$gt: 50000}}}, {$project: {lname: 1, salary: 1}}])
{ "_id" : ObjectId("604b915672eb837a5ef337b6"), "lname" : "Borg", "salary" : 55000 }
{ "_id" : ObjectId("604b915672eb837a5ef337bd"), "lname" : "James", "salary" : 85000 }
{ "_id" : ObjectId("604b915672eb837a5ef337be"), "lname" : "Freed", "salary" : 89000 }
{ "_id" : ObjectId("604b915672eb837a5ef337bf"), "lname" : "James", "salary" : 81000 }
{ "_id" : ObjectId("604b915672eb837a5ef337c3"), "lname" : "Wallis", "salary" : 92000 }
{ "_id" : ObjectId("604b915672eb837a5ef337c4"), "lname" : "Zell", "salary" : 56000 }
{ "_id" : ObjectId("604b915672eb837a5ef337c5"), "lname" : "Vile", "salary" : 53000 }
{ "_id" : ObjectId("604b915672eb837a5ef337c6"), "lname" : "Brand", "salary" : 62500 }
{ "_id" : ObjectId("604b915672eb837a5ef337c7"), "lname" : "Vos", "salary" : 61000 }
{ "_id" : ObjectId("604b915672eb837a5ef337c9"), "lname" : "Grace", "salary" : 79000 }
{ "_id" : ObjectId("604b915672eb837a5ef337cb"), "lname" : "Bays", "salary" : 70000 }
{ "_id" : ObjectId("604b915672eb837a5ef337cc"), "lname" : "Best", "salary" : 60000 }
{ "_id" : ObjectId("604b915672eb837a5ef337ce"), "lname" : "Ball", "salary" : 62000 }
{ "_id" : ObjectId("604b915672eb837a5ef337cf"), "lname" : "Bender", "salary" : 96000 }

group

> use companyDB
switched to db companyDB

> db.employees.aggregate({$group: {_id: "$worksFor", numEmps: {$sum: 1}}})
{ "_id" : 5, "numEmps" : 4 }
{ "_id" : 8, "numEmps" : 14 }
{ "_id" : 7, "numEmps" : 10 }
{ "_id" : 1, "numEmps" : 1 }
{ "_id" : 4, "numEmps" : 3 }
{ "_id" : 6, "numEmps" : 8 }
> 
> db.employees.aggregate({$group: {_id: "$worksFor", totalSalary: {$sum: "$salary"}}})
{ "_id" : 8, "totalSalary" : 571500 }
{ "_id" : 5, "totalSalary" : 133000 }
{ "_id" : 7, "totalSalary" : 634500 }
{ "_id" : 4, "totalSalary" : 93000 }
{ "_id" : 1, "totalSalary" : 55000 }
{ "_id" : 6, "totalSalary" : 480000 }

match, group

> db.employees.aggregate([{$match: {salary: {$gt: 50000}}} , {$group: {_id: "$worksFor", totalSalary: {$sum: "$salary"}}}])
{ "_id" : 7, "totalSalary" : 543500 }
{ "_id" : 8, "totalSalary" : 96000 }
{ "_id" : 6, "totalSalary" : 307000 }
{ "_id" : 1, "totalSalary" : 55000 }

> db.employees.aggregate([{$match: {salary: {$gt: 50000}}} , {$group: {_id: "$worksFor", totalSalary: {$avg: "$salary"}}}])
{ "_id" : 8, "totalSalary" : 96000 }
{ "_id" : 7, "totalSalary" : 67937.5 }
{ "_id" : 1, "totalSalary" : 55000 }
{ "_id" : 6, "totalSalary" : 76750 }

pymongo

pip install pymongo