PL I – ASSIGNMENT 12

ASSIGNMENT NO.  12

Title:

Indexing and querying with MongoDB using suitable example.

Requirements:

  1. Computer System with Linux/Open Source Operating System.

Theory:

Indexes: Indexes support the efficient resolution of queries. Without indexes, MongoDB must scan every document of a collection to select those documents that match the query statement. This scan is highly inefficient and require the mongod to process a large volume of data. Indexes are special data structures, that store a small portion of the data set in an easy to traverse form. The index stores the value of a specific field or set of fields, ordered by the value of the field as specified in index.

The ensureIndex() Method:  To create an index you need to use ensureIndex() method of mongodb.

Syntax: Basic syntax of ensureIndex() method is as follows()

>db.COLLECTION_NAME.ensureIndex({KEY:1})

Here key is the name of filed on which you want to create index and 1 is for ascending order. To create index in descending order you need to use -1.

Example:

>db.mycol.ensureIndex({"title":1})

In ensureIndex() method you can pass multiple fields, to create index on multiple fields.

>db.mycol.ensureIndex({"title":1,"description":-1})

explain() method : This method provides information on the query plan. The query plan is the plan the server uses to find the matches for a query. This information may be useful when optimizing a query. The explain() method returns a document that describes the process used to return the query results. The explain() method has the following form:

db.collection.find().explain()

Querying in MongoDB:

The find() Method : To query data from MongoDB collection,  use MongoDB’s find() method.

Syntax:  Basic syntax of find() method is as follows

>db.COLLECTION_NAME.find()

find() method will display all the documents in a non structured way.

The pretty() Method:  To display the results in a formatted way, you can use pretty() method.

Syntax:

>db.mycol.find().pretty()

Apart from find() method there is findOne() method, that reruns only one document.

AND in MongoDB:

Syntax: In the find() method if you pass multiple keys by separating them by ‘,’ then MongoDB treats it AND condition. Basic syntax of AND is shown below:

>db.mycol.find({key1:value1, key2:value2}).pretty()

OR in MongoDB:

Syntax: To query documents based on the OR condition, you need to use $or keyword. Basic syntax of OR is shown below:

 

>db.mycol.find(
{
$or: [
{key1: value1}, {key2:value2}
]
}
).pretty()

Querying Arrays: Querying for elements of an array is simple. An array can mostly be treated as though each element is the value of the overall key. For example, if the array is a list of fruits,like this:

> db.food.insert({"fruit" : ["apple", "banana", "peach"]})

the following query:

> db.food.find({"fruit" : "banana"})

will successfully match the document. We can query for it in much the same way as though we had a document that looked like the (illegal) document: {“fruit” : “apple”, “fruit” : “banana”, “fruit” : “peach”}.

$all: If you need to match arrays by more than one element, you can use “$all”. This allows you to match a list of elements. For example, suppose we created a collection with three elements:

> db.food.insert({"_id" : 1, "fruit" : ["apple", "banana", "peach"]})

> db.food.insert({"_id" : 2, "fruit" : ["apple", "kumquat", "orange"]})

> db.food.insert({"_id" : 3, "fruit" : ["cherry", "banana", "apple"]})

Then we can find all documents with both “apple” and “banana” elements by querying with “$all”:

> db.food.find({fruit : {$all : ["apple", "banana"]}})

{"_id" : 1, "fruit" : ["apple", "banana", "peach"]}

{"_id" : 3, "fruit" : ["cherry", "banana", "apple"]}

Order does not matter. Notice “banana” comes before “apple” in the second result. Using a one-element array with “$all” is equivalent to not using “$all”. For instance, {fruit : {$all : [‘apple’]} will match the same documents as {fruit : ‘apple’}. You can also query by exact match using the entire array. However, exact match will not match a document if any elements are missing or superfluous. For example, this will match the first document shown previously:

> db.food.find({"fruit" : ["apple", "banana", "peach"]})

But this will not:

> db.food.find({"fruit" : ["apple", "banana"]})

Querying on Embedded Documents: There are two ways of querying for an embedded

document: querying for the whole document or querying for its individual key/value pairs.

Querying for an entire embedded document works identically to a normal query. For example, if we have a document that looks like this:

{

“name” : {

“first” : “Joe”,

“last” : “Schmoe”

},

“age” : 45

}

we can query for someone named Joe Schmoe with the following:

> db.people.find({“name” : {“first” : “Joe”, “last” : “Schmoe”}})

However, if Joe decides to add a middle name key, suddenly this query won’t work anymore; it doesn’t match the entire embedded document! This type of query is also order-sensitive; {“last” : “Schmoe”, “first” : “Joe”} would not be a match.

If possible, it’s usually a good idea to query for just a specific key or keys of an embedded

document. Then, if your schema changes, all of your queries won’t suddenly break because

they’re no longer exact matches. You can query for embedded keys using dotnotation:

> db.people.find({“name.first” : “Joe”, “name.last” : “Schmoe”})

Now, if Joe adds more keys, this query will still match his first and last names. This dot-notation is the main difference between query documents and other document types. Query documents can contain dots, which mean “reach into an embedded document.” Dot-notation is also the reason that documents to be inserted cannot contain the . character. Oftentimes people run into this limitation when trying to save URLs as keys. One way to get around it is to always perform a global replace before inserting or after retrieving, substituting a character that isn’t legal in URLs for the dot (.) character. Embedded document matches can get a little tricky as the document structure gets more complicated. For example, suppose we are storing blog posts and we want to find comments by Joe that were scored at least a five. We could model the post as follows:

> db.blog.find()
{
"content" : "...",
"comments" : [
{
"author" : "joe",
"score" : 3,
"comment" : "nice post"
},
{
"author" : "mary",
"score" : 6,
"comment" : "terrible post"
}
]
}

Now, we can’t query using db.blog.find({“comments” : {“author” : “joe”, “score” : {“$gte” : 5}}}). Embedded document matches have to match the whole document, and this doesn’t match the “comment” key. It also wouldn’t work to do db.blog.find({“comments.author” : “joe”, “comments.score” : {“$gte” : 5}}), because the author criteria could match a different comment than the score criteria. That is, it would return the document shown earlier; it would match “author” : “joe” in the first comment and “score” : 6 in the second comment.

To correctly group criteria without needing to specify every key, use “$elemMatch”. This vaguely named conditional allows you to partially specify criteria to match a single embedded document in an array. The correct query looks like this:

> db.blog.find({“comments” : {“$elemMatch” : {“author” : “joe”, “score” : {“$gte” : 5}}}})

“$elemMatch” allows us to “group” our criteria. As such, it’s only needed when you have more than one key you want to match on in an embedded document.

Conclusion : This assignment explains details about use of indexing and querying in MongoDB.

 

*****************************QUERIES******************

AIM : Indexing and querying with MongoDB using suitable example.

> use te_a
switched to db te_a

> show collections
expt5
system.indexes

> db.expt5.find().pretty();
{
     "_id" : ObjectId("541cff058a5439b34ba0b41d"),
      "RNO" : 3,
      "NAME" : {
            "FIRST" : "AKASH",
            "LNAME" : "KOTHARI"
      },
      "ADDRESS" : {
            "CITY" : "PUNE",
            "DISTRICT" : "PUNE",
            "PIN" : 444605
      },
      "CONTACTNO" : 8888888888,
      "EMAIL" : "jfsdkfj@dkfj.com"
}
{
      "_id" : ObjectId("541e3d3d6a3acb31eb494c35"),
      "RNO" : 2,
      "NAME" : {
            "FIRST" : "ISHAAN",
            "LNAME" : "KOTHARI"
      },
      "ADDRESS" : {
            "CITY" : "AMRAVATI",
            "DISTRICT" : "AMRAVATI",
            "PIN" : 444605
      },
      "CONTACTNO" : 789798797
}
{
      "_id" : ObjectId("541e4391313dc145de686df9"),
      "RNO" : 1,
      "NAME" : {
            "FIRST" : "SANDESH",
            "LNAME" : "GANGWAL"
      },
      "ADDRESS" : {
            "CITY" : "PUNE",
            "DISTRICT" : "PUNE",
            "PIN" : 345678
      },
      "CONTACTNO" : [
            1234567,
            456788076
      ]
}
 
> db.expt5.ensureIndex({"RNO":1},{"UNIQUE":true});
{
      "createdCollectionAutomatically" : false,
      "numIndexesBefore" : 1,
      "numIndexesAfter" : 2,
      "ok" : 1
}
 
> db.expt5.find().explain();
{
      "cursor" : "BasicCursor",
      "isMultiKey" : false,
      "n" : 3,
      "nscannedObjects" : 3,
      "nscanned" : 3,
      "nscannedObjectsAllPlans" : 3,
      "nscannedAllPlans" : 3,
      "scanAndOrder" : false,
      "indexOnly" : false,
      "nYields" : 0,
      "nChunkSkips" : 0,
      "millis" : 0,
      "server" : "localhost.localdomain:27017",
      "filterSet" : false
}
 
> db.expt5.insert({"RNO" : 1, "NAME" : { "FIRST" : "SANDESH", "LNAME" : "GANGWAL" },
"ADDRESS" : { "CITY" : "PUNE", "DISTRICT" : "PUNE", "PIN" : 345678 }, "CONTACTNO" :[1234567,456788076] });
WriteResult({
      "nInserted" : 0,
      "writeError" : {
            "code" : 11000,
            "errmsg" : "insertDocument :: caused by :: 11000 E11000 duplicate key error index: te_a.expt5.$RNO_1  dup key: { : 1.0 }"
      }
})
 
> db.expt5.find().sort({"NAME.FIRST":1});
{ "_id" : ObjectId("541cff058a5439b34ba0b41d"), "RNO" : 3, "NAME" : { "FIRST" : "AKASH", "LNAME" : "KOTHARI" }, "ADDRESS" : { "CITY" : "PUNE", "DISTRICT" : "PUNE", "PIN" : 444605 }, "CONTACTNO" : 8888888888, "EMAIL" : "jfsdkfj@dkfj.com" }
{ "_id" : ObjectId("541e3d3d6a3acb31eb494c35"), "RNO" : 2, "NAME" : { "FIRST" : "ISHAAN", "LNAME" : "KOTHARI" }, "ADDRESS" : { "CITY" : "AMRAVATI", "DISTRICT" : "AMRAVATI", "PIN" : 444605 }, "CONTACTNO" : 789798797 }
{ "_id" : ObjectId("541e4391313dc145de686df9"), "RNO" : 1, "NAME" : { "FIRST" : "SANDESH", "LNAME" : "GANGWAL" }, "ADDRESS" : { "CITY" : "PUNE", "DISTRICT" : "PUNE", "PIN" : 345678 }, "CONTACTNO" : [ 1234567, 456788076 ] }
 
> db.expt5.find({"RNO":3});
{ "_id" : ObjectId("541cff058a5439b34ba0b41d"), "RNO" : 3, "NAME" : { "FIRST" : "AKASH", "LNAME" : "KOTHARI" }, "ADDRESS" : { "CITY" : "PUNE", "DISTRICT" : "PUNE", "PIN" : 444605 }, "CONTACTNO" : 8888888888, "EMAIL" : "jfsdkfj@dkfj.com" }

> db.expt5.find({"RNO":3,"PIN":3434434});


> db.expt5.find({"RNO":3,"NAME.FIRST":"AKASH"});
{ "_id" : ObjectId("541cff058a5439b34ba0b41d"), "RNO" : 3, "NAME" : { "FIRST" : "AKASH", "LNAME" : "KOTHARI" }, "ADDRESS" : { "CITY" : "PUNE", "DISTRICT" : "PUNE", "PIN" : 444605 }, "CONTACTNO" : 8888888888, "EMAIL" : "jfsdkfj@dkfj.com" }

> db.expt5.find({$or:[{"RNO":3},{"ADDRESS.CITY":"PUNE"}]});
{ "_id" : ObjectId("541cff058a5439b34ba0b41d"), "RNO" : 3, "NAME" : { "FIRST" : "AKASH", "LNAME" : "KOTHARI" }, "ADDRESS" : { "CITY" : "PUNE", "DISTRICT" : "PUNE", "PIN" : 444605 }, "CONTACTNO" : 8888888888, "EMAIL" : "jfsdkfj@dkfj.com" }
{ "_id" : ObjectId("541e4391313dc145de686df9"), "RNO" : 1, "NAME" : { "FIRST" : "SANDESH", "LNAME" : "GANGWAL" }, "ADDRESS" : { "CITY" : "PUNE", "DISTRICT" : "PUNE", "PIN" : 345678 }, "CONTACTNO" : [ 1234567, 456788076 ] }

 >db.expt5.find({"RNO":{$gt:1}});
{ "_id" : ObjectId("541e3d3d6a3acb31eb494c35"), "RNO" : 2, "NAME" : { "FIRST" : "ISHAAN", "LNAME" : "KOTHARI" }, "ADDRESS" : { "CITY" : "AMRAVATI", "DISTRICT" : "AMRAVATI", "PIN" : 444605 }, "CONTACTNO" : 789798797 }
{ "_id" : ObjectId("541cff058a5439b34ba0b41d"), "RNO" : 3, "NAME" : { "FIRST" : "AKASH", "LNAME" : "KOTHARI" }, "ADDRESS" : { "CITY" : "PUNE", "DISTRICT" : "PUNE", "PIN" : 444605 }, "CONTACTNO" : 8888888888, "EMAIL" : "jfsdkfj@dkfj.com" }

> db.expt5.find().sort({"NAME.FIRST":1});
{ "_id" : ObjectId("541cff058a5439b34ba0b41d"), "RNO" : 3, "NAME" : { "FIRST" : "AKASH", "LNAME" : "KOTHARI" }, "ADDRESS" : { "CITY" : "PUNE", "DISTRICT" : "PUNE", "PIN" : 444605 }, "CONTACTNO" : 8888888888, "EMAIL" : "jfsdkfj@dkfj.com" }
{ "_id" : ObjectId("541e3d3d6a3acb31eb494c35"), "RNO" : 2, "NAME" : { "FIRST" : "ISHAAN", "LNAME" : "KOTHARI" }, "ADDRESS" : { "CITY" : "AMRAVATI", "DISTRICT" : "AMRAVATI", "PIN" : 444605 }, "CONTACTNO" : 789798797 }
{ "_id" : ObjectId("541e4391313dc145de686df9"), "RNO" : 1, "NAME" : { "FIRST" : "SANDESH", "LNAME" : "GANGWAL" }, "ADDRESS" : { "CITY" : "PUNE", "DISTRICT" : "PUNE", "PIN" : 345678 }, "CONTACTNO" : [ 1234567, 456788076 ] }

> db.expt5.find().sort({"ADDRESS.CITY":-1})
{ "_id" : ObjectId("541e4391313dc145de686df9"), "RNO" : 1, "NAME" : { "FIRST" : "SANDESH", "LNAME" : "GANGWAL" }, "ADDRESS" : { "CITY" : "PUNE", "DISTRICT" : "PUNE", "PIN" : 345678 }, "CONTACTNO" : [ 1234567, 456788076 ] }
{ "_id" : ObjectId("541cff058a5439b34ba0b41d"), "RNO" : 3, "NAME" : { "FIRST" : "AKASH", "LNAME" : "KOTHARI" }, "ADDRESS" : { "CITY" : "PUNE", "DISTRICT" : "PUNE", "PIN" : 444605 }, "CONTACTNO" : 8888888888, "EMAIL" : "jfsdkfj@dkfj.com" }
{ "_id" : ObjectId("541e3d3d6a3acb31eb494c35"), "RNO" : 2, "NAME" : { "FIRST" : "ISHAAN", "LNAME" : "KOTHARI" }, "ADDRESS" : { "CITY" : "AMRAVATI", "DISTRICT" : "AMRAVATI", "PIN" : 444605 }, "CONTACTNO" : 789798797 }

> db.expt5.find().sort({"ADDRESS.CITY":-1,"NAME.FIRST":1});
{ "_id" : ObjectId("541cff058a5439b34ba0b41d"), "RNO" : 3, "NAME" : { "FIRST" : "AKASH", "LNAME" : "KOTHARI" }, "ADDRESS" : { "CITY" : "PUNE", "DISTRICT" : "PUNE", "PIN" : 444605 }, "CONTACTNO" : 8888888888, "EMAIL" : "jfsdkfj@dkfj.com" }
{ "_id" : ObjectId("541e4391313dc145de686df9"), "RNO" : 1, "NAME" : { "FIRST" : "SANDESH", "LNAME" : "GANGWAL" }, "ADDRESS" : { "CITY" : "PUNE", "DISTRICT" : "PUNE", "PIN" : 345678 }, "CONTACTNO" : [ 1234567, 456788076 ] }
{ "_id" : ObjectId("541e3d3d6a3acb31eb494c35"), "RNO" : 2, "NAME" : { "FIRST" : "ISHAAN", "LNAME" : "KOTHARI" }, "ADDRESS" : { "CITY" : "AMRAVATI", "DISTRICT" : "AMRAVATI", "PIN" : 444605 }, "CONTACTNO" : 789798797 }

> db.expt5.find().sort({"FNAME":1});
{ "_id" : ObjectId("541e3d3d6a3acb31eb494c35"), "RNO" : 2, "NAME" : { "FIRST" : "ISHAAN", "LNAME" : "KOTHARI" }, "ADDRESS" : { "CITY" : "AMRAVATI", "DISTRICT" : "AMRAVATI", "PIN" : 444605 }, "CONTACTNO" : 789798797 }
{ "_id" : ObjectId("541e4391313dc145de686df9"), "RNO" : 1, "NAME" : { "FIRST" : "SANDESH", "LNAME" : "GANGWAL" }, "ADDRESS" : { "CITY" : "PUNE", "DISTRICT" : "PUNE", "PIN" : 345678 }, "CONTACTNO" : [ 1234567, 456788076 ] }
{ "_id" : ObjectId("541cff058a5439b34ba0b41d"), "RNO" : 3, "NAME" : { "FIRST" : "AKASH", "LNAME" : "KOTHARI" }, "ADDRESS" : { "CITY" : "PUNE", "DISTRICT" : "PUNE", "PIN" : 444605 }, "CONTACTNO" : 8888888888, "EMAIL" : "jfsdkfj@dkfj.com" }

>  db.expt5.find().skip(2);
{ "_id" : ObjectId("541e4391313dc145de686df9"), "RNO" : 1, "NAME" : { "FIRST" : "SANDESH", "LNAME" : "GANGWAL" }, "ADDRESS" : { "CITY" : "PUNE", "DISTRICT" : "PUNE", "PIN" : 345678 }, "CONTACTNO" : [ 1234567, 456788076 ] }

 
>  db.expt5.find().limit(2);
{ "_id" : ObjectId("541cff058a5439b34ba0b41d"), "RNO" : 3, "NAME" : { "FIRST" : "AKASH", "LNAME" : "KOTHARI" }, "ADDRESS" : { "CITY" : "PUNE", "DISTRICT" : "PUNE", "PIN" : 444605 }, "CONTACTNO" : 8888888888, "EMAIL" : "jfsdkfj@dkfj.com" }
{ "_id" : ObjectId("541e3d3d6a3acb31eb494c35"), "RNO" : 2, "NAME" : { "FIRST" : "ISHAAN", "LNAME" : "KOTHARI" }, "ADDRESS" : { "CITY" : "AMRAVATI", "DISTRICT" : "AMRAVATI", "PIN" : 444605 }, "CONTACTNO" : 789798797 }

 

Leave a Reply

Your email address will not be published. Required fields are marked *