DBA TO CLOUD AND BIGDATA SOLUTION: MONGODB Aggregation Operations

-- TO VIEW THE DOCUMENT: ALL COLUMNS

> db.doccollect.find({likes:100});

{ "_id" : ObjectId("5b4abeec47c637ee06e1cbc9"), "title" : "MongoDB doc100", "description" : "MongoDB document 1", "by" : "ashok", "tags" : [ "mongodb", "NoSQLDB", "DocDB", "database" ], "likes" : 100 }

{ "_id" : ObjectId("5b4abfdf47c637ee06e1cbca"), "title" : "MongoDB doc100", "description" : "MongoDB document 1", "by" : "ashok", "tags" : [ "mongodb", "NoSQLDB", "DocDB", "database" ], "likes" : 100 }

{ "_id" : ObjectId("5b4ac1f347c637ee06e1cbcb"), "title" : "MongoDB doc100", "description" : "MongoDB document 1", "by" : "ashok", "tags" : [ "mongodb", "NoSQLDB", "DocDB", "database" ], "likes" : 100 }

{ "_id" : ObjectId("5b4ac2d81df2d6fb5e3de700"), "title" : "casandra doc1", "description" : "NoSQLDB", "by" : "Ashok", "tags" : [ "NoSQL", "DocDB", "NotOnlySQL" ], "likes" : 100 }

> > db.doccollect.find().pretty();

-- ALL COLUMNS WITHOUT _ID COLUMN

> db.doccollect.find({likes:100},{_id:0});

{ "title" : "MongoDB doc100", "description" : "MongoDB document 1", "by" : "ashok", "tags" : [ "mongodb", "NoSQLDB", "DocDB", "database" ], "likes" : 100 }

{ "title" : "casandra doc1", "description" : "NoSQLDB", "by" : "Ashok", "tags" : [ "NoSQL", "DocDB", "NotOnlySQL" ], "likes" : 100 }

>> db.doccollect.find({like:100},{_id:1});

> db.doccollect.find({likes:100});

{ "_id" : ObjectId("5b4ac2d81df2d6fb5e3de700"), "title" : "casandra doc1", "description" : "NoSQLDB", "by" : "Ashok", "tags" : [ "NoSQL", "DocDB", "NotOnlySQL" ], "likes" : 100 }

-- TO VIEW THE DOCUMENT: ONLY _id COLUMN

> db.doccollect.find({likes:80},{_id:1});

{ "_id" : ObjectId("5b4acd8b1df2d6fb5e3de78d") }

> db.doccollect.find({likes:100},{_id:1});

{ "_id" : ObjectId("5b4abeec47c637ee06e1cbc9") }

{ "_id" : ObjectId("5b4abfdf47c637ee06e1cbca") }

{ "_id" : ObjectId("5b4ac1f347c637ee06e1cbcb") }

{ "_id" : ObjectId("5b4ac2d81df2d6fb5e3de700") }

-- TO VIEW THE DOCUMENTS: ONE COLUMN WITHOUT _id column

> db.doccollect.find({likes:80},{_id:0,title:1});

{ "title" : "MongoDB doc10" }

> db.doccollect.find({likes:100},{_id:0,title:1});

{ "title" : "MongoDB doc100" }

{ "title" : "casandra doc1" }

-- TO VIEW THE DOCUMENT: MULTIPLE COLUMNS WITHOUT _id column

> db.doccollect.find({likes:100},{_id:0,title:1,by: "ashok", likes:1});

{ "title" : "MongoDB doc100", "by" : "ashok", "likes" : 100 }

{ "title" : "casandra doc1", "by" : "Ashok", "likes" : 100 }

> db.doccollect.find({likes:80}, {_id:0,title:1, by: "Ashok",title:1});

{ "title" : "MongoDB doc10", "by" : "Ashok" }

> db.doccollect.find({likes:80},{_id:1,title:1});

{ "_id" : ObjectId("5b4c8f965d075b50580db385"), "title" : "PostGresql doc1" }

> db.doccollect.find({likes:80},{_id:1,title:1,tags:1});

{ "_id" : ObjectId("5b4c8f965d075b50580db385"), "title" : "PostGresql doc1", "tags" : [ "Postgresql", "rdbmsdoc", "nosql" ] }

#CREATE INVENTORY COLLECTION

# INSERT items in inventory collection

> db.inventory.insert({ item: "canvas", qty: 100, tags: ["cotton"], size: { h: 28, w: 35.5, uom: "cm" } });

-- Find all items in inventory collection

> db.inventory.find();

> db.inventory.insert([

... { item: "journal", qty: 25, tags: ["blank", "red"], size: { h: 14, w: 21, uom: "cm" } },

... { item: "mat", qty: 85, tags: ["gray"], size: { h: 27.9, w: 35.5, uom: "cm" } },

... { item: "mousepad", qty: 25, tags: ["gel", "blue"], size: { h: 19, w: 22.85, uom: "cm" } }

... ]);

> db.inventory.find();

{ "_id" : ObjectId("5b4c96325d075b50580db386"), "item" : "canvas", "qty" : 100, "tags" : [ "cotton" ], "size" : { "h" : 28, "w" : 35.5, "uom" : "cm" } }

{ "_id" : ObjectId("5b4c970b5d075b50580db387"), "item" : "journal", "qty" : 25, "tags" : [ "blank", "red" ], "size" : { "h" : 14, "w" : 21, "uom" : "cm" } }

{ "_id" : ObjectId("5b4c970b5d075b50580db388"), "item" : "mat", "qty" : 85, "tags" : [ "gray" ], "size" : { "h" : 27.9, "w" : 35.5, "uom" : "cm" } }

{ "_id" : ObjectId("5b4c970b5d075b50580db389"), "item" : "mousepad", "qty" : 25, "tags" : [ "gel", "blue" ], "size" : { "h" : 19, "w" : 22.85, "uom" : "cm" } }

-- TO VIEW THE DOCUMENT: EQUAL TO

-- USING CHARACTER

> db.doccollect.find({"by" : "ashok"},{by:1,title:1,_id:0});

{ "title" : "MongoDB doc100", "by" : "ashok" }

> db.doccollect.find({"by" : "Ashok"},{by:1,title:1,_id:0});

{ "title" : "casandra doc1", "by" : "Ashok" }

{ "title" : "MongoDB doc10", "by" : "Ashok" }

-- USING NUMBER

> db.doccollect.find({likes:80},{by:1,title:1,_id:0,likes:1});]

{ "title" : "MongoDB doc10", "by" : "Ashok", "likes" : 80 }

> db.doccollect.find({likes:100},{by:1,title:1,_id:0,likes:1});

{ "title" : "MongoDB doc100", "by" : "ashok", "likes" : 100 }

{ "title" : "casandra doc1", "by" : "Ashok", "likes" : 100 }

-- USING CHARACTER - not equal to

> db.doccollect.find({by: {$ne: "ashok"}}, {by:1,title:1,_id:0});

{ "title" : "casandra doc1", "by" : "Ashok" }

{ "title" : "MongoDB doc10", "by" : "Ashok" }

{ "title" : "Artificial Inteligence", "by" : "Siva" }

-- USING NUMBER -- NOT EQUAL TO -- $ne

db.doccollect.find({likes: {$ne: 100}}, {by:1,title:1,_id:0});

> db.doccollect.find({likes: {$ne: 100}}, {by:1,title:1,_id:0});

{ "title" : "MongoDB doc10", "by" : "Ashok" }

{ "title" : "Artificial Inteligence", "by" : "Siva" }

> db.doccollect.find({likes:{$ne:80}},{by:1,title:1,_id:0});

{ "title" : "MongoDB doc100", "by" : "ashok" }

{ "title" : "casandra doc1", "by" : "Ashok" }

{ "title" : "Artificial Inteligence", "by" : "Siva" }

> db.users10.find({"Age":{$ne : 30}}, {Name:1,Age:1,Gender:1,_id:0});

{ "Name" : " Test User1", "Age" : 11, "Gender" : "F" }

{ "Name" : " Test User2", "Age" : 12, "Gender" : "F" }

{ "Name" : " Test User3", "Age" : 13, "Gender" : "F" }

{ "Name" : " Test User4", "Age" : 14, "Gender" : "F" }

{ "Name" : " Test User5", "Age" : 15, "Gender" : "F" }

{ "Name" : " Test User6", "Age" : 16, "Gender" : "F" }

{ "Name" : " Test User7", "Age" : 17, "Gender" : "F" }

{ "Name" : " Test User8", "Age" : 18, "Gender" : "F" }

{ "Name" : " Test User9", "Age" : 19, "Gender" : "F" }

{ "Name" : " Test User10", "Age" : 20, "Gender" : "F" }

{ "Name" : " Test User11", "Age" : 21, "Gender" : "F" }

{ "Name" : " Test User12", "Age" : 22, "Gender" : "F" }

{ "Name" : " Test User13", "Age" : 23, "Gender" : "F" }

{ "Name" : " Test User14", "Age" : 24, "Gender" : "F" }

{ "Name" : " Test User15", "Age" : 25, "Gender" : "F" }

{ "Name" : " Test User16", "Age" : 26, "Gender" : "F" }

{ "Name" : " Test User17", "Age" : 27, "Gender" : "F" }

{ "Name" : " Test User18", "Age" : 28, "Gender" : "F" }

{ "Name" : " Test User19", "Age" : 29, "Gender" : "F" }

{ "Name" : " Test User1", "Age" : 11, "Gender" : "F" }

Type "it" for more

> db.users10.find({Name : {$ne : " Test User20"}}, {Name:1,Age:1,Gender:1,_id:0});

{ "Name" : " Test User1", "Age" : 11, "Gender" : "F" }

{ "Name" : " Test User2", "Age" : 12, "Gender" : "F" }

{ "Name" : " Test User3", "Age" : 13, "Gender" : "F" }

{ "Name" : " Test User4", "Age" : 14, "Gender" : "F" }

{ "Name" : " Test User5", "Age" : 15, "Gender" : "F" }

{ "Name" : " Test User6", "Age" : 16, "Gender" : "F" }

{ "Name" : " Test User7", "Age" : 17, "Gender" : "F" }

{ "Name" : " Test User8", "Age" : 18, "Gender" : "F" }

{ "Name" : " Test User9", "Age" : 19, "Gender" : "F" }

{ "Name" : " Test User10", "Age" : 20, "Gender" : "F" }

{ "Name" : " Test User11", "Age" : 21, "Gender" : "F" }

{ "Name" : " Test User12", "Age" : 22, "Gender" : "F" }

{ "Name" : " Test User13", "Age" : 23, "Gender" : "F" }

{ "Name" : " Test User14", "Age" : 24, "Gender" : "F" }

{ "Name" : " Test User15", "Age" : 25, "Gender" : "F" }

{ "Name" : " Test User16", "Age" : 26, "Gender" : "F" }

{ "Name" : " Test User17", "Age" : 27, "Gender" : "F" }

{ "Name" : " Test User18", "Age" : 28, "Gender" : "F" }

{ "Name" : " Test User19", "Age" : 29, "Gender" : "F" }

{ "Name" : " Test User1", "Age" : 11, "Gender" : "F" }

Type "it" for more

------GREATER THAN

> db.doccollect.find({likes: {$gt :80}}, {by:1,title:1,_id:0,likes:1});

{ "title" : "MongoDB doc100", "by" : "ashok", "likes" : 100 }

{ "title" : "casandra doc1", "by" : "Ashok", "likes" : 100 }

> db.users10.find({Age:{$gt :50}}, {Name:1,Age:1,Gender:1,_id:0});

{ "Name" : " Test User41", "Age" : 51, "Gender" : "F" }

{ "Name" : " Test User42", "Age" : 52, "Gender" : "F" }

{ "Name" : " Test User43", "Age" : 53, "Gender" : "F" }

{ "Name" : " Test User44", "Age" : 54, "Gender" : "F" }

{ "Name" : " Test User45", "Age" : 55, "Gender" : "F" }

{ "Name" : " Test User46", "Age" : 56, "Gender" : "F" }

{ "Name" : " Test User47", "Age" : 57, "Gender" : "F" }

{ "Name" : " Test User48", "Age" : 58, "Gender" : "F" }

{ "Name" : " Test User49", "Age" : 59, "Gender" : "F" }

{ "Name" : " Test User50", "Age" : 60, "Gender" : "F" }

{ "Name" : " Test User51", "Age" : 61, "Gender" : "F" }

{ "Name" : " Test User52", "Age" : 62, "Gender" : "F" }

{ "Name" : " Test User53", "Age" : 63, "Gender" : "F" }

{ "Name" : " Test User54", "Age" : 64, "Gender" : "F" }

{ "Name" : " Test User55", "Age" : 65, "Gender" : "F" }

{ "Name" : " Test User56", "Age" : 66, "Gender" : "F" }

{ "Name" : " Test User57", "Age" : 67, "Gender" : "F" }

{ "Name" : " Test User58", "Age" : 68, "Gender" : "F" }

{ "Name" : " Test User59", "Age" : 69, "Gender" : "F" }

{ "Name" : " Test User60", "Age" : 70, "Gender" : "F" }

-- GREATHER THAN OR EUAL TO

> db.doccollect.find({likes:{$gte:80}}, {by:1,title:1,_id:0,likes:1});

{ "title" : "MongoDB doc100", "by" : "ashok", "likes" : 100 }

{ "title" : "casandra doc1", "by" : "Ashok", "likes" : 100 }

{ "title" : "MongoDB doc10", "by" : "Ashok", "likes" : 80 }

> db.doccollect.find({likes:{$gte:100}}, {by:1,title:1,_id:0,likes:1});

{ "title" : "MongoDB doc100", "by" : "ashok", "likes" : 100 }

{ "title" : "casandra doc1", "by" : "Ashok", "likes" : 100 }

-- LESS THAN

> db.doccollect.find({likes:{$lt:100}}, {by:1,title:1,_id:0,likes:1});

{ "title" : "MongoDB doc10", "by" : "Ashok", "likes" : 80 }

> db.users10.find({Age:{$lt:50}},{Age:1,Gender:1,Country:1,_id:0});

{ "Age" : 11, "Gender" : "F", "Country" : "U.K." }

{ "Age" : 12, "Gender" : "F", "Country" : "U.K." }

{ "Age" : 13, "Gender" : "F", "Country" : "U.K." }

{ "Age" : 14, "Gender" : "F", "Country" : "U.K." }

{ "Age" : 15, "Gender" : "F", "Country" : "U.K." }

{ "Age" : 30, "Gender" : "F", "Country" : "U.K." }

{ "Age" : 40, "Gender" : "F", "Country" : "U.K." }

{ "Age" : 48, "Gender" : "F", "Country" : "U.K." }

{ "Age" : 49, "Gender" : "F", "Country" : "U.K." }

> it

no cursor

-- LESSER THAN OR EQUAL TO

db.users10.find({Age:{$lte:50}},{Age:1,Gender:1,Country:1,_id:0});

> db.users10.find({Age:{$lt:50}},{Age:1,Gender:1,Country:1,_id:0});

{ "Age" : 11, "Gender" : "F", "Country" : "U.K." }

{ "Age" : 12, "Gender" : "F", "Country" : "U.K." }

{ "Age" : 13, "Gender" : "F", "Country" : "U.K." }

{ "Age" : 14, "Gender" : "F", "Country" : "U.K." }

{ "Age" : 47, "Gender" : "F", "Country" : "U.K." }

{ "Age" : 48, "Gender" : "F", "Country" : "U.K." }

{ "Age" : 49, "Gender" : "F", "Country" : "U.K." }

{ "Age" : 50, "Gender" : "F", "Country" : "U.K." }

db.doccollect.find({likes:{$lte:80}}, {by:1,_id:0,});

> db.doccollect.find({likes:{$lte:80}}, {by:1,_id:0,});

{ "by" : "Ashok" }

> db.doccollect.find({likes:{$lte:100}}, {by:1,_id:0,});

{ "by" : "ashok" }

{ "by" : "Ashok" }

---######################### QUERY OPERATOR#################----------------

--- AND OPERATOR

> db.doccollect.find({by: 'Ashok',likes:100}, {by:1,title:1,_id:0,likes:1})

{ "title" : "casandra doc1", "by" : "Ashok", "likes" : 100 }

> db.doccollect.find({by: 'Ashok',likes:80}, {by:1,title:1,_id:0,likes:1})

{ "title" : "MongoDB doc10", "by" : "Ashok", "likes" : 80 }

db.createCollection("inventory");

db.inventory.insert(

{"item" : "abc1", description: "product 1", qty: 300 }

{"item" : "abc2", description: "product 2", qty: 200 }

{"item" : "xyz1", description: "product 3", qty: 250 }

{"item" : "VWZ1", description: "product 4", qty: 300 }

{"item" : "VWZ2", description: "product 5", qty: 180 }

--################################################################################################

-- Create inventory collection

--################################################################################################

db.inventory.insert({ item: "canvas", qty: 100, tags: ["cotton"], size: { h: 28, w: 35.5, uom: "cm" } });

> db.inventory.insert({ item: "kungfo", qty: 100, tags: ["lether"], size: { h:32, w: 25.5, uom: "mm" } });

> db.inventory.insert({ item: "slip-on shoe", qty: 100, tags: ["casual"], size: { h:30, w: 25.5, uom: "cm" } });

> db.inventory.insert([{ item: "slip-on shoe", qty: 100, tags: ["casual"], size: { h:30, w: 25.5, uom: "cm" } },

... { item: "Jelly shoes", qty:50, tags: ["casual"], size: { h:28, w: 25.5, uom: "mm"}}]);

> db.inventory.find({qty:100, item:'canvas'});

{ "_id" : ObjectId("5b4d8202a45df39b8bfc6a68"), "item" : "canvas", "qty" : 100, "tags" : [ "cotton" ], "size" : { "h" : 28, "w" : 35.5, "uom" : "cm" } }

> db.inventory.find({qty:100, item:'canvas'},{qty:1,item:1,_id:0});

{ "item" : "canvas", "qty" : 100 }

-- CREATE student collection in ashokdb database

-- insert below documents in collection

db.student.insert(

[

{

"f_name" : "Zenny",

"sex" : "Female",

"class" : "VI",

"age" : 12,

"grd_point" : 32.6342

{

"f_name" : "Paul",

"sex" : "Male",

"class" : "VII",

"age" : 13,

"grd_point" : 29.5904

{

"f_name" : "Tom",

"sex" : "Male",

"class" : "VI",

"age" : 11,

"grd_point" : 30.1257

{

"f_name" : "Lassy",

"sex" : "Female",

"class" : "VIII",

"age" : 13,

"grd_point" : 28.2514

{

"f_name" : "Peter",

"sex" : "Male",

"class" : "VI",

"age" : 11,

"grd_point" : 31.5201

}

]

);

--################################################################################################

1) how to join the collection;

2)how to merge two collection

3) how to join the two documents by using AND operators

--################################################################################################

db.student.find({$and:[{"sex":"Male"},{"grd_point":{ $gte: 31 }},{"class":"VI"}]}).pretty();

> db.student.find();

{ "_id" : ObjectId("5b53228f6639d8ab61a1327e"), "f_name" : "Zenny", "sex" : "Female", "class" : "VI", "age" : 12, "grd_point" : 32.6342 }

{ "_id" : ObjectId("5b53228f6639d8ab61a1327f"), "f_name" : "Paul", "sex" : "Male", "class" : "VII", "age" : 13, "grd_point" : 29.5904 }

{ "_id" : ObjectId("5b53228f6639d8ab61a13280"), "f_name" : "Tom", "sex" : "Male", "class" : "VI", "age" : 11, "grd_point" : 30.1257 }

{ "_id" : ObjectId("5b53228f6639d8ab61a13281"), "f_name" : "Lassy", "sex" : "Female", "class" : "VIII", "age" : 13, "grd_point" : 28.2514 }

{ "_id" : ObjectId("5b53228f6639d8ab61a13282"), "f_name" : "Peter", "sex" : "Male", "class" : "VI", "age" : 11, "grd_point" : 31.5201 }

-- In the below condition describe as sex=male and grade_point < 31 and class=VI

> db.student.find({$and:[{"sex":"Male"},{"grd_point":{ $gte: 31 }},{"class":"VI"}]}).pretty();

{

"_id" : ObjectId("5b53228f6639d8ab61a13282"),

"f_name" : "Peter",

"sex" : "Male",

"class" : "VI",

"age" : 11,

"grd_point" : 31.5201

}

> db.student.find({$and:[{"sex":"Male"},{"grd_point":{ $gte: 31 }},{"class":"VI"}]});

{ "_id" : ObjectId("5b53228f6639d8ab61a13282"), "f_name" : "Peter", "sex" : "Male", "class" : "VI", "age" : 11, "grd_point" : 31.5201 }

/// Example: SQL LIKE SELECT * FROM student WHERE sex="Male" AND grd_point>=31 AND class="VI"; ///

-- TO VIEW THE DOCUMENT: OR operator

-- first see all records

> db.doccollect.find();

{ "_id" : ObjectId("5b4ac2d81df2d6fb5e3de700"), "title" : "casandra doc1", "description" : "NoSQLDB", "by" : "Ashok", "tags" : [ "NoSQL", "DocDB", "NotOnlySQL" ], "likes" : 100 }

{ "_id" : ObjectId("5b4acd8b1df2d6fb5e3de78d"), "title" : "MongoDB doc10", "description" : "MongoDB document 10", "by" : "Ashok", "tags" : [ "MongoDB", "NoSQLDB", "DocDB", "database" ], "likes" : 80 }

{ "_id" : ObjectId("5b4ad2851df2d6fb5e3de790"), "title" : "Artificial Inteligence", "by" : "Siva" }

> db.doccollect.find({$or:[{by: "Siva"},{likes:80}]},{by:1,_id:0,title:1,description:1,likes:1});

{ "title" : "MongoDB doc10", "description" : "MongoDB document 10", "by" : "Ashok", "likes" : 80 }

{ "title" : "Artificial Inteligence", "by" : "Siva" }

-- @@@@@@@@@@@@@@@@@@ OR operator and Greater Than @@@@@@@@@@@@@@@@@@@

> db.doccollect.find();

{ "_id" : ObjectId("5b4ac2d81df2d6fb5e3de700"), "title" : "casandra doc1", "description" : "NoSQLDB", "by" : "Ashok", "tags" : [ "NoSQL", "DocDB", "NotOnlySQL" ], "likes" : 100 }

{ "_id" : ObjectId("5b4ad2851df2d6fb5e3de790"), "title" : "Artificial Inteligence", "by" : "Siva" }

db.doccollect.insert( {"title" : "MongoDB doc50", "description" : "PostgresQL DB", "by" : "Kamal",

"tags" : [ "Postgresql", "RdbmsSQLDB", "DocDB", "database" ], "likes" : 80})

{

> db.doccollect.find({$or:[{by:"Kamal"},{likes:{$gt:100}}]},{by:1,title:1,_id:0,likes:1});

{ "title" : "MongoDB doc50", "by" : "Kamal", "likes" : 80 }

> db.doccollect.find({$or:[{by:'ashok'},{likes:{$gt:80}}]},{by:1,title:1,_id:0,likes:1});

{ "title" : "MongoDB doc100", "by" : "ashok", "likes" : 100 }

{ "title" : "casandra doc1", "by" : "Ashok", "likes" : 100 }

> db.doccollect.find({$or:[{by:'ashok'},{likes:{$gt:100}}]},{by:1,title:1,_id:0,likes:1});

{ "title" : "MongoDB doc100", "by" : "ashok", "likes" : 100 }

> db.doccollect.find({$or:[{by:'Ashok'},{likes:{$gt:80}}]},{by:1,title:1,_id:0,likes:1});

{ "title" : "MongoDB doc100", "by" : "ashok", "likes" : 100 }

{ "title" : "casandra doc1", "by" : "Ashok", "likes" : 100 }

{ "title" : "MongoDB doc10", "by" : "Ashok", "likes" : 80 } ------->>> here by:Ashok is matching

-- @@@@@@@@@@@@@@@@@@ NOT OR operator and Greater Than @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

> db.doccollect.find({$nor:[{by:"Kamal"},{likes:{$gt:100}}]},{by:1,title:1,_id:0,likes:1});

{ "title" : "MongoDB doc100", "by" : "ashok", "likes" : 100 }

{ "title" : "casandra doc1", "by" : "Ashok", "likes" : 100 }

{ "title" : "MongoDB doc10", "by" : "Ashok", "likes" : 80 }

{ "title" : "Artificial Inteligence", "by" : "Siva" }

> db.doccollect.find({$nor:[{by:"ashok"},{likes:{$gt:100}}]},{by:1,title:1,_id:0,likes:1});

{ "title" : "casandra doc1", "by" : "Ashok", "likes" : 100 }

{ "title" : "MongoDB doc10", "by" : "Ashok", "likes" : 80 }

{ "title" : "Artificial Inteligence", "by" : "Siva" }

{ "title" : "MongoDB doc50", "by" : "Kamal", "likes" : 80 }

> db.doccollect.find({$nor:[{by:'Ashok'},{likes:{$gt:80}}]},{by:1,title:1,_id:0,likes:1});

{ "title" : "Artificial Inteligence", "by" : "Siva" }

{ "title" : "MongoDB doc50", "by" : "Kamal", "likes" : 80 } ---->>> here not equal to (by:ashok) is matching

-- @@@@@@@@@@@@@@@@@@ AND and OR OPERATOR @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

> db.doccollect.find({by:'Ashok',$or:[{title:' MongoDB doc100'}, {likes:{$gt:80}}]},{by:1,title:1,_id:0,likes:1})

{ "title" : "casandra doc1", "by" : "Ashok", "likes" : 100 }

> db.doccollect.find({by:'Ashok',$or:[{title:' MongoDB doc100'}, {likes:{$gt:100}}]},{by:1,title:1,_id:0,likes:1})

> db.doccollect.find({by:'Kamal',$or:[{title:' MongoDB doc100'}, {likes:{$gt:50}}]},{by:1,title:1,_id:0,likes:1})

{ "title" : "MongoDB doc50", "by" : "Kamal", "likes" : 80 }

db.doccollect.insert( {"title" : "MongoDB DOC 120", "description" : "SAP HANA db", "by" : "Ankur",

"tags" : [ "SAP-Hana", "RdbmsSQLDB", "binary", "database" ], "likes" : 120})

> db.doccollect.find();

{ "_id" : ObjectId("5b4ac2d81df2d6fb5e3de700"), "title" : "casandra doc1", "description" : "NoSQLDB", "by" : "Ashok", "tags" : [ "NoSQL", "DocDB", "NotOnlySQL" ], "likes" : 100 }

{ "_id" : ObjectId("5b4ad2851df2d6fb5e3de790"), "title" : "Artificial Inteligence", "by" : "Siva" }

{ "_id" : ObjectId("5b534dacb1e34bc0841af025"), "title" : "MongoDB doc50", "description" : "PostgresQL DB", "by" : "Kamal", "tags" : [ "Postgresql", "RdbmsSQLDB", "DocDB", "database" ], "likes" : 80 }

{ "_id" : ObjectId("5b5353c9ec36bd9344ade14e"), "title" : "MongoDB DOC 120", "description" : "SAP HANA db", "by" : "Ankur", "tags" : [ "SAP-Hana", "RdbmsSQLDB", "binary", "database" ], "likes" : 120 }

--------------------------- AND and OR OPERATOR -------------------------------

> db.users10.find({"Age":30, $or:[{"Name" : "Test User10"},{Age:{$gt:10}}]},{_id:0,Name:1,Age:1,Gender:1,Country:1});

{ "Name" : " Test User20", "Age" : 30, "Gender" : "F", "Country" : "U.K." }

{ "Name" : " Test User20", "Age" : 30, "Gender" : "F", "Country" : "U.K." } ---> we have 2 documents in users10 collections

>> db.users10.find({"Age":30, $or:[{"Name" : "Test User10"},{Age:{$gt:30}}]},{_id:0,Name:1,Age:1,Gender:1,Country:1});

> db.users10.find({"Age":30, $or:[{"Name" : "Test User10"},{Age:{$gt:20}}]},{_id:0,Name:1,Age:1,Gender:1,Country:1});

{ "Name" : " Test User20", "Age" : 30, "Gender" : "F", "Country" : "U.K." }

> db.users10.find({"Age":30, $or:[{"Name" : "Test User10"},{Age:{$gt:10}}]},{_id:0,Name:1,Age:1,Gender:1,Country:1});

{ "Name" : " Test User20", "Age" : 30, "Gender" : "F", "Country" : "U.K." }

> db.users10.find({"Age":30, $or:[{"Name" : "Test User40"},{Age:{$gt:40}}]},{_id:0,Name:1,Age:1,Gender:1,Country:1});

> db.users10.find({"Age":30, $or:[{"Name" : "Test User31"},{Age:{$gt:30}}]},{_id:0,Name:1,Age:1,Gender:1,Country:1});

> db.users10.find({"Age":30, $or:[{"Name" : "Test User12"},{Age:{$gt:10}}]},{_id:0,Name:1,Age:1,Gender:1,Country:1});

{ "Name" : " Test User20", "Age" : 30, "Gender" : "F", "Country" : "U.K." }

------ BETWEEN

> db.doccollect.find({likes:{$gt:50,$lte:100}},{_id:0,title:1,by:1,likes:1});

{ "title" : "MongoDB doc100", "by" : "ashok", "likes" : 100 }

{ "title" : "casandra doc1", "by" : "Ashok", "likes" : 100 }

{ "title" : "MongoDB doc10", "by" : "Ashok", "likes" : 80 }

{ "title" : "MongoDB doc50", "by" : "Kamal", "likes" : 80 }

> db.doccollect.find({likes:{$gt:80,$lte:120}},{_id:0,title:1,by:1,likes:1});

{ "title" : "MongoDB doc100", "by" : "ashok", "likes" : 100 }

{ "title" : "casandra doc1", "by" : "Ashok", "likes" : 100 }

{ "title" : "MongoDB DOC 120", "by" : "Ankur", "likes" : 120 }

> db.doccollect.find({likes:{$gt:50,$lt:120}},{_id:0,title:1,by:1,likes:1});

{ "title" : "MongoDB doc100", "by" : "ashok", "likes" : 100 }

{ "title" : "casandra doc1", "by" : "Ashok", "likes" : 100 }

{ "title" : "MongoDB doc10", "by" : "Ashok", "likes" : 80 }

{ "title" : "MongoDB doc50", "by" : "Kamal", "likes" : 80 }

> db.doccollect.find({likes:{$gt:80,$lt:120}},{_id:0,title:1,by:1,likes:1});

{ "title" : "MongoDB doc100", "by" : "ashok", "likes" : 100 }

{ "title" : "casandra doc1", "by" : "Ashok", "likes" : 100 }

--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ IN OPERATOR @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

> db.doccollect.find({likes:{$in:[50,80,100]}},{_id:0,title:1,by:1,likes:1});

{ "title" : "MongoDB doc100", "by" : "ashok", "likes" : 100 }

{ "title" : "casandra doc1", "by" : "Ashok", "likes" : 100 }

{ "title" : "MongoDB doc10", "by" : "Ashok", "likes" : 80 }

{ "title" : "MongoDB doc50", "by" : "Kamal", "likes" : 80 }

> db.doccollect.find({likes:{$in:[80,100]}},{_id:0,title:1,by:1,likes:1});

{ "title" : "MongoDB doc100", "by" : "ashok", "likes" : 100 }

{ "title" : "casandra doc1", "by" : "Ashok", "likes" : 100 }

{ "title" : "MongoDB doc10", "by" : "Ashok", "likes" : 80 }

{ "title" : "MongoDB doc50", "by" : "Kamal", "likes" : 80 }

--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ NOT IN @@@@@@@@@@@@@@@@@@@@@@@@@@@@@

> db.doccollect.find({likes:{$nin:[80,100]}},{_id:0,title:1,by:1,likes:1});

{ "title" : "Artificial Inteligence", "by" : "Siva" }

{ "title" : "MongoDB DOC 120", "by" : "Ankur", "likes" : 120 }

> db.doccollect.find({likes:{$nin:[80]}},{_id:0,title:1,by:1,likes:1});

{ "title" : "MongoDB doc100", "by" : "ashok", "likes" : 100 }

{ "title" : "casandra doc1", "by" : "Ashok", "likes" : 100 }

{ "title" : "Artificial Inteligence", "by" : "Siva" }

{ "title" : "MongoDB DOC 120", "by" : "Ankur", "likes" : 120 }

--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ NULL @@@@@@@@@@@@@@@@@@@@@@@@@@@@@

> db.doccollect.find({likes:null},{_id:0,title:1,by:1,likes:1});

{ "title" : "Artificial Inteligence", "by" : "Siva" }

> db.users10.find({Country:{$ne:null}},{_id:0,Name:1,Age:1,Gender:1});

{ "Name" : " Test User1", "Age" : 11, "Gender" : "F" }

{ "Name" : " Test User2", "Age" : 12, "Gender" : "F" }

{ "Name" : " Test User3", "Age" : 13, "Gender" : "F" }

{ "Name" : " Test User4", "Age" : 14, "Gender" : "F" }

{ "Name" : " Test User5", "Age" : 15, "Gender" : "F" }

{ "Name" : " Test User6", "Age" : 16, "Gender" : "F" }

{ "Name" : " Test User7", "Age" : 17, "Gender" : "F" }

{ "Name" : " Test User8", "Age" : 18, "Gender" : "F" }

{ "Name" : " Test User9", "Age" : 19, "Gender" : "F" }

{ "Name" : " Test User10", "Age" : 20, "Gender" : "F" }

{ "Name" : " Test User11", "Age" : 21, "Gender" : "F" }

{ "Name" : " Test User12", "Age" : 22, "Gender" : "F" }

{ "Name" : " Test User13", "Age" : 23, "Gender" : "F" }

{ "Name" : " Test User14", "Age" : 24, "Gender" : "F" }

{ "Name" : " Test User15", "Age" : 25, "Gender" : "F" }

{ "Name" : " Test User16", "Age" : 26, "Gender" : "F" }

{ "Name" : " Test User17", "Age" : 27, "Gender" : "F" }

{ "Name" : " Test User18", "Age" : 28, "Gender" : "F" }

{ "Name" : " Test User19", "Age" : 29, "Gender" : "F" }

{ "Name" : " Test User20", "Age" : 30, "Gender" : "F" }

--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ NOT NULL @@@@@@@@@@@@@@@@@@@@@@@@@@@@@

> db.doccollect.find({likes:{$ne:null}},{_id:0,title:1,by:1,likes:1});

{ "title" : "MongoDB doc100", "by" : "ashok", "likes" : 100 }

{ "title" : "casandra doc1", "by" : "Ashok", "likes" : 100 }

{ "title" : "MongoDB doc10", "by" : "Ashok", "likes" : 80 }

{ "title" : "MongoDB doc50", "by" : "Kamal", "likes" : 80 }

{ "title" : "MongoDB DOC 120", "by" : "Ankur", "likes" : 120 }

> db.doccollect.find({likes:{$exists:true}},{_id:0,title:1,by:1,likes:1});

{ "title" : "MongoDB doc100", "by" : "ashok", "likes" : 100 }

{ "title" : "casandra doc1", "by" : "Ashok", "likes" : 100 }

{ "title" : "MongoDB doc10", "by" : "Ashok", "likes" : 80 }

{ "title" : "MongoDB doc50", "by" : "Kamal", "likes" : 80 }

{ "title" : "MongoDB DOC 120", "by" : "Ankur", "likes" : 120 }

--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ SORTING DOCUMENTS @@@@@@@@@@@@@@@@@@@@@@@@@@@@@

--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ Sorting a document in Ascending Order condition @@@@@@@@@@@@@@@@@@@@@@@@@@@@@

> db.doccollect.find({likes:{$gt:80}}, {by:1,title:1,_id:0,likes:1}).sort({likes:1});

{ "title" : "MongoDB doc100", "by" : "ashok", "likes" : 100 }

{ "title" : "casandra doc1", "by" : "Ashok", "likes" : 100 }

{ "title" : "MongoDB DOC 120", "by" : "Ankur", "likes" : 120 }

> db.doccollect.find({likes:{$lt:100}},

{by:1,title:1,_id:0,likes:1}).sort({likes:1});

{ "title" : "MongoDB doc10", "by" : "Ashok", "likes" : 80 }

{ "title" : "MongoDB doc50", "by" : "Kamal", "likes" : 80 }

> db.doccollect.find({likes:{$lt:120}}, {by:1,title:1,_id:0,likes:1}).sort({likes:1});

{ "title" : "MongoDB doc10", "by" : "Ashok", "likes" : 80 }

{ "title" : "MongoDB doc50", "by" : "Kamal", "likes" : 80 }

{ "title" : "MongoDB doc100", "by" : "ashok", "likes" : 100 }

{ "title" : "casandra doc1", "by" : "Ashok", "likes" : 100 }

--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ Sorting a document in Descending order condition @@@@@@@@@@@@@@@@@@@@@@@@@@@@@

> db.doccollect.find({likes:{$lt:120}}, {by:1,title:1,_id:0,likes:1}).sort({likes:-1});

{ "title" : "MongoDB doc100", "by" : "ashok", "likes" : 100 }

{ "title" : "casandra doc1", "by" : "Ashok", "likes" : 100 }

{ "title" : "MongoDB doc10", "by" : "Ashok", "likes" : 80 }

{ "title" : "MongoDB doc50", "by" : "Kamal", "likes" : 80 }

> db.doccollect.find({likes:{$lt:100}}, {by:1,title:1,_id:0,likes:1}).sort({likes:-1});

{ "title" : "MongoDB doc10", "by" : "Ashok", "likes" : 80 }

{ "title" : "MongoDB doc50", "by" : "Kamal", "likes" : 80 }

--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ SORT A DOCUMENT BASED ON MULTIPLE COLUMNS @@@@@@@@@@@@@@@@@@@@@@@@@@@@@

> db.doccollect.find({likes:{$lt:120}}, {by:1,title:1,_id:0,likes:1}).sort({by:1,likes:1});

{ "title" : "MongoDB doc10", "by" : "Ashok", "likes" : 80 }

{ "title" : "casandra doc1", "by" : "Ashok", "likes" : 100 }

{ "title" : "MongoDB doc50", "by" : "Kamal", "likes" : 80 }

{ "title" : "MongoDB doc100", "by" : "ashok", "likes" : 100 }

> db.doccollect.find({likes:{$lt:120}}, {by:1,title:1,_id:0,likes:1}).sort({by:-1,likes:1});

{ "title" : "MongoDB doc100", "by" : "ashok", "likes" : 100 }

{ "title" : "MongoDB doc50", "by" : "Kamal", "likes" : 80 }

{ "title" : "MongoDB doc10", "by" : "Ashok", "likes" : 80 }

{ "title" : "casandra doc1", "by" : "Ashok", "likes" : 100 }

> db.doccollect.find({likes:{$lt:120}}, {by:1,title:1,_id:0,likes:1}).sort({by:-1,likes:-1});

{ "title" : "MongoDB doc100", "by" : "ashok", "likes" : 100 }

{ "title" : "MongoDB doc50", "by" : "Kamal", "likes" : 80 }

{ "title" : "casandra doc1", "by" : "Ashok", "likes" : 100 }

{ "title" : "MongoDB doc10", "by" : "Ashok", "likes" : 80 }

> db.doccollect.find({likes:{$lt:120}}, {by:1,title:1,_id:0,likes:1}).sort({by:1,likes:-1});

{ "title" : "casandra doc1", "by" : "Ashok", "likes" : 100 }

{ "title" : "MongoDB doc10", "by" : "Ashok", "likes" : 80 }

{ "title" : "MongoDB doc50", "by" : "Kamal", "likes" : 80 }

{ "title" : "MongoDB doc100", "by" : "ashok", "likes" : 100 }

----------------------------------------------------------------------------------------------------------------------------------------

> db.student.find();

{ "_id" : ObjectId("5b53228f6639d8ab61a1327e"), "f_name" : "Zenny", "sex" : "Female", "class" : "VI", "age" : 12, "grd_point" : 32.6342 }

{ "_id" : ObjectId("5b53228f6639d8ab61a1327f"), "f_name" : "Paul", "sex" : "Male", "class" : "VII", "age" : 13, "grd_point" : 29.5904 }

{ "_id" : ObjectId("5b53228f6639d8ab61a13280"), "f_name" : "Tom", "sex" : "Male", "class" : "VI", "age" : 11, "grd_point" : 30.1257 }

{ "_id" : ObjectId("5b53228f6639d8ab61a13281"), "f_name" : "Lassy", "sex" : "Female", "class" : "VIII", "age" : 13, "grd_point" : 28.2514 }

{ "_id" : ObjectId("5b53228f6639d8ab61a13282"), "f_name" : "Peter", "sex" : "Male", "class" : "VI", "age" : 11, "grd_point" : 31.5201 }

> db.student.find({"age":{$lt:12}},{_id:0,f_name:1,sex:1,age:1,class:1});

{ "f_name" : "Tom", "sex" : "Male", "class" : "VI", "age" : 11 }

{ "f_name" : "Peter", "sex" : "Male", "class" : "VI", "age" : 11 }

> db.student.find({"age":{$lt:13}},

{_id:0,f_name:1,sex:1,age:1,class:1}).sort({grd_point:1});

{ "f_name" : "Tom", "sex" : "Male", "class" : "VI", "age" : 11 }

{ "f_name" : "Peter", "sex" : "Male", "class" : "VI", "age" : 11 }

{ "f_name" : "Zenny", "sex" : "Female", "class" : "VI", "age" : 12 }

> db.student.find({"age":{$lt:13}},{_id:0,f_name:1,sex:1,age:1,class:1,grd_point:1}).sort({grd_point:1});

{ "f_name" : "Tom", "sex" : "Male", "class" : "VI", "age" : 11, "grd_point" : 30.1257 }

{ "f_name" : "Peter", "sex" : "Male", "class" : "VI", "age" : 11, "grd_point" : 31.5201 }

{ "f_name" : "Zenny", "sex" : "Female", "class" : "VI", "age" : 12, "grd_point" : 32.6342 }

> db.student.find({"age":{$lt:12}},{_id:0,f_name:1,sex:1,age:1,class:1,grd_point:1}).sort({grd_point:1});

{ "f_name" : "Tom", "sex" : "Male", "class" : "VI", "age" : 11, "grd_point" : 30.1257 }

{ "f_name" : "Peter", "sex" : "Male", "class" : "VI", "age" : 11, "grd_point" : 31.5201 }

--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ LIMITING AND SKIPPING THE DOCUMENTS @@@@@@@@@@@@@@@@@@@@@@@@@@@@@

--@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ VIEW LIMITED NUMBER OF DOCUMENTS @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

--WITHOUT SKIPPING

> db.student.find({"age":{$lt:13}},{_id:0,f_name:1,sex:1,age:1,class:1,grd_point:1}).sort({grd_point:1}).limit(2);

{ "f_name" : "Tom", "sex" : "Male", "class" : "VI", "age" : 11, "grd_point" : 30.1257 }

{ "f_name" : "Peter", "sex" : "Male", "class" : "VI", "age" : 11, "grd_point" : 31.5201 }

> db.doccollect.find({likes:{$lt:120}}, {by:1,title:1,_id:0,likes:1}).sort({by:1,likes:1}).limit(2);

{ "title" : "MongoDB doc10", "by" : "Ashok", "likes" : 80 }

{ "title" : "casandra doc1", "by" : "Ashok", "likes" : 100 }

> db.doccollect.find({likes:{$lt:120}}, {by:1,title:1,_id:0,likes:1}).sort({by:1,likes:1}).limit(4);

{ "title" : "MongoDB doc10", "by" : "Ashok", "likes" : 80 }

{ "title" : "casandra doc1", "by" : "Ashok", "likes" : 100 }

{ "title" : "MongoDB doc50", "by" : "Kamal", "likes" : 80 }

{ "title" : "MongoDB doc100", "by" : "ashok", "likes" : 100 }

-- LIMIT

> db.doccollect.find({likes:{$lt:120}}, {by:1,title:1,_id:0,likes:1}).sort({by:-1,likes:-1}).limit(5);

{ "title" : "MongoDB doc100", "by" : "ashok", "likes" : 100 }

{ "title" : "MongoDB doc50", "by" : "Kamal", "likes" : 80 }

{ "title" : "casandra doc1", "by" : "Ashok", "likes" : 100 }

-- LIMIT & SKIP condition

> db.doccollect.find({likes:{$lt:120}}, {by:1,title:1,_id:0,likes:1}).sort({by:-1,likes:-1}).limit(5).skip(0);

{ "title" : "MongoDB doc100", "by" : "ashok", "likes" : 100 }

{ "title" : "MongoDB doc50", "by" : "Kamal", "likes" : 80 }

{ "title" : "casandra doc1", "by" : "Ashok", "likes" : 100 }

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@

TO SORT AND VIEW LIMITED NUMBER OF DOCUMENTS AND SKIPPING @@@@@@@@@@@@@@@@@@@@@@@@@@@@@

> db.doccollect.find({likes:{$lt:100}}, {by:1,title:1,_id:0,likes:1}).sort({likes:1}).limit(3).skip(0);

{ "title" : "MongoDB doc10", "by" : "Ashok", "likes" : 80 }

{ "title" : "MongoDB doc50", "by" : "Kamal", "likes" : 80 }

> db.doccollect.find({likes:{$lt:100}},

{by:1,title:1,_id:0,likes:1}).sort({likes:1}).limit(3).skip(1);---> from the output

{ "title" : "MongoDB doc50", "by" : "Kamal", "likes" : 80 }

> db.doccollect.find({likes:{$lt:120}}, {by:1,title:1,_id:0,likes:1}).sort({likes:1}).limit(3).skip(1);

{ "title" : "MongoDB doc50", "by" : "Kamal", "likes" : 80 }

{ "title" : "MongoDB doc100", "by" : "ashok", "likes" : 100 }

> db.doccollect.find({likes:{$lt:120}}, {by:1,title:1,_id:0,likes:1}).sort({likes:1}).limit(3).skip(2);

{ "title" : "MongoDB doc100", "by" : "ashok", "likes" : 100 }

DBA TO CLOUD AND BIGDATA SOLUTION

Sunday, September 30, 2018

MONGODB Aggregation Operations

Step by Step YugabyteDB 2.11 (Open Source) Distributed DB - Multi-node Cluster Setup on RHEL

BLOCKROLL

Report Abuse