MongoDB command cheatsheet

MongoDB is open-source, NoSQL database that stores data in a JSON-like document. It has highly flexible and dynamic data model which is faster, agile and scalable. It evolves to meet the need to manage the growing size and complexity of data. This post is all about MongoDB cheat sheet for quick reference.

MySQL & MongoDB

Install, Start & Stop

Installation steps will vary from system to system, follow this link to know more.

MongoDB Component

Connecting to MongoDB

db.createUser({
  user: "dbadmin",
  pwd: "12345678",
  roles: [{ role: "dbOwner", db: "admin" }],
})

Database

Collection

Document: CRUD

insert document into 'users' collection

db.users.insert({
  userid: "123",
  age: 18,
  name: "vikash",
})

find all documents in 'users' collection

db.users.find()

find all documents from collection ‘users’ but select field ‘_id’, ‘name’ & ‘age’ only

db.users.find(
  {},
  {
    name: 1,
    age: 1,
  }
)

find all documents from collection ‘users’ but select field ‘name’ & ‘age’ only. By default ‘_id’ field is always selected, so to remove it.

db.users.find(
  {},
  {
    _id: 0,
    name: 1,
    age: 1,
  }
)

find all documents from collection ‘users’ where ‘name’=‘vikash’

db.users.find({
  name: "vikash",
})

find all documents from collection ‘users’ where ‘name’=‘vikash’ and select field ‘userid’ only

db.users.find(
  {
    name: "vikash"
  },
  {
    _id: 0
    userid: 1
  }
)

find all documents from collection ‘users’ where ‘name’!=‘vikash’

db.users.find({
  name: {
    $ne: "vikash",
  },
})

find all documents from collection ‘users’ where ‘name’=‘vikash’ and age=18

db.users.find({
  name: "vikash",
  age: 18,
})

find all documents from collection ‘users’ where ‘name’=‘vikash’ or age=18

db.users.find({
  $or: [
    {
      name: "vikash",
    },
    {
      age: 18,
    },
  ],
})

find all documents from collection ‘users’ where ‘age > 18’

db.users.find({
  age: {
    $gt: 18,
  },
})

find all documents from collection ‘users’ where ‘age < 18’

db.users.find({
  age: {
    $lt: 18,
  },
})

find all documents from collection ‘users’ where ‘age >= 18’

db.users.find({
  age: {
    $gte: 18,
  },
})

find all documents from collection ‘users’ where ‘name is like %ind%’

db.users.find({
  name: /ind/,
})

find all documents from collection ‘users’ where ‘name is like ind%’

db.users.find({
  name: /^ind/,
})

find all documents from collection ‘users’ where ‘name is like %ind%’ and order(ASC) by field ‘age’

db.users
  .find({
    name: /ind/,
  })
  .sort({
    age: 1,
  })

find all documents from collection ‘users’ where ‘name is like %ind%’ and order(DESC) by field ‘age’

db.users
  .find({
    name: /ind/,
  })
  .sort({
    age: -1,
  })

find the number of documents in collection ‘users’

db.users.find().count()

find the number of documents in collection ‘users’ where field ‘name’ exist

db.users
  .find({
    name: {
      $exists: true,
    },
  })
  .count()

show distinct value for field ‘name’ of collection ‘users’

db.users.distinct("name")

fetch 2 document skipping first 5 documents from collection ‘users’

db.users
  .find()
  .limit(2)
  .skip(5)

updated field ‘age’ to 19 of collection ‘users’ where name = ‘vikash’

db.users.update(
  {
    name: "vikash",
  },
  {
    $set: { age: 19 },
  },
  {
    multi: true,
  }
)

increase current value of field ‘age’ by 5 of collection ‘users’ where name = ‘vikash’

db.users.update(
  {
    name: "vikash",
  },
  {
    $inc: { age: 5 },
  },
  {
    multi: true,
  }
)

delete all documents from ‘users’ collection

db.users.remove({})

delete all the documents of collection ‘users’ where name = ‘vikash’

db.users.remove({
  name: "vikash",
})

Aggregate

SQL: SELECT, GROUP BY, HAVING

MongoDB: $project, $group, $match

count number of users in each ‘age’ group

db.users.aggregate([
  {
    $group: {
      _id: "$age",
      num_usr: { $sum: 1 },
    },
  },
])

count number of users in each ‘age’ group where name=“vikash” & sort by ‘age’

db.users.aggregate([
  { $match: {name: "vikash"} },
  { $group: {_id: "$age", num_usr: {$sum: 1} } }
  { $sort: {age: 1} }
])

sum of field ‘age’ in each ‘name’ group

db.users.aggregate([
  {
    $group: {
      _id: "$name",
      sum_age: { $sum: "$age" },
    },
  },
])

average age in each ‘name’ group

db.users.aggregate([
  {
    $group: {
      _id: "$name",
      avg_age: { $avg: "$age" },
    },
  },
])

minmum age in each ‘name’ group

db.users.aggregate([
  {
    $group: {
      _id: "$name",
      avg_age: { $min: "$age" },
    },
  },
])

maximum age in each ‘name’ group

db.users.aggregate([
  {
    $group: {
      _id: "$name",
      avg_age: { $max: "$age" },
    },
  },
])

Index

Create an index on field ‘name’ of collection ‘users’

db.users.ensureIndex({
  name: 1,
})

Drop an index from field ‘name’ of collection ‘users’

db.users.dropIndex({
  name: 1,
})

Create an compound index on field ‘name’ & ‘age’ of collection ‘users’

db.users.ensureIndex({
  name: 1,
  age: 1,
})

Drop an compound index on field ‘name’ & ‘age’ of collection ‘users’

db.users.dropIndex({
  name: 1,
  age: 1,
})

MongoImport & MongoExport

mongoimport & mongoexport is not recommended for production backup due to difference in json & bson, instead use mongodump & mongorestore. Don't run mongoimport & mongoexport from mongo shell becuase these are independent command.

Import data from accounts.json file to users db & accounts collection by default connect to localhost & port 27017

mongoimport --db users --collection accounts --file accounts.json

Import data from accounts.json file to users db, collection(on omit) name will be file name

mongoimport --db users --file accounts.json

Import data from accounts.json file to users db, replace matching(_id field) document

mongoimport --db users --mode upsert --file accounts.json

Import data from accounts.json file to users db, merge matching(_id field) document

mongoimport --db users --mode merge --file accounts.json

Import data from accounts.csv file to users db, collection accounts, headerline is not mandatory but mongodb uses it to identify fieldname

mongoimport --db users --type csv --headerline --file accounts.json

Import data from account.json file to remote host:port with auth enabled

mongoimport -h mongodb1.example.com:3000
-u demouser -p demopwd -d users -c accounts
--file accounts.json

Import data from account.json file to remote host and port with auth enabled

mongoimport --host mongodb1.example.com
--port 3000
--username demouser --password demopwd
--db users --collection accounts
--file accounts.json

Export accounts collection to accounts.json file

mongoexport --db users --collection accounts
--out accounts.json

Export accounts collection to accounts.csv file, –fields are mandatory

mongoexport --db users --collection accounts
--type=csv --fields name,address
--out accounts.csv

Export accounts collection from remote host to accounts.json file

mongoexport --db users --collection accounts
-h mongodb1.example.com:3000
-u user -p 'password'
--out accounts.json

Mongodump & Mongorestore

User must have proper access control before running mongodump & mongorestore. Two built-in mongodb role backup & restore can be used to give proper priviledge to user to perform backup & restore operation.

Dump all data from defaults host-localhost:27017 & dumpDirectroy-dump/

mongodump

Dump all data from specific host

mongodump --host mongodb1.example.com --port 300

Dump all data from remote host with auth enabled

mongodump --host mongodb.example.net --port 27017
--username user --password "pass"

Dump all data to specific dump directory

mongodump --out mongodb1/date23Sept/

Dump only specific data

mongodump --db users --collection account

Restore from backuped location to default host localhost:27017

mongorestore mongodb1/date23Sept/

Restore from backuped location to specific host

mongorestore --host mongodb1.example.com
--port 3000 mongodb1/date23Sept/

Restore from backuped location to specific host with auth enabled

mongorestore --host mongodb1.example.com --port 3000
--username user --password 'pass'
mongodb1/date23Sept/

Replication & Sharding

Replication: is the process of syncing data across multiple nodes to provide availability in case of failure of another node. It increases the redundancy but increases the quality of service. Minimum 3 node is required, one is primary and others are secondary. Primary sync the data to secondary. Write happen through primary.

In case of failure of the primary node, secondary node can become primary node and once original node got recovered he again gets the role of the primary node.In order to add more host to replicaset(rs), first connect to primary node through mongo client then use below command, HOST_NAME is the address of the new node to be added. Each rs has a limit of 12 nodes.

rs.add(HOST_NAME:PORT)

Sharding: is the process of distributing data (when data grows considerably) across multiple machines to reduce load and increase the quality of service through horizontal scaling. Each shard is different replica set. Sharding in mongo is implemented through 3 component.

1. Shard: used to store actual data. Each shard contain a subset of data, not the whole data. each shard can be deployed as a replica set.
2. Config Server: this store the metadata related to the mapping of cluster’s data set to shard. Query server uses these metadata to target shard. Exactly 3 servers is used for this.
3. Query Router: client sends the query to this server and on behalf of the client, it communicates to config server, get the target shard and return data from target shard to client. 2 or more than 2 server is used to distribute the client load.