Notes

- show all databases
- show dbs;

- use new db and switch to new db
- use demo;

- create new collection
- db.createCollection(’collection_name’);

- show all collections in the db
- show collections;

- drop collection
- db.collection_name.drop();

- to drop database first be in that database
- db.dropDatabase();

- insert new document in collection
- insert
- insertOne
- insertMany
- db.collection_name.insert({});

- display all documents from collection
- db.collection_name.find();
- db.collection_name.find().pretty();

- to display specific documents from collection according to field
- db.collection_name.find({}, {"name":1,_id:0})
- 0 for not displaying that field and 1 for displaying those fields

- to display specific documents according to value
- db.collection_name.find({city:'surat'},{})

- to update document
- update
- updateOne
- updateMany
- db.collection_name.update({field:value},{$set:{field:newValue}})

- to remove specific field from documents
- db.collection_name.update({field:value},{$unset:{field:""}})

- to remove all documents
- db.collection_name.remove({});

- to remove specific documents
- db.collection_name.remove({field:value});

- to display unique documents according to the fields
- db.collection_name.distinct({field});

- to find total number of unique documents
- db.collection_name.distinct({city}).length; // total number of cities

- sort documents according to the field
- ascending
- db.collection_name.find().sort({field:-1}).pretty();
- descending
- db.collection_name.find().sort({field:1}).pretty();


- first few documents
- db.collection_name.find().limit(numberOfDocuments);

- last few documents
- db.collection_name.find().skip(db.collection_name.count() - 3); // last 3 documents

- Display the next five students after skipping first five which are in the collection.
- db.Students.find().skip(5).limit(5).pretty();

- count number of documents
- db.collection_name.count()

- use of regex pattern
- db.collection_name.find({name:{$regex:"^M"}}); // name first letter insertMany

- pattern matching
- ^ begin with /^/
- $ end with /..$/
- . single character //
- * multiple characters

- to increase/decrease value
- db.collection_name.updateMany({},{$inc:{"qty":30}}) // increase qty by 30
- db.collection_name.updateMany({},{$inc:{"qty":-30}}) // decrease qty by 30
- db.collection_name.updateMany({},{$mul:{"price":1.5}}) // increase price by 15%

- range of values
- db.collection_name.find({$and:[{"qty":{$gt:50}},{"qty":{$lt:100}}]}) //qty range 50-100

- relational operators
- $eq equal to
- $ne
- $gte , $lte
- $lt , $gt

- other operators
- db.Students.find({City:{$in:['Valsad','Navsari','Surat']}}).pretty(); // in
- db.Students.find({City:{$nin:['Valsad','Navsari','Surat']}}).pretty(); // not in

- logical operators
- $and, $not, $or, $nor

- Aggregation
- $match
- $project // reshaping the documents like rename/remove/concat fields
- db.Items.aggregate({$project:{_id:0,msg:{$concat:["The","$name"," stock date is ",stockdate:{$dateToString: { format: "%Y-%m-%d", date: "$stockdate"}}]}}})
- db.Items.aggregate({$project:{_id:0,msg:{$concat:["$name","price is ",{ $toString: "$price" }]}}})

- $unwind
- db.inventory.insertOne({ "_id" : 1, "item" : "ABC1", sizes: [ "S", "M", "L"] })
- db.inventory.aggregate( [ { $unwind : "$sizes" } ] )

- $group
- db.Items.aggregate({$group:{_id:0,avg_price:{$avg:'$price'}}});
- db.Students.aggregate({$group:{_id:0,City:1,number:{$sum:$City}}})

- Aggregate functions
- $sum
- $avg
- $min
- $max
- $push
- $first
- $last
- $addToSet

db.Students.aggregate([{$group: {_id: 0, uniqueValues: {$addToSet: "$City"}}}])

https://www.javainuse.com/sql2mongo
db.Students.aggregate([{
"$group": {
"_id": "$City",
"count": {
"$sum": 1
}
}
},{
"$project": {
"City": "$_id",
"count": 1,
"_id": 0
}
}])



====================================================================================================================================================
use dbname;
show dbs;
show collections;
db.dropDatabase()
db.createCollection('stud');
db.stud.insertMany([{"name":"demo"},{"name":"temp"}]);
db.stud.find().pretty();
db.stud.find({},{"name":1,"_id":0});
db.stud.distinct("city"); //unique cities
db.stud.find({"city":"surat"},{}); //get all the details of who city is surat.
db.stud.find().sort({"enro":-1}) //ascending order
db.stud.find().limit(5) //first five data
db.stud.find().skip(db.stud.count()-3) //last three data
db.stud.update({"name":"demo"},{$set:{"dob":"06/01/1922"}}); //set
db.stud.update({"name":"demo"},{$unset:{"dob":"06/01/1922"}}); //unset
db.Students.find({$or:[{"Gender":"NULL"},{"Gender":0}]}).pretty();//Null and o value
db.stud.find( { $or: [ { quantity: { $lt: 20 } }, { price: 10 } ] } ) //or ,and,lt,gt,eq,lte,gte
db.stud.remove({"city":"vapi"})//remove details who city is vapi
db.stud.find({"name":{$regex:"^.a"}},{_id:0}) //second letter is 'a'
db.stud.count() //count number of data
db.stud.distinct("city").length //total number of city
db.stud.updateMany({},{$inc:{"marks":10}});
db.stud.updateMany({},{$mul:{"price":1.5}});

db.stud.aggregate({$group:{_id:0,min:{$min:'$marks'},max:{$max:'$marks'}}});
db.stud.aggregate({$group:{_id:0,avg_marks:{$avg:'$marks'}}});
db.Items.aggregate({$project:{_id:0,msg:{$concat:["$name","price is ",{ $toString: "$price" }]}}})//display msg custome
db.Items.aggregate({$project:{_id:0,msg:{$concat:["The","$name"," stock date is ",stockdate:{$dateToString: { format: "%Y-%m-%d", date: "$stockdate"}}]}}})
---------
Array
---------
db.emp_count.insertOne({"emp_name":"ABC", "emp_skills": [["PostgreSQL", "MongoDB", "MySQL", "Perl","ORACLE"]]});
db.stud_test.find ({results: {$all: [88]}}) //display all the filed form array
db.stud_test.find({results: {$elemMatch: {$gte: 80, $lt: 95}}}) //only match given critria
db.stud_test.find ({“results": {$size: 3}}); //three recoreds show
db.stud_test.updateOne ({stud_id: 1, results: 85},{$set: {"results" : 95 }}) // identify array element and update the same into the collection
db.stud_test.updateOne( { stud_id: 1 }, { $pop: { results: -1 } } ) //remove the first and last element from an array
db.stud_test.update( { stud_id: 1 }, { $pull: { results: { $gte: 95 } } } ) // is used to remove elements from an existing array.
db.stud_test.update ({stud_id: 1}, { $push: { results: 101 }}) //push the element
---------
import and export
---------
sudo mongoimport --db newdb --collection restaurants --file primer-dataset.json
sudo mongoexport --db newdb -c restaurants --out newdbexport.json
---------
mapReduce
---------
var map=function(){ emit(this.age,this.rank)}; //(two and more value same,not same in there)
var reduce=function(age,rank){ return Array.sum(rank);}; //Array.avg(rank)
db.employee.mapReduce(map,reduce,{out :"resultCollection1"});
db.resultCollection1.find()

var mapfunction=function(){emit(this.age,this.marks)} //(two and more value same,not same in there)
var reducefunction=function(key,values){return Array.sum(values)}
db.Studentdata.mapReduce(mapfunction,reducefunction,{'out':'Result1_mapreduce'})
db.Result1_mapreduce.find()

var mapfunction=function(){emit(this.age,this.marks)} //(two and more value same,not same in there)
var reducefunction=function(key,values){return Array.sum(values)}
db.Studentdata.mapReduce(mapfunction,reducefunction,{query:{age:{$gt:18}}},{out:'Result1_mapreduce'}) //above 18
db.Result1_mapreduce.find()

---------------------------
Count Student of each city { "count" : 3, "City" : "Surat" }
---------------------------
db.Students.aggregate([{
"$group": {
"_id": "$City",
"count": {
"$sum": 1
}
}
},{
"$project": {
"City": "$_id",
"count": 1,
"_id": 0
}
}])




=====================================================================================================










1.Display only the name and price of items, whose manufacturer is “Persona”.
db.Items.find({"manufracturer":"Persona" }, { "name" : 1 ,"price" : 1 , _id:0 } );

2.Count the total number of manufacturers.
db.Items.distinct("manufracturer").length

3.Display all the item’s details, whose names starts from "Sh".
db.Items.find({"name":{$regex: "^Sh"}},{_id:0})

4.Display the items details of all different types of pens.
db.Items.find({"name":{$regex: ".Pen."}},{_id:0})

5.Display only the name and price of product along with quantity, whose price is more than 80
db.Items.find({"price":{$gt:80}},{"name":1,"price":1,_id:0}).pretty()

6.Show only the item and its price of those items, whose price is less equal to 20.
db.Items.find({"price":{$lte:20}},{"name":1,"price":1,_id:0}).pretty()

7.Display the details of those items; whose dealer code is 102 or quantity is more than 100.
db.Items.find({$or: [{"id":"102"},{"qty":{$gt:100}}]})

8.Show the details of items, whose quantity is in the range of 50 to 100.
db.Items.find({$and:[{"qty":{$gt:50}},{"qty":{$lt:100}}]})

9.Increase the price of items by 10.
db.Items.updateMany({},{$inc:{"price":10}});

10.Decrease the quantity of items by 30.
db.Items.updateMany({},{$inc:{"qty":-30}});

11.Update the price of items by increasing 15%.
db.Items.updateMany({},{$mul:{"price":1.5}});

12.Find the maximum and minimum price of item in a single query.
db.Items.aggregate({$group:{_id:0,min:{$min:'$price'},max:{$max:'$price'}}});

13.Calculate the average of item price.
db.Items.aggregate({$group:{_id:0,avg_price:{$avg:'$price'}}});

14.Calculate the total amount of items and find the average of it.
db.Items.aggregate({$group:{_id:0,total_price:{$sum:'$price'},avg_price:{$avg:'$price'}}})

15.Display all item names and its price in following format: Talcom Powder price is 40. [Hint: $concat]
db.Items.aggregate({$project:{_id:0,msg:{$concat:["$name","price is ",{ $toString: "$price" }]}}})


16. Display all item names and its stock date in following format: The Ball Pen 0.5 stock date is 2010-03-31.
db.Items.aggregate({$project:{_id:0,msg:{$concat:["The","$name"," stock date is ",stockdate:{$dateToString: { format: "%Y-%m-%d", date: "$stockdate"}}]}}})

db.Items.aggregate({$project:{stockdate:{$dateToString:{format:"%Y-%m-%d", date:"$stockdate"}}}})