Hands on guide of mongodb query statements

Ketan Trentiya

·

Follow

3 min read

·

May 7, 2021

--

Listen

Share

List out all the databases

show dbs

• Returns list of the databases
• The database which is created, And has not a single collection, will be not display in the list

Show selected database

db

• Returns name of the selected database

Create or Select database

use mySchool

• Will select mySchool database
• If mySchool database is not created, then will create the mySchool database and select it right a way

All the database queries will affect to selected database. So one should always check or select the database before firing any database query

Drop / Delete database

• First of all select database to be drop / delete

db.dropDatabase();

Create new collection

db.createCollection("students");

List all the collections

show collections;

• Returns list of the collections

Drop / Delete collection

db.collection_name.drop();

• Returns true if collection drop or deleted, otherwise false

Insert new document into collection

db.players.insert({"FirstName": "John", "LastName": "Doe"});

• If players collection is not present when inserting the document It will create new collection named players, and then insert the new document into it.

Insert multiple documents

db.players.insert(
  [
   {
    "FirstName": "Shubman",
    "LastName": "Gill",
    "Age": "22",
    "team": "KKR"
   },
   {
    "FirstName": "Eoin",
    "LastName": "Morgan",
    "Age": "30",
    "team": "KKR"
   },
   {
    "FirstName": "Rohit",
    "LastName": "Sharma",
    "Age": "20",
    "team": "Mumbai Indians"
   },
   {
    "FirstName": "Shikhar",
    "LastName": "Dhawan",
    "Age": "22",
    "team": "Delhi Capitals"
   }
  ]
 );

Select / Query all the documents of the collection

db.players.find();

Display queried result into prettified format

db.players.find().pretty();

Select very first document of the collection

db.players.findOne();ordb.players.findOne({"FirstName": "John"});

Conditional select statements

Equal to

db.players.find({"FirstName": "Rahul"});

Greather than

db.players.find({"Age": {$gt : "22"}});

Greater than or Equal to

db.players.find({"Age" : {$gte: "22"}});

Less than

db.players.find({"Age" : {$lt: "22"}});

Less than or Equal to

db.players.find({"Age" : {$lte: "22"}});

Not Equal to

db.players.find({"Age": {$ne: "22"}});

AND

db.players.find({"Age": {$gt: "22"}, "team": "KKR"});

• Here “,” will react as AND operator

OR

( 1 )
db.players.find({ $or: [{"Age": "22"},{"team": "KKR"}]});( 2 )
db.players.find({ $or: [{"Age": {$gte: "22"}},{"team": "KKR"}]});

AND & OR together

db.players.find(
{
 "Age": {$gte: "22"},
 $or: [
  {"team": "KKR"},
  {"team":"DC"}
 ]
});

Update only Single document

( 1 )
db.players.update(
 { "_id": ObjectId("6093cac81938510fc4258cb3") },
 { $set: {"team":"Delhi Capital"} }
);( 2 )
db.players.update(
 {"team": "KKR"},
 {$set: {"team": "Kolkota Knight Riders"}}
);

• This statement will update very first single document only, with field “team” === “KKR”, and will not update other with “team” === “KKR”

Update multiple documents

( 1 )
db.players.update(
 {"team": "KKR"},
 {$set: {"team": "Kolkota Knight Riders"}},
 {multi: true}
);( 2 )
db.players.update(
 {"Age": {$lte: "22"}},
 {$set: {"young": true}},
 {multi: true}
);

Update or Insert if document not found

db.players.save(
 {
  "_id" : ObjectId("6093b1d11938510fc4258cae"),
  "FirstName" : "Nitish",
  "LastName" : "Rana",
  "Age" : "27",
  "team": "Kolkota Knight Riders"
 }
);

Insert using custom ID

db.players.save(
 {
  "_id" : ObjectId("000000000000000000000001"),
  "FirstName" : "Rohit",
  "LastName" : "Sharma",
  "Age" : "32",
  "team" : "Mumbai Indians"
 }
);

• Make sure the custom ID should be 24 characters long

Delete / Remove documents

Remove using ID

db.players.remove({"_id" : ObjectId("6093d6c91938510fc4258cb7")});

Remove only first occurance of the satisfied codition

db.players.remove({"team": "Kolkota Knight Riders"}, 1);

Remove all the document which satisfied condition

db.players.remove({"team": "Kolkota Knight Riders"});

Remove all the documents from collection

db.players.remove({});

Projection

( 1 )
db.players.find({}, {"FirstName": 1});( 2 )
db.players.find({}, {"FirstName": 0});( 3)
db.players.find({}, {"FirstName": 1, "_id": 0});

• To select specific field from the document
• “_id” field will be included in result by default, To explicitly omit from the result implement “_id” field as false, like ( i.e. : db.players.find({},{“FirstName”:1,”_id”:0}) )

Limit

db.players.find().limit(4);

Skip

db.players.find().skip(2);

Sort

( 1 )
db.players.find().sort({"team":1});( 2 )
db.players.find().sort({"team": -1});

• 1 for ascending order, -1 for descending order

Indexing

Create Index

db.collection_name.ensureIndex({"key": 1});

Drop Index

db.collection_name.dropIndex({"key": 1});

• Make sure to give index only those field/column, which will hold unique value

Aggregate

Sum

db.players.aggregate(
 [
  {
   $group: {
    _id: "$team",
    totalPlayers: { $sum: 1}
   }
  }
 ]
);

Max

db.players.aggregate(
 [
  {
   $group: {
    "_id": "$team", maxAgePlayer: {$max: "$Age"}
   }
  }
 ]
);

Min

db.players.aggregate(
 [
  {
   $group: {
    "_id": "$team", minAgePlayer: {$min: "$Age"}
   }
  }
 ]
);