8. MongoDB : Aggregate, MapReduce & Group

8. MongoDB : Aggregate, MapReduce & Group

Aggregate ############################################

//Calculate the total transaction that customer did till now (consider only transaction >= 10000)
db.customer_info.aggregate([
                        {$match:{"transaction": {$gte:100000}}},    //consider only records gte=100000
{$group:{{_id: "Cust_Id", transaction: {$sum:"$amount"}}}},   //if Cust_Id is same add the transaction
{$sort:"trasaction":1}}]);

Another Eg:

db.items.count();   //6

db.items.distinct("item");   //[ "pen", "pencil", "books" ]

db.items.count({item: "pen"});  //2

MapReduce ###########################################

Map reduce: (Looks same as Aggregation, but this works parallely)
----------
var mapFunction = function() {emit(this.customer_name,this.amount)}; // customer_name, amount are fields in customer_info.

var reduceFunction = function(customer_name,arrayOfAmounts){
return Array.sum(arrayOfAmounts);
}

//using mapFunction and educeFunction => it generates mapReduce_result table with final o/p
db.customer_info.mapReduce(mapFunction,reduceFunction,{out: "mapReduce_result"});
show tables; //you see - mapReduce_result
db.mapReduce_result.find();  //o/p will be reduced o/p

Group ############################################

//display totalAmount of particular customer_name = "John"

db.customer_info.group({key: {"customer_name":1},      //group on customer_name

                        cond: {"customer_name": "John"},            //get only "John" customer

                        reduce:function(curr,result)                        //reduce: add all the amount

                                         {result.amount += curr.amount;}, 

                        intial: {amount:0}})

7. MongoDB : GEO JSON & Geospatial Index

7. MongoDB : GEO JSON & Geospatial Index

//GEO JSON DATA ########################################################

/GEO JSON - which contains geography informations. - and location: {type: "point", coordinates: [-78.97,40.77]} this kind of data should be must.

db.createCollection("places");

db.places.insert({location: {type: "Point", coordinates: [12.925397371881461,77.63657815315337]}, name:"BangalorePizza_1", category:"PizzaShop"});

db.places.insert({location: {type: "Point", coordinates: [12.915442118877891,77.63820893631903]}, name:"BangalorePizza_2", category:"PizzaShop"});

db.places.insert({location: {type: "Point", coordinates: [12.916989810315489,77.63220078798098]}, name:"BangalorePizza_3", category:"PizzaShop"});

db.places.insert({location: {type: "Point", coordinates: [12.330624740837576,76.62695926148842]}, name:"MysorePizza_1", category:"PizzaShop"});

db.places.insert({location: {type: "Point", coordinates: [12.325232832287508,76.63530303596097]}, name:"MysorePizza_2", category:"PizzaShop"});

db.places.insert({location: {type: "Point", coordinates: [12.978403513741311,77.56954207070805]}, name:"Bangalore Railway Station1", category:"RailwayStation"});

db.places.insert({location: {type: "Point", coordinates: [12.978063446690769,77.57170478534276]}, name:"Bangalore Railway Station22", category:"RailwayStation"});

//GEO JSON INDEX ########################################################

//Geospatial Index
//to query , 1st we have to create index
//compound index -> geoIndex with normalIndex - YES possible
//compound index -> multiple geoIndex - NO not possible.
//db.collection.createIndex({<location field> : "2dsphere"}) is mandatory

db.places.createIndex({location: "2dsphere", category: -1, name:1});
db.places.createIndex({category: -1, name:1, location: "2dsphere"});
db.places.createIndex({location: "2dsphere"});

db.places.getIndexes();

//GEO JSON FIND ########################################################

db.places.find(

   { location: { $geoWithin: { $center: [ [12.91740963747045,77.63365255325667], 10 ] } } }

)

db.places.find(

   {category:"PizzaShop",location: { $geoWithin: { $center: [ [12.91740963747045,77.63365255325667], 10 ] } }}

)

db.places.find({location: {$geoWithin:{$box:[[-100,74.00],[10,100]}}})
db.places.find({location: {$geoWithin:{$geometry:{type:"Polygon", coordinates:[[[0,0],[-90,45.00],[-100,-50.00],[


     

6. MongoDB : Work with Index

6. MongoDB : Work with Index

*******************************************************************************
db.items.createIndex({..  ,  ..}, {.....}
                                                 {name:"myOwnIndex_name"}
                                                 {unique:true}
                                                 {default_languate:"spanish"}
                                                 {background:true}

db.items.dropIndex({...}
db.items.find({....}).hint({item:1})
db.items.find({....}).hint({$natural:1})
*******************************************************************************

############################################################### COLLECTION & DOCUMENT###############################################################

user test;

db.createCollection("items"); //create collection, collection or table name is "items" here

//it contains embedded document (details)
db.items.insert({"item":"Book","available":true,"soldQty":144821,"category":"NoSQl","details":{"ISDN":"1234","publisher":"XYZ Company"},"onlineSale":true});

db.items.find({"item":"Book"});
db.items.find({"item":"Book"}).pretty();

############################################################### INDEX###############################################################

create Normal Index.#####################################################

db.items.createIndex({"item":1}); //accending

db.items.createIndex({"item":-1}); //decending

db.items.getIndexes(); //list out all indexes

db.items.dropIndexes();
db.items.dropIndex("item");

//INDEX ON EMBEDDED DOCUMENT ###########################################

db.items.createIndex({"details.ISDN":1})

COMPOUND INDEX #################################################

db.items.createIndex({"item":1,"available":-1});
//index name = item_1_available_1

COMPOUND INDEX with ARRAY AND FIELD##############################
//here courses is array, and name is field
//creating index on array -> MangoDB - creates index on each element in array, so we should be very care full while creating

db.items.createIndex({"courses":1,"name":-1})

HASHED INDEX#############################################

db.items.createIndex({item:"hashed"})

unique INDEX #################################################
//unique ness on value inside document.

db.items.createIndex({"item":1},{unique:true});

TEXT INDEX#############################################
//we can give our own name for index
db.items.createIndex({item:"text"},{name:"item_index"})

TEXT INDEX - default language ##############################

db.customer_info.createIndex({"item":"text"},{default_languate:"spanish"})

INDEX CREATING IN BACKGROUND ##############################
//key like "item" need not to be in  " ".... (Its not mandatory)

db.items.createIndex({item:1},{background:true});

DROP INDEX ######################################

db.items.dropIndex({"item":1})
db.items.dropIndexes()

MONITOR INDEX GOING STATUS AND KILL ##############################

db.currentOp() //to monitor index going status
db.killOp()

PRINT ALL INDEXES  FROM ALL COLLECTIONS ##############################
//Note: we have db.items.getIndexes() -> which gets all index for particular colletion
//to get all indexes from all collection we have to for loop and print as shown below.

db.getCollectionNames().forEach(
function(collection)
   {
      indexes = db[collection].getIndexes();
      print("indexes for collection :"+collection+" : ");
      printjson(indexes);
    }
)

TO CHECK THE SQL QUERY USED FOR INDEX CREATION##############################

db.collection.explain() OR  cursor.explain()

FEW HELP QUIRES ##############################

help

db.items.help()

db.items.storageSize()
db.items.totalIndexSize()
db.items.reIndex()
db.printCollectionStats()

FIND WITH HINT COMMAND ##############################
//this is to tell mangodb to use only index mentioned in the hint while finding the records

//here to use "item" index using hint
db.items.find({item: "Book", available: true }).hint({item:1})

//here not to use any index - using $natural
db.items.find({item: "Book", available: true }).hint({$natural:1}).explain("executionStats")

5. MongoDB : Work with (Database, Collection and Documents)

5. MongoDB : Work with (Database, Collection and Documents)

********************************************************************
db.createCollection(...)
db.items.insert(...);       //{....}
db.items.find(...);         // {....}, $lt, $lte, $gte, $in, $or, /...,  $slice,  $elemMatch, 
db.items.update(...)      // {....}. $set
********************************************************************

show dbs;     //show all existing db's
use test;        //to switch to test db, if test does not exist then it creates and then switch

show collections;

db.createCollection("items");

//insert  ####################################################

db.items.insert({"name":"Sumit","courses":["mongodb","AWS","GCP"]});  //create document inside collection

db.items.find();

db.items.find({"name":"Sumit"});

db.items.find({"name":"Sumit"}).pretty();

//Bulk insert, if we dont give _id -> then it mongodb takes some unique id

var bulk = db.items.initializeOrderedBulkOp();

bulk.insert({_id:1, item:"pen", available:true, soldQty:700});

bulk.insert({_id:2, item:"pencil", available:false, soldQty:900});

bulk.insert({_id:3, item:"books", available:true, soldQty:700});

bulk.execute();

var bulk = db.items.initializeUnorderedBulkOp();

bulk.insert({_id:4, item:"pen", available:true, soldQty:700});

bulk.insert({_id:5, item:"pencil", available:false, soldQty:900});

bulk.insert({_id:6, item:"books", available:true, soldQty:700});

bulk.execute();

db.items.find();

db.items.find({available:true}).limit(5);  //limit(5) = row=5

db.items.findOne();

db.items.findOne({available:true});

db.items.find({available:true}).pretty();

db.items.find({soldQty: {$lt:800}});     //lessthan 800

db.items.find({soldQty: {$lte:800}, available:true});  //lessthan or equal to

db.items.find({available:{$in:[true,false]}});  //IN

db.items.find({available:true,soldQty:{$lt:900}}); //AND or ,

db.items.find({ $or: [{available:true},{soldQty:700}] }); //OR condition, note: within that mention in {},{}

//regular expression  ####################################################

//regular expression match, eg: in website search_______ anything in website

db.items.find({item:/pe/i});   //caseinsensitive //search anything which has 'pe' or 'i' in it

db.items.find({item:/Pen?/i}); //caseinsensitive //search anything which has 'pe' or 'i' in it

//array serch  ####################################################

db.itesm.find({country_codes:5});  //if country_codes is array - then search for '5' inside that.

db.itesm.find({country_codes.0:5});

//projection quries. (Only for Arrays : $elemMatch, $slice)

//here country_codes is array.

db.items.find({_id:5},{country_codes:{$slice:2}}); //shows only 1st 2 array elements, 

note: if you give {$slice:-2} then it shows last 2 elements of array.

db.itesm.find({country_codes:{$elemMatch:{$gte:3,$lte:6}}}); //between 3 and 6, inside an array elemMatch

//javascript inside query ####################################################

//javascript inside query - using "$where"

db.items.find({"$where":function().......});

db.items.find(({"$where": "this.x + this.y == 10"});

db.items.find(({"$where": "function() {return this.x + this.y == 10;}"});

//cursor, ####################################################

//cursor, light way of handling on dataset query.

var cursor = db.collection.find();

while(cursor.hasNext()) {

....obj= cursor.next();

.....

}

db.c.find.skip(3);

db.c.find().soft({username:1,age:-1})

//UPDATE RECORD ####################################################

//update document where item=Book
//Note: below taken is different example of "items" collection.

db.items.update({item:"Book"},{$set: {category: 'NoSQL', details: {ISDN: "1234", publisher:"XYZ"}}});

//update an embedded field
db.items.update({item: "Pen"},{$set: {"details.model":"14Q2"}});

//update multiple documents. (multi:true)
db.items.update({item: "Pen"},{$set:{catogory:"stationary"},$currentDate:{lastModified:true}},{multi:true});

4. MongoDB : Installation steps

4. MongoDB : Installation steps


//check DB ranking in market
###############################################################
https://db-engines.com/en/ranking

Install MangoDB (Install MangoDB needs - 30GB and few GB of RAM.)
###############################################################
https://docs.mongodb.com/manual/tutorial/install-mongodb-on-windows/
-> 3rd link -> https://docs.mongodb.com/manual/tutorial/install-mongodb-on-windows/#install-mdb-edition
-> MongoDB Download Center.
-> "Community Server" tab

https://www.mongodb.com/download-center?_ga=2.49479651.473795272.1532242152-1894495752.1532242152#community

###############################################################

First shell

-- Navigate to C:\Program Files\MongoDB\Server\4.0\bin\
-- mongod.exe --dbpath D:\mongoDBDatabase --rest --journal  // --replSet (Personal database path) 

Second shell

-- Navigate to C:\Program Files\MongoDB\Server\4.0\bin\
-- mongo.exe

3. MongoDB : NO SQL databases types

3. MongoDB : NO SQL databases types

2. MongoDB : Comparison with RDBMS

2. MongoDB : Comparison with RDBMS