Sunday, 18 December 2016

Database manipulation in MongoDB

Database manipulation in MongoDB

Basic database queries for MongoDB
The remove() Method
Syntax: >db.COLLECTION_NAME.remove(DELLETION_CRITTERIA)
Example:>db.Employee.remove({'title':'MongoDB Overview'});
Remove Only One
Syntax: >db.COLLECTION_NAME.remove(DELETION_CRITERIA,1)

And/OR Condition
>db.Employee.find({$and:[{"by":"Sudhir"},{"title": "Testing MongoDB Database"}]})
>db.Employee.find({$or:[{"by":"Sudhir"},{"title": "Testing MongoDB Database"}]})

Using AND and OR Together
>db.mycol.find({"likes": {$gt:10}, $or: [{"by": "Sudhir"},{"title": "Testing MongoDB Database"}]})

The save() method replaces the existing document with the new document passed in the save() method.
Syntax: >db.COLLECTION_NAME.save({_id:ObjectId(),NEW_DATA})
Example: >db.mycol.save(
{
"_id" : ObjectId(5983548781331adf45ec7), "title":"Tutorials Point New Topic",
"by":"Tutorials Point"});

The aggregate() Method
For the aggregation in MongoDB, you should use aggregate() method.
Syntax
Basic syntax of aggregate() method is as follows −
>db.COLLECTION_NAME.aggregate(AGGREGATE_OPERATION)
Example: > db.mycol.aggregate([{$group : {_id : "$by_user", num_tutorial : {$sum : 1}}}])
Predefine aggregate function
$sum : Perform sum operation
Example: db.mycol.aggregate([{$group : {_id : "$by_user", num_tutorial : {$sum : "$likes"}}}])

$avg : Calculates the average operation
Example: db.mycol.aggregate([{$group : {_id : "$by_user", num_tutorial : {$avg : "$likes"}}}])

$min : Returns minimum value
Example: db.mycol.aggregate([{$group : {_id : "$by_user", num_tutorial : {$min : "$likes"}}}])

$max : Returns maximum value
Example: db.mycol.aggregate([{$group : {_id : "$by_user", num_tutorial : {$max : "$likes"}}}])

$push : Perform insertation in array
Example: db.mycol.aggregate([{$group : {_id : "$by_user", url : {$push: "$url"}}}])

$addToSet: Inserts the value to an array in the resulting document but does not create duplicates.
Example: db.mycol.aggregate([{$group : {_id : "$by_user", url : {$addToSet : "$url"}}}])


Dump MongoDB Data
>mongodump

Modeling Embedded Relationships
Example:
{
"_id":ObjectId("52ffc33cd85242f436000001"),
"contact": "987654321",
"dob": "01-01-1991",
"name": "Tom Benzamin",
"address": [
{
"building": "22 A, Indiana Apt",
"pincode": 123456,
"city": "Los Angeles",
"state": "California"
},
{
"building": "170 A, Acropolis Apt",
"pincode": 456789,
"city": "Chicago",
"state": "Illinois"
}
]
}

Modeling Referenced Relationships
{
"_id":ObjectId("52ffc33cd85242f436000001"),
"contact": "987654321",
"dob": "01-01-1991",
"name": "Tom Benzamin",
"address_ids": [
ObjectId("52ffc4a5d85242602e000000"),
ObjectId("52ffc4a5d85242602e000001")
]
}
For fetching records in referenced relationships you have to run two queries
>var result = db.users.findOne({"name":"Sudhir Pandey"},{"address_ids":1})
>var addresses = db.address.find({"_id":{"$in":result["address_ids"]}})

Joining in MongoDB
One to Many Join
For 1:1 relation we can use the $lookup aggregator

$lookup aggregator syntax:
db.leftCollection.aggregate([ { $lookup: {from: "rightCollection", localField: "leftVal", foreignField: "rightVal", as: "embeded Data"}}])

Now Create CountryCode Collection
db.countryCode.insert([{code: 1}, {code: 20}, {code: 30}])

Next, create a lookup table pairing the country codes to country names:
db.country.insert([{code: 1, name: "United States"}, {code: 20, name: "Egypt"}, {code: 30, name: "Greece"}])

Now we can query join the two collections with the $lookup operator.
db.countryCode.aggregate([
{ $lookup: {from: "country", localField: "code", foreignField: "code", as: "countryName"} },
{ $project: {"code":1, "countryName.name":1, "_id":0} }
])

One to One Join
For 1:1 relation we can use the $unwind aggregator
db.countryCode.aggregate([
{ $lookup: {from: "country", localField: "code", foreignField: "code", as: "countryName"} },
{ $unwind: "$countryName"},
{ $project: {"code":1, "countryName.name":1, "_id":0} }
])

Nested Lookups

db.phone.insert([{number: "555-1212", countryCode: 1}, {number: "851-1234", countryCode: 20}])
db.phone.aggregate([
{ $lookup: {from: "countryCode", localField: "countryCode", foreignField: "code", as: "countryCode"} },
{ $unwind: "$countryCode"},
{ $lookup: {from: "countryCodeLookup", localField: "countryCode.code", foreignField: "code", as: "country"} }
])

Code Explaination:
$unwind :
Deconstructs an array field from the input documents to output a document for each element. Each output document is the input document with the value of the array field replaced by the element.
Example: inventory document { "_id" : 1, "item" : "ABC1", sizes: [ "S", "M", "L"] }
db.inventory.aggregate( [ { $unwind : "$sizes" } ] )
Output:
{ "_id" : 1, "item" : "ABC1", "sizes" : "S" }
{ "_id" : 1, "item" : "ABC1", "sizes" : "M" }
{ "_id" : 1, "item" : "ABC1", "sizes" : "L" }

$project : Used to select some specific fields from a collection.
Example: Book Collection {
"_id" : 1,
title: "abc123",
isbn: "0001122223334",
author: { last: "zzz", first: "aaa" },
copies: 5
}
db.books.aggregate( [ { $project : { title : 1 , author : 1 } } ] )
Output:
{ "_id" : 1, "title" : "abc123", "author" : { "last" : "zzz", "first" : "aaa" } }
Note:The _id field is always included by default. To exclude the _id field from the output documents of the $project stage, specify the exclusion of the _id field by setting it to 0 in the projection document.

i.e db.books.aggregate( [ { $project : { _id: 0, title : 1 , author : 1 } } ] )

0 comments:

Post a Comment