Tuesday, June 30, 2015

Sub Documents and Regex in MongoDB

Sub documents

> db.num.insert({x:10,"Name":{"FName":"tim","LName":"Southee"}})
WriteResult({ "nInserted" : 1 })

> db.num.find({'Name.FName':"tim"}).pretty()
{
        "_id" : ObjectId("55911994525a7a85c0902ff7"),
        "x" : 10,
        "Name" : {
                "FName" : "tim",
                "LName" : "Southee"
        }
}
>
> db.num.insert({x:10,"Name":{"FName":"tim","LName":"Southee"},"Qualif":["Bca","
Mca"]})
WriteResult({ "nInserted" : 1 })
> db.num.find({'Qualif':"Bca"}).pretty()
{
        "_id" : ObjectId("55911cf9525a7a85c0902ff8"),
        "x" : 10,
        "Name" : {
                "FName" : "tim",
                "LName" : "Southee"
        },
        "Qualif" : [
                "Bca",
                "Mca"
        ]
}
>

Regex
> db.Employee.find({"EmpName":{$regex:/^S/i}}).pretty()
{
        "_id" : ObjectId("55910c4f525a7a85c0902ff5"),
        "EmpName" : "Supreet",
        "Designation" : "Senior Software Eng"
}


Array

> db.myproj.insert({"Project":{"project1":["wbs1","wbs2"]}})
WriteResult({ "nInserted" : 1 })
> db.myproj.find({'Project.project1':"wbs1"}).pretty()
{
        "_id" : ObjectId("55911f67525a7a85c0902ffa"),
        "Project" : {
                "project1" : [
                        "wbs1",
                        "wbs2"
                ]
        }
}
> db.myproj.insert({"Project":{"project2":["wbs1","wbs2"]}})
WriteResult({ "nInserted" : 1 })
>
> db.myproj.find().pretty()
{
        "_id" : ObjectId("55911f17525a7a85c0902ff9"),
        "Project" : [
                "wbs1",
                "wbs2"
        ]
}
{
        "_id" : ObjectId("55911f67525a7a85c0902ffa"),
        "Project" : {
                "project1" : [
                        "wbs1",
                        "wbs2"
                ]
        }
}
{
        "_id" : ObjectId("55911fee525a7a85c0902ffb"),
        "Project" : {
                "project2" : [
                        "wbs1",
                        "wbs2"
                ]
        }
}
>

Orders collection : selecting from an array
> db.orders.insert({"memos":[{"memo":"on time","by":"payment"},{"memo":"delayed","by":"shipping"}]})
WriteResult({ "nInserted" : 1 })
> db.orders.find().pretty()
{
        "_id" : ObjectId("55912599525a7a85c0902ffc"),
        "memos" : [
                {
                        "memo" : "on time",
                        "by" : "shipping"
                },
                {
                        "memo" : "approved",
                        "by" : "billing"
                }
        ]
}
{
        "_id" : ObjectId("559125cb525a7a85c0902ffd"),
        "memos" : [
                {
                        "memo" : "on time",
                        "by" : "payment"
                },
                {
                        "memo" : "delayed",
                        "by" : "shipping"
                }
        ]
}
> db.orders.find({'memos.0.by':'shipping'}).pretty()
{
        "_id" : ObjectId("55912599525a7a85c0902ffc"),
        "memos" : [
                {
                        "memo" : "on time",
                        "by" : "shipping"
                },
                {
                        "memo" : "approved",
                        "by" : "billing"
                }
        ]
}
> db.orders.find({'memos.by':'shipping'}).pretty()
{
        "_id" : ObjectId("55912599525a7a85c0902ffc"),
        "memos" : [
                {
                        "memo" : "on time",
                        "by" : "shipping"
                },
                {
                        "memo" : "approved",
                        "by" : "billing"
                }
        ]
}
{
        "_id" : ObjectId("559125cb525a7a85c0902ffd"),
        "memos" : [
                {
                        "memo" : "on time",
                        "by" : "payment"
                },
                {
                        "memo" : "delayed",
                        "by" : "shipping"
                }
        ]
}


Posted by at No comments:
Labels: , ,

Helper Functions in MongoDB

> db.orders.find().count()
2
>
Sort({Fieldname:sort order})
> db.orders.find().sort({_id:-1}).pretty()
{
        "_id" : ObjectId("559125cb525a7a85c0902ffd"),
        "memos" : [
                {
                        "memo" : "on time",
                        "by" : "payment"
                },
                {
                        "memo" : "delayed",
                        "by" : "shipping"
                }
        ]
}
{
        "_id" : ObjectId("55912599525a7a85c0902ffc"),
        "memos" : [
                {
                        "memo" : "on time",
                        "by" : "shipping"
                },
                {
                        "memo" : "approved",
                        "by" : "billing"
                }
        ]
}
>


Limit(num)
> db.orders.find().limit(1).pretty()
{
        "_id" : ObjectId("55912599525a7a85c0902ffc"),
        "memos" : [
                {
                        "memo" : "on time",
                        "by" : "shipping"
                },
                {
                        "memo" : "approved",
                        "by" : "billing"
                }
        ]
}
>


Posted by at No comments:
Labels: ,

Update in MongoDB - More examples

Positional or field specific update
Use of $set ,$inc ,$unset operator
Updating multiple docs in single command ->{multi:1}
-by default {multi:0}
Upsert-> default is false


> db.numbers.insert({"x":10,"y":67})
WriteResult({ "nInserted" : 1 })
> db.numbers.insert({"x":10,"y":50})
WriteResult({ "nInserted" : 1 })
> db.numbers.insert({"x":10,"y":100})
WriteResult({ "nInserted" : 1 })
> db.numbers.find().pretty()
{ "_id" : ObjectId("559212cb8e94c66f57951518"), "x" : 10, "y" : 67 }
{ "_id" : ObjectId("559212d88e94c66f57951519"), "x" : 10, "y" : 50 }
{ "_id" : ObjectId("559212dd8e94c66f5795151a"), "x" : 10, "y" : 100 }


> db.numbers.update({"x":10}, {$set:{"y":120}},{multi:1} )
WriteResult({ "nMatched" : 3, "nUpserted" : 0, "nModified" : 3 })
> db.numbers.find().pretty()
{ "_id" : ObjectId("559212cb8e94c66f57951518"), "x" : 10, "y" : 120 }
{ "_id" : ObjectId("559212d88e94c66f57951519"), "x" : 10, "y" : 120 }
{ "_id" : ObjectId("559212dd8e94c66f5795151a"), "x" : 10, "y" : 120 }
>

Note : if you do not use $set , schema will change after you update
 Unset : to remove a column

> db.numbers.find().pretty()
{ "_id" : ObjectId("559212cb8e94c66f57951518"), "x" : 101, "y" : 202 }
{ "_id" : ObjectId("559212d88e94c66f57951519"), "x" : 10, "y" : 125 }
{ "_id" : ObjectId("559212dd8e94c66f5795151a"), "x" : 10, "y" : 125 }
{ "_id" : ObjectId("55921888ab28e968f573138f"), "x" : 101, "y" : 202 }

> db.numbers.update({"x":101}, {$unset:{"y":202}})
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })

> db.numbers.find().pretty()
{ "_id" : ObjectId("559212cb8e94c66f57951518"), "x" : 101 }
{ "_id" : ObjectId("559212d88e94c66f57951519"), "x" : 10, "y" : 125 }
{ "_id" : ObjectId("559212dd8e94c66f5795151a"), "x" : 10, "y" : 125 }
{ "_id" : ObjectId("55921888ab28e968f573138f"), "x" : 101, "y" : 202 }
>





  
Increment
> db.numbers.update({"x":10}, {$inc:{"y":5}},{multi:1} )
WriteResult({ "nMatched" : 3, "nUpserted" : 0, "nModified" : 3 })
> db.numbers.find().pretty()
{ "_id" : ObjectId("559212cb8e94c66f57951518"), "x" : 10, "y" : 125 }
{ "_id" : ObjectId("559212d88e94c66f57951519"), "x" : 10, "y" : 125 }
{ "_id" : ObjectId("559212dd8e94c66f5795151a"), "x" : 10, "y" : 125 }
>
 Upsert: to modify or insert a new document

> db.numbers.find().pretty()
{ "_id" : ObjectId("559212cb8e94c66f57951518"), "x" : 10, "y" : 125 }
{ "_id" : ObjectId("559212d88e94c66f57951519"), "x" : 10, "y" : 125 }
{ "_id" : ObjectId("559212dd8e94c66f5795151a"), "x" : 10, "y" : 125 }
> db.numbers.update({"x":10}, {$set:{"x":101,"y":202}},{upsert:1} )
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })

> db.numbers.find().pretty()
{ "_id" : ObjectId("559212cb8e94c66f57951518"), "x" : 101, "y" : 202 }
{ "_id" : ObjectId("559212d88e94c66f57951519"), "x" : 10, "y" : 125 }
{ "_id" : ObjectId("559212dd8e94c66f5795151a"), "x" : 10, "y" : 125 }

> db.numbers.update({"x":1}, {$set:{"x":101,"y":202}},{upsert:1} )
WriteResult({
        "nMatched" : 0,
        "nUpserted" : 1,
        "nModified" : 0,
        "_id" : ObjectId("55921888ab28e968f573138f")
})
>





Posted by at No comments:
Labels: ,

Array Operations in MongoDB



Update operators for arrays
$push:add a value
$pop:remove one value from collection from either right end of left end
$pushAll
$pull:remove element from any position
$pullAll:remove more than 1 irrespective of position
$addToSet:add value if its unique
$push+$each+$position:

>  db.array.insert({x:[1,2,3,4,5,6]})
WriteResult({ "nInserted" : 1 })
> db.array.find().pretty()
{
        "_id" : ObjectId("55921d5c8e94c66f5795151b"),
        "x" : [
                1,
                2,
                3,
                4,
                5,
                6
        ]
}

Push

> db.array.update({"x":1},{$push:{"x":7}})
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
> db.array.find().pretty()
{
        "_id" : ObjectId("55921d5c8e94c66f5795151b"),
        "x" : [
                1,
                2,
                3,
                4,
                5,
                6,
                7
        ]
}

Pull

> db.array.update({"x":1},{$pull:{"x":7}})
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
> db.array.find().pretty()
{
        "_id" : ObjectId("55921d5c8e94c66f5795151b"),
        "x" : [
                1,
                2,
                3,
                4,
                5,
                6
        ]
}
>
Pull All

> db.array.update({"x":1},{$pullAll:{"x":[1,2]}})
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
> db.array.find().pretty()
{
        "_id" : ObjectId("55921d5c8e94c66f5795151b"),
        "x" : [
                3,
                4,
                5,
                6
        ]
}
>

Pull with condition


> db.array.update({"x":3},{$pull:{"x":{$gt:5}}})
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
> db.array.find().pretty()
{ "_id" : ObjectId("55921d5c8e94c66f5795151b"), "x" : [ 3, 4, 5 ] }
>


POP

> db.array.update({"x":3},{$pop:{"x":-1}})
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })
>
> db.array.find().pretty()
{ "_id" : ObjectId("55921d5c8e94c66f5795151b"), "x" : [ 4, 5, 6 ] }
>


Add to set

> db.array.update({"x":3},{$addToSet:{"x":5}})
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 0 })

> db.array.find().pretty()
{ "_id" : ObjectId("55921d5c8e94c66f5795151b"), "x" : [ 3, 4, 5 ] }

> db.array.update({"x":3},{$addToSet:{"x":6}})
WriteResult({ "nMatched" : 1, "nUpserted" : 0, "nModified" : 1 })

> db.array.find().pretty()
{
        "_id" : ObjectId("55921d5c8e94c66f5795151b"),
        "x" : [
                3,
                4,
                5,
                6
        ]
}

Posted by at No comments:
Labels: ,

Indexes in MongoDB

Create Index
> db.Customer.ensureIndex({"CustCode":1})
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 1,
        "numIndexesAfter" : 2,
        "ok" : 1
}
> db.Customer.find()
> db.Customer.getIndexes()
[
        {
                "v" : 1,
                "key" : {
                        "_id" : 1
                },
                "name" : "_id_",
                "ns" : "testdb.Customer"
        },
        {
                "v" : 1,
                "key" : {
                        "CustCode" : 1
                },
                "name" : "CustCode_1",
                "ns" : "testdb.Customer"
        }
]
>
> db.Customer.createIndex({"CustCode":1},{unique:1})
{
        "createdCollectionAutomatically" : false,
        "numIndexesBefore" : 2,
        "numIndexesAfter" : 3,
        "ok" : 1
}
> db.Customer.getIndexes()
[
        {
                "v" : 1,
                "key" : {
                        "_id" : 1
                },
                "name" : "_id_",
                "ns" : "testdb.Customer"
        },
        {
                "v" : 1,
                "key" : {
                        "_id" : 1,
                        "CustCode" : 1
                },
                "name" : "_id_1_CustCode_1",
                "ns" : "testdb.Customer"
        },
        {
                "v" : 1,
                "unique" : true,
                "key" : {
                        "CustCode" : 1
                },
                "name" : "CustCode_1",
                "ns" : "testdb.Customer"
        }
]
> db.Customer.insert({"CustCode":2,"Name":"Customer2"})
WriteResult({
        "nInserted" : 0,
        "writeError" : {
                "code" : 11000,
                "errmsg" : "insertDocument :: caused by :: 11000 E11000 duplicate key error index: testdb.Customer
.$CustCode_1  dup key: { : 2.0 }"
        }
})
>

Posted by at No comments:
Labels: ,

Aggregation in Mongo Db



Aggregation Mappers
SQL
Mongo
Where
$match
GroupBy
$group
Having
$match
Select
$project
orderBy
$sort
Limit
$limit
Sum
$sum

Summerization of data
Do not store data in any data tables
Do not change data
Like group by clause

db.products.aggregate({$group:{_id:"$Category",TotalValue:{$sum:"$Price"}}})
"_id" : "Desktops", "TotalValue" : 0 }
"_id" : "Laptops", "TotalValue" : 0 }
db.products.aggregate({$group:{_id:"$Category",TotalValue:{$sum:"$Price"}}})
"_id" : "Laptops", "TotalValue" : 110000 }
db.products.aggregate({$group:{_id:"$Category",TotalValue:{$sum:"$Price"}}})
"_id" : "Desktops", "TotalValue" : 20000 }
"_id" : "Laptops", "TotalValue" : 110000 }
Posted by at 2 comments:
Labels: ,

Friday, June 5, 2015

Posting Data to MongoDB using MVC5

Lets take an example of adding a book category to MongoDB

Create a controller and add action method to get the UI page so that user can enter the data

Add an Action Method
   [HttpGet]
        public ActionResult AddBookCategory()
        {
            return View("View1");
        }



add a Model
 public class BookCategory
    {
        public string CategoryName { get; set; }
    }
}

add a strongly typed View

This view contains text box control to add data and a submit button
we will use html helpers to use that

@model WebApplication1.Models.BookCategory

@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>View1</title>
</head>
<body>
    @using (Html.BeginForm())
    {
        @Html.AntiForgeryToken()
       
        <div class="form-horizontal">
            <h4>BookCategory</h4>
            <hr />
            @Html.ValidationSummary(true)
   
            <div class="form-group">
                @Html.LabelFor(model => model.CategoryName, new { @class = "control-label col-md-2" })
                <div class="col-md-10">
                    @Html.EditorFor(model => model.CategoryName)
                  
                </div>
            </div>
   
            <div class="form-group">
                <div class="col-md-offset-2 col-md-10">
                    <input type="submit" value="Create" class="btn btn-primary" onclick="return confirm('This will insert category in the database')" />
                </div>
            </div>
        </div>
    }
   
    <div>
        @Html.ActionLink("Back to List", "Index")
    </div>
</body>
</html>

and then again add a Action method inside a controller to insert into Mongo DB

[HttpPost]
        public ActionResult AddBookCategory(BookCategory category)
        {
           
            MongoClient monClient = new MongoClient("mongodb://localhost");
            MongoServer ms = monClient.GetServer();
            ms.Connect();
            MongoDatabase db = ms.GetDatabase("testdb");
            MongoCollection collection = db.GetCollection<BookCategory>("BookCategory");
            BookCategory bc=new BookCategory();
            bc.CategoryName=Request.Form["CategoryName"];
            collection.Insert(category);
            return View("View1");
          
        }
Isnt it simple . Try it out

Posted by at No comments:
Labels: , , ,
Subscribe to: Posts (Atom)