MongoDB Course

00. Intro

Requirements

Basic knowledge in subjects:

1. Relational databases
2. SQL
3. JavaScript
4. JSON data format

To perform the exercises:

1. Install MongoDB Community Server
2. Install Robo 3T client
3. Download and import example companies dataset

References

Presentation: https://lsolilo.github.io/slides/mongodb-00-intro.html

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

01. Basic Queries

Accessing all data in collection

Retrieve all companies:

db.getCollection('companies').find({})
//or
db.companies.find({})

SQL equivalent:

SELECT * FROM companies

Simple filtering by a field

Find company/companies with phone number 206.219.0537:

db.getCollection('companies').find({phone_number:'206.219.0537'})
//or
db.getCollection('companies').find({'phone_number':'206.219.0537'})

SQL equivalent:

SELECT * FROM companies WHERE phone_number = '206.219.0537'

Retrieving specified fields from collection

Find companies with phone number 206.219.0537, but return only company name and phone number:

db.getCollection('companies').find({phone_number:'206.219.0537'}, {name:1, phone_number:1})
//and to hide the document identifier:
db.getCollection('companies').find({phone_number:'206.219.0537'}, {_id:0, name:1, phone_number:1})

SQL equivalent:

SELECT _id, name, phone_number FROM companies WHERE phone_number = '206.219.0537'

Filtering by multiple fields

Find companies founded in May 2008:

db.getCollection('companies').find({founded_year:2008, founded_month:2})

SQL equivalent:

SELECT * FROM companies WHERE founded_year = 2008 AND founded_month = 2

Searching by partial text

Find companies with name containing phrase 'pay':

db.getCollection('companies').find({'name': /pay/})

SQL equivalent:

SELECT * FROM companies WHERE name LIKE '%pay%'

Or, to perform case-insensitive version of the same query:

db.getCollection('companies').find({'name': /pay/i})

SQL equivalent:

SELECT * FROM companies WHERE lower(name) LIKE '%pay%'

Combining filters

Find companies founded in 2008 or 2009:

db.getCollection('companies').find({$or: [{founded_year: 2008}, {founded_year: 2009}]})

SQL equivalent:

SELECT * FROM companies WHERE founded_year = 2008 OR founded_year = 2009

Counting elements

Find how many companies were founded in 2008:

db.getCollection('companies').find({founded_year:2008}).count()

SQL equivalent:

SELECT COUNT(*) FROM companies WHERE founded_year = 2008

Comparisons

Find companies founded in 2010 or later:

db.getCollection('companies').find({founded_year: {$gte: 2010}})

SQL equivalent:

SELECT * FROM companies WHERE founded_year >= 2010

Sorting elements

Find companies founded in 2010 or later and order them chronologically:

db.getCollection('companies').find({founded_year: {$gte: 2010}}, {founded_year: 1, founded_month: 1})
    .sort({founded_year: 1, founded_month: 1})

SQL equivalent:

SELECT founded_year, founded_month 
FROM companies 
WHERE founded_year >= 2010 
ORDER BY founded_year, founded_month

Finding by nested collection elements

Find companies with products named 'Stickam Mobile':

db.getCollection('companies').find({'products.name': 'Stickam Mobile'})

Find companies with product named 'Webchat Lite' and permalink 'webchat-lite':

db.getCollection('companies').find({'products': {$elemMatch: {name: 'Webchat Lite', permalink: 'webchat-lite'}}})

Additional projection of filtered elements:

db.getCollection('companies').find({'products': {$elemMatch: {name: 'Webchat Lite', permalink: 'webchat-lite'}}},
    {'products': {$elemMatch: {name: 'Webchat Lite', permalink: 'webchat-lite'}}})