Various queries in mongo db

This article explains, advanced queries in mongo db such as list queries, aggregation in mongo db .

Hello all. Today we will see various queries in mongo db. Please refer to my previous post to get basics of mongo db here 

To perform some queries , we need some dummy data, just run this script to populate some dummy data in mongo shell

save this as queries.js

use test;

db.names.insertMany([
{'name': 'bala', 'age': 12, 'city': 'Kanchipuram', 'state': 'Tamilnadu',
'teams': ['cricket', 'basket', 'carrom']},
{'name': 'hari', 'age': 20, 'city': 'chennai', 'state': 'Tamilnadu',
'teams': ['carrom', 'volleyball']},
{'name': 'ashok', 'age': 22, 'city': 'Banglore', 'state': 'Karnataka',
'teams': ['cricket', 'basket', 'carrom']},
{'name': 'Naveen', 'age': 12, 'city': 'Hyderabad', 'state': 'Karnataka',
'teams': ['basket', 'carrom']},
{'name': 'jalandar', 'age': 12, 'city': 'Chennai', 'state': 'Tamilnadu',
'teams': ['cricket', 'basket', 'carrom']},
{'name': 'Veda', 'age': 35, 'city': 'Gurgaon', 'state': 'Haryana',
'teams': [ 'basket', 'carrom']},
{'name': 'akansha', 'age': 50, 'city': 'Gurgaon', 'state': 'Haryana',
'teams': ['carrom']}
]);


Now run like this on terminal 

     mongo < queries.js

You will see something like this .


connecting to: test
switched to db test
{
"acknowledged" : true,
"insertedIds" : [
ObjectId("58ad6dae9cd7f52bf320cfa6"),
ObjectId("58ad6dae9cd7f52bf320cfa7"),
ObjectId("58ad6dae9cd7f52bf320cfa8"),
ObjectId("58ad6dae9cd7f52bf320cfa9"),
ObjectId("58ad6dae9cd7f52bf320cfaa"),
ObjectId("58ad6dae9cd7f52bf320cfab"),
ObjectId("58ad6dae9cd7f52bf320cfac")
]
}

It means , some data are inserted into test database.

Now let's look into queries .

Step 1: Connect to mongo shell

   mongo test

Step 2: List collections in test database by

  show collections

It will list all the collections inside  test  database.

Step 3: Check the data which are inserted into test database by

  db.names.find()

It will list all the documents inside  names collection.

Now let's get into action

Step 4: Simple find query

Let's say we need to find docs which contains  name: bala

>db.names.find({"name": "bala"})
{ "_id" : ObjectId("58ad6dae9cd7f52bf320cfa6"), "name" : "bala", "age" : 12, "city" : "Kanchipuram", "state" : "Tamilnadu", "teams" : [ "cricket", "basket", "carrom" ] }

Simple find and show specific field value alone

Let's say we have to find by some condition and show specific field values alone

(e.x) name -> bala, list value of city alone

 >db.names.find({"name": "bala"}, {"city": 1})

{ "_id" : ObjectId("58ad6dae9cd7f52bf320cfa6"), "city" : "Kanchipuram" }

If we want add more fields values to be listed then just add  {field_name : 1}
like this

 >db.names.find({"name": "bala"},{"name": 1,"city": 1})

{ "_id" : ObjectId("58ad6dae9cd7f52bf320cfa6"), "name" : "bala", "city" : "Kanchipuram" }

List field query in mongo db $in query

Now if you noticed  teams field in our data is a list field. lets query by that field

Let's find names who are in  cricket team

 >db.names.find({"teams": {"$in": ["cricket"]}}, {"name": 1})
{ "_id" : ObjectId("58ad6dae9cd7f52bf320cfa6"), "name" : "bala" }
{ "_id" : ObjectId("58ad6dae9cd7f52bf320cfa8"), "name" : "ashok" }
{ "_id" : ObjectId("58ad6dae9cd7f52bf320cfaa"), "name" : "jalandar" }

List field Not in ($nin) query

Let's say we need to find names who are not in cricket team

 >db.names.find({"teams": {"$nin": ["cricket"]}}, {"name": 1})
{ "_id" : ObjectId("58ad6dae9cd7f52bf320cfa7"), "name" : "hari" }
{ "_id" : ObjectId("58ad6dae9cd7f52bf320cfa9"), "name" : "Naveen" }
{ "_id" : ObjectId("58ad6dae9cd7f52bf320cfab"), "name" : "Veda" }
{ "_id" : ObjectId("58ad6dae9cd7f52bf320cfac"), "name" : "akansha" }

Note: You can pass multiple values in list for both $in and $nin query. Here just for example i passed only one item in list.


Greater than and lesser than query

Let's say i need to find docs less than  age < 20

>db.names.find({"age": {"$lt": 20}})
{ "_id" : ObjectId("58ad6dae9cd7f52bf320cfa6"), "name" : "bala", "age" : 12, "city" : "Kanchipuram", "state" : "Tamilnadu", "teams" : [ "cricket", "basket", "carrom" ] }
{ "_id" : ObjectId("58ad6dae9cd7f52bf320cfa9"), "name" : "Naveen", "age" : 12, "city" : "Hyderabad", "state" : "Karnataka", "teams" : [ "basket", "carrom" ] }
{ "_id" : ObjectId("58ad6dae9cd7f52bf320cfaa"), "name" : "jalandar", "age" : 12, "city" : "Chennai", "state" : "Tamilnadu", "teams" : [ "cricket", "basket", "carrom" ] }

Now greater than query age > 20

 >db.names.find({"age": {"$gt": 20}})
{ "_id" : ObjectId("58ad6dae9cd7f52bf320cfa8"), "name" : "ashok", "age" : 22, "city" : "Banglore", "state" : "Karnataka", "teams" : [ "cricket", "basket", "carrom" ] }
{ "_id" : ObjectId("58ad6dae9cd7f52bf320cfab"), "name" : "Veda", "age" : 35, "city" : "Gurgaon", "state" : "Haryana", "teams" : [ "basket", "carrom" ] }
{ "_id" : ObjectId("58ad6dae9cd7f52bf320cfac"), "name" : "akansha", "age" : 50, "city" : "Gurgaon", "state" : "Haryana", "teams" : [ "carrom" ] }

Now  age > 20 and age < 40

>db.names.find({"age": {"$gt": 20, "$lt": 40}})
{ "_id" : ObjectId("58ad6dae9cd7f52bf320cfa8"), "name" : "ashok", "age" : 22, "city" : "Banglore", "state" : "Karnataka", "teams" : [ "cricket", "basket", "carrom" ] }
{ "_id" : ObjectId("58ad6dae9cd7f52bf320cfab"), "name" : "Veda", "age" : 35, "city" : "Gurgaon", "state" : "Haryana", "teams" : [ "basket", "carrom" ] }

Multiple find condition query

Let's find docs by  city= Gurgaon and age > 20

>db.names.find({"age": {"$gt": 20}, "city": "Gurgaon"})
{ "_id" : ObjectId("58ad6dae9cd7f52bf320cfab"), "name" : "Veda", "age" : 35, "city" : "Gurgaon", "state" : "Haryana", "teams" : [ "basket", "carrom" ] }
{ "_id" : ObjectId("58ad6dae9cd7f52bf320cfac"), "name" : "akansha", "age" : 50, "city" : "Gurgaon", "state" : "Haryana", "teams" : [ "carrom" ] }

Aggregation Query

Let's find every names in doc and number teams they are in, which results every name in doc and numberoFTeams they are associated with

>db.names.aggregate([{$project: {name:1,numberOfTeams: { $size: "$teams" }}}])

{ "_id" : ObjectId("58ad6dae9cd7f52bf320cfa6"), "name" : "bala", "numberOfTeams" : 3 }
{ "_id" : ObjectId("58ad6dae9cd7f52bf320cfa7"), "name" : "hari", "numberOfTeams" : 2 }
{ "_id" : ObjectId("58ad6dae9cd7f52bf320cfa8"), "name" : "ashok", "numberOfTeams" : 3 }
{ "_id" : ObjectId("58ad6dae9cd7f52bf320cfa9"), "name" : "Naveen", "numberOfTeams" : 2 }
{ "_id" : ObjectId("58ad6dae9cd7f52bf320cfaa"), "name" : "jalandar", "numberOfTeams" : 3 }
{ "_id" : ObjectId("58ad6dae9cd7f52bf320cfab"), "name" : "Veda", "numberOfTeams" : 2 }
{ "_id" : ObjectId("58ad6dae9cd7f52bf320cfac"), "name" : "akansha", "numberOfTeams" : 1 }

Indexing a Collection by particular field

Indexing is very important in mongo db . It speeds query processing . Imagine you have millions or billions of data in collection . If you index your collection by particular fields which we gonna query it will help in minimising query processing time of mongo db.

Let's index names collection by field name and age and city

 >db.names.ensureIndex({"name": 1, "age": 1, "city": 1})
{
"createdCollectionAutomatically" : false,
"numIndexesBefore" : 1,
"numIndexesAfter" : 2,
"ok" : 1
}

Tells us docs are indexed successfully

Limiting Number of results in a query

Let's say i need to find and limit result values by 1, even though multiple documents matches query

>db.names.find({"age": {"$gt": 20}, "city": "Gurgaon"}).limit(1)
{ "_id" : ObjectId("58ad6dae9cd7f52bf320cfab"), "name" : "Veda", "age" : 35, "city" : "Gurgaon", "state" : "Haryana", "teams" : [ "basket", "carrom" ] }

Sorting a result by query

  List names and sort by age in descending order by field age

>db.names.find({}, {"name": 1, "age": 1}).sort({'age': -1})

{ "_id" : ObjectId("58ad6dae9cd7f52bf320cfac"), "name" : "akansha", "age" : 50 }
{ "_id" : ObjectId("58ad6dae9cd7f52bf320cfab"), "name" : "Veda", "age" : 35 }
{ "_id" : ObjectId("58ad6dae9cd7f52bf320cfa8"), "name" : "ashok", "age" : 22 }
{ "_id" : ObjectId("58ad6dae9cd7f52bf320cfa7"), "name" : "hari", "age" : 20 }
{ "_id" : ObjectId("58ad6dae9cd7f52bf320cfaa"), "name" : "jalandar", "age" : 12 }
{ "_id" : ObjectId("58ad6dae9cd7f52bf320cfa9"), "name" : "Naveen", "age" : 12 }
{ "_id" : ObjectId("58ad6dae9cd7f52bf320cfa6"), "name" : "bala", "age" : 12 }

List results in ascending order by field age


> db.names.find({}, {"name": 1, "age": 1}).sort({'age': 1})

{ "_id" : ObjectId("58ad6dae9cd7f52bf320cfaa"), "name" : "jalandar", "age" : 12 }
{ "_id" : ObjectId("58ad6dae9cd7f52bf320cfa9"), "name" : "Naveen", "age" : 12 }
{ "_id" : ObjectId("58ad6dae9cd7f52bf320cfa6"), "name" : "bala", "age" : 12 }
{ "_id" : ObjectId("58ad6dae9cd7f52bf320cfa7"), "name" : "hari", "age" : 20 }
{ "_id" : ObjectId("58ad6dae9cd7f52bf320cfa8"), "name" : "ashok", "age" : 22 }
{ "_id" : ObjectId("58ad6dae9cd7f52bf320cfab"), "name" : "Veda", "age" : 35 }
{ "_id" : ObjectId("58ad6dae9cd7f52bf320cfac"), "name" : "akansha", "age" : 50 }

That's it . Hope it helps. Meet you guys with another interesting topic in my blog .

Thanks for reading ! Happy Coding !!!

Leave a comment

(Note: Comments are moderated)