Sharing Knlowledge With The World…

Month: February 2013

Data Retrieval MONGO – DB



Ankit Kansal & Nayan Naik
As we now that MongoDB is a NO-SQL database so we just can’t write  SQL queries to retrieve data from the MonogoDB. MongoDB itself has provided some basic methods by which you can easily retrieve the data. There are various operators provided through which you can easily retrieve the desired data.
We will provide you MongoDB methods as well as SQL Queries on the same data set. For Practice session we have taken the favorite emp table from scott schema and populate the same emp table in MongoDB schema.
1) Which query/method returns you all set of data from the Table/Collection(emp) ?
SQL:-    select * from emp
Mongo:- db.emp.find() —-for better representation you can write db.emp.find().pretty()  
*To retrieve a single result from collection—-db.emp.findOne()
2) Which query/method will return you data from specific columns lets say EMPNO,ENAME,SAL ?
SQL:-      select EMPNO,ENAME,SAL from emp;
Mongo:-  db.emp.find({},{_id:0,EMPNO:1,ENAME:1,SAL:1});
*please take care of field names they must be in same case as in collection unlike oracle it will not change the field cases implicitly.
**Each document implicitly displays you _id field so if you don’t want it to be displaed then use _id:0.
3) Which query/method will help in filtering data ?
 SQL:-     select * from emp where EMPNO = 7788;
Mongo:-  db.emp.find({EMPNO:7788},{});
*For String Data Type ENAME:”NAME” “” quotes are mandatory.4)Which method will return you the count of rows/documents currently present ? 
SQL:-     select count(*) from emp;
Mongo:- db.emp.count()——-or——db.emp.find().count() –return total count of documents.

5)How to perform sorting on a given data set?
SQL:-     select * from emp order by empno asc/desc    –by default it’s ascending
Mongo:-  db.emp.find().sort({EMPNO:1/-1});     –(-1) for desending

6)Querying with two or more condition and all must be true like AND condition?
SQL:-     select * from emp where EMPNO=7369 and DEPTNO=20;
Mongo:-  db.emp.find({EMPNO:7369,DEPTNO:20}).pretty()
7)Querying with two or more condition and ANY of them will be true?
SQL:-     select * from emp where EMPNO=7369 or DEPTNO=20;
Mongo:-  db.employee.find({“$or”:[{EMPNO:1111},{DEPTNO:30}]}) –This will return you any of the matched result.

8)Querying with two or more condition and ANY of them will be true with aggregation framework?
SQL:-     select column_name from emp where condition_1=? or condition_2=? group by column_name;
Mongo:-  db.employee.aggregate({$match:{$or:[{empno:1111},{deptno:30}]}},{$group:{_id:”$deptno”}}) –Within array after $or clause you can have any number of condition with in each separate group distinguished by ,

9)Explain how $in operator works with example?
IN OPERATOR / $in operator
SQL:-     select * from emp where empno in (1113,1114);
Mongo:-  db.employee.find({empno:{$in:[1113,1114]}})

10)Explain how $nin operator works with example?
NIN OPERATOR / $in operator
SQL:-     select * from emp where empno NOT in (1113,1114);
Mongo:-  db.employee.find({empno:{$nin:[1113,1114]}})

11)Explain how $lt/$gt operator works with example?
$lt/$gt OPERATOR
SQL:-     select * from emp where SAL > 1500 and SAL < 3000;
Mongo:-  db.emp.find({EMPNO:{$gt:1500},EMPNO:{$lt:3000}});

12)Explain how $gte/$lte operator works with example?
Ans:- Same as above but it includes the given values also

13)Explain how $ne operator works with example?
Ans:- All outputs except the the match/where condition.

14)Explain the usage of $exists operator?

* Relational database management system works on fixed schema structure where  as MongoDB is schema less document based Database. $exists operator is to find out those documents which contains the specified field on the basis of two conditions either true or false. If true then only those documents will retrieved which contains the specified filed within the document and if false then only those records will be retrieved which do not contain that  field.
example:- This query will return those documents which do not contain comm field.
15) Accessing data of sub documents in MongoDB?
As we know that in mongodb a document can be contained with in another document and to access a sub document . operator must be used.
Sub Documents
16) Find out those employees which belongs to department higher than department 10 using max() or $max operator in MongoDB?
max function provides you a functionality of specifying an upper bound to your query result.
To use max function or the $max operator in mongodb first you have to build the index on that particular column and then you can query the collection to retrieve your data.
example describes your query returns only that data which is lower to the department 30

max function

Min function totally works in reverse direction/order.

Continue Reading

Data Aggregation FrameWork



Ankit Kansal & Nayan Naik

Data Aggregation is a powerful technique provided by MongoDB for performing aggregation analysis. This technique is mostly similar with what we have in oracle as GROUP BY clause.
There are some of guidelines provided by MongoDB developer zone which you must follows to obtain the desired output.
Data flow inside an aggregation function moves from left to right.
               db.collection_name.aggregate(–set of operators required) 
 Operators Lists:-
  1. $project
  2. $match 
  3. $skip
  4. $limit
  5. $group
  6. $unwind
  7. $sort
  8. $sum      

Let’s understand the working of Aggregation framework with the use of Operators listed above.


               $project is a PROJECTION operator and is used with aggregation framework and mainly its work is to project the selected fields from a  given document.

In this query $project selects name filed from things collection by writing name:1 and as we know that _id field is by default selected so to avoid displaying _id field we have written _id:0.
NOTE:- Aggregation framework works upon the principle of pipe lines that is once you have select the fields and you have moved to next part then only those selected fields will be available to perform operations for you and other fields from the collection will be vanished.more….


 In the above figure the query is selecting/projecting only the names from the collection and later on when i am using $match/WHERE operator to put a condition on the data the output which received is empty. This shows working as a pipe line in aggregation framework.


              $match operator is used to filter the documents based upon some defined conditions.This operator is mostly similar with WHERE clause of SQL.
 In the previous example i have used $match operator with $project operator. As described earlier $match operator is used for filtering the data based upon some required condition and then $project operator is used which helps in selecting the desired fields only.

When you are using _id operator which tells about the column on which aggregation is to be performed, there you have to use “$” symbol before the field name and it tells that data should be taken of the field, and keep in mind that when you are using double group by aliasing of columns must be done.

Scenario 1:-

For this scenario i have a collection name employee which is somehow resembles emp table of oracle.

$group operator is similar as group by clause in oracle. As, we already know that $project is used for selecting the columns from the collection. _id operator is used to specify the column on which group by operation is to be performed (such as group by deptno). count is a variable name that is used to display the values generated by $sum operator for each group. $sum is used to add a value specified in the clause within a group for each member. In my example i have used 1 so, in this case 1 will addded for each member within a group. If i have taken 2 instead of 1 then for each member in a group 2 is added.Here 1 behaves as count(empno) function in ORACLE.

Scenario 2:-
In this scenario same employee collection is used and $sort operator is used for sorting the result. Now, 
If you want to perform some operations on the data generated within aggregation function then it should be performed to the next level in which it is produced. Such as count data is produced with in the group operator so to perform some functions onto it you must perform onto the next level in which it is produced. In this scenario i want to sort the result on the basis of count generated. That’s why $sort operator was applied onto the next level.
Scenario 3:-
Same employee collection is used in this case 
1) Firstly $skip operator is used to skip the top rows generated. 
2) Secondly $limit operator is used to limit the number of rows to be processed further.

Scenario 4:-

This Scenario helps you to understand how a double group by works in MongoDB. Like in Oracle where we do as group by deptno,jobid. Now to achieve a similar kind of functionality in MobgoDB it should be done as-
Note:- In this case i have sorted the data on the basis of deptno which is a sub-document of _id field. So in this case we have to use “_id.deptno” field name to sort the final data. Nested documents can be accessed using . operator and “” quotes were also necessary.(-) negative symbol signifies that data should be sorted in descending order only.
Scenario 5:-
This scenario considered the usage of $unwind operator. $unwind operator is only used when you have array as a data type and $unwind operator is used to separate the values present in the array. For each element in the array a separate document is created with the same object_Id.
collection formed:-

Output Received After the query:-
Scenario 6:-
In this scenario i ll show you the usage of $substr operator to be used in mongoDB. Like in other technologies, here also $substr is used for data extraction from a particular selected field, but currently it is only supported by String Fileds.
So, by writing this type of query one can extract data from the given selected field and later on group by operation can also be performed onto it. The first integer inside the syntax specifies the location to start from the string and the next integer tells how many characters you want from the string to be extracted. By writing this query and later on by applying $group operator you can identify and count the number of people who have same aliases.

Scenario 7:-

In this scenario we will see the usage of $subtract operator. This operator provides functionality of deleting one field from the other.

This will return you the difference of two numbers. Second number is subtracted from the first one.
Similarly, you can work upon $sum operator.

$strcasecmp:-This operator is used to compare the two given string in the document if the length of the first string is greater then that of second string then the result is positive, and if the length of the second string is greater then that of first string then the result is in negative. If both the string are same then return output is 0.    
Continue Reading

Date Manipulation for MongoDB



Ankit Kansal & Nayan Naik

MONGODB stores dates and time in UTC(Co-ordinated universal time) format for documents.To check how you a UTC looks like—–>>
  1. Go on your mongo shell 
  2. type var vDate = new Date()
  3. vDate

This will print you the current date and time in UTC time format.

In our this post we will discuss some scenarios that generally comes while developing any application. This will help you to overcome various problems.

                      Suppose you want to insert a document in a collection and your requirement defines that one of your field is going to store current day-time value for future reference.
>>db.collection_name.insert({name:”ankit kansal”,currnt_time:new Date()})
This statement will store a document within a collection and it’s going to have three fields first is your _id column, second is your name and third is currnt_time.
                      Now one of your user want to query the data. And he wants only documents to be printed which satisfies a particular date lets suppose 10/1/2013, but your document hold data inform of UCT date format so what will you do then ?

Date Query
So, to overcome this situation one solution is to add a new column lets say with a name user_date and it’s value should be derived from the field that stores orignal date i.e. UTC date.
  1.  Take the output of a desired query in a variable.
  2.  Extract all the data in different variables.
  3.  Convert them string format and concatenate them using “-“
  4.  Then save them by assigning a new key field.
Now a user can easily access the information, and by applying several business logic you can achieve various functionalaties.
  1. new Date() // current date and time.
  2. new Date(milliseconds) //milliseconds since 1970/01/01
  3. new Date(dateString)
  4. new Date(year, month, day, hours, minutes, seconds, milliseconds)
Similarly you can access Time Values also.


Continue Reading

CRUD operations in MongoDB



Ankit Kansal & Nayan Naik

CRUD provides Create,Remove,Update and Delete functionality to the MongoDB. These are the four major functions that are used to develop mongoDB based applications.All four of them comes under the category of write operations.
These functions are generally performed on collections(Table) level.
                     Create operation is used to insert a new document with in the collection or to create a new collection with a specified document entries(key:value).
Each document in a mongoDB collection can have maximum of 16 MB size.
During insertion please keep in mind
  1. The field/column name _id is reserved for use as a primary key; its value must be unique in the collection, is immutable, and may be of any type other than an array.
  2. The field names cannot start with the $ character.
  3. The field names cannot contain the . character.
Ways to Create a collection or to Insert a new document for a particular Collection. 

Insert Function:-

Insert is a basic function that is commonly used to insert a new row/document in a collection. It also provides the functionality such that if a Collection does not exist and you are trying to insert a document than it automatically creates a new collection with the given document/record.




     Fig 1.1
show collections:- show collections is a method which is used to display the present available collection in the current database. It’s similar as Select * from CAT/TAB in oracle.
db.collection_name.find():- for now simply this method is used to find the documents for a collections. No arguments returns all the documents for you.
*more info later

db.collection_name.findOne():- This function returns a single arbitrary document from the collection but the result is in proper format as displayed above. It makes the document more readable 
So, in the given example first i checked how many collections are present inside the database with SHOW COLLECTIONS and then with the help of the insert function i inserted a document with the information displayed in fig 1.1. Prior to the insertion the collection was not existed but after a simple insertion a collection with name employee created as well as a record was also inserted into that collection.
*note If you attempt to insert a document without the _id field, the client library or the mongod instance will add an _id field and populate the field with a unique ObjectId. or you can manually provide an unique value to the _id field. _id behave as primary key for a each document and must be distinct. 

Save Function:-

Save function is different from insert function it performs a check if a document contains an _id   field reference of an existing document then it updates*  the document in the collection otherwise a fresh insert will be made to a collection.
*if the key is already present in the database then it replaces the old value from the new value and if the key is not present then it inserts a new key and value for that document.


Case1:- In this case a variable  x is initialized with one json document and then the variable got saved in the collection save_example

 Case2:- In this case a variable xTemp is holding a json document returned from a query and then i add a variable sal into that variable and then save() is applied resulting an update to that document as $set does.


Case3:- In this case a variable xTemp is holding a document which is received from a query and then as the name key already present in the document a simple update is fired resulting a name value change for that document

Fig 1.5

note:-If a variable is initialized with a separate document and save function is fired then if the collection contains a same _id field then whole document will be replaced with new one else an insert will be made.
Fig 1.6

                        Remove function allows you to delete/remove documents/rows from the collections. It’s mostly similar to the DELETE command that we normally uses in Relational Databases. 


db.Collection_name.remove({“key”:”value”}) or db.Collection_name.remove()

1) Ist syntax requires a parameter that is where condition it searches for the given document/documents and then delete it from the collcetion.
2) Second syntax did not contain any argument and thus deletes all the documents from the collection.

Case1:- When we pass some parameters to the query to find out the desired documents and then remove them.

In the previous example i wanted to delete the document/documents whose  name value contains “itemtodelete”, so in where clause db.remove_function.remove({where_clause})  i specified the requirement and then fired the query for the given collection.
                   While you are passing the parameters in a where clause to find out the documents and then removes and along with that you provide another argument as 1 or true in that case only a single document from all the match documents will be removed.
The following operation deletes a single document from the delete_function collection where name:”nayan”

    db.delete_function.remove( { name:”nayan” }, 1 )

Case2:- Second syntax does not hold any argument resulting all the documents from the given collection will be removed.
                     Update method is used to change the previous values to new values for a particular method or for whole of the documents in the collection.
UPDATE method have a lot of scenarios that will be discussed .


  • By default update() only updates a single document but if you want to update all the documents matching query condition then you have to use multi = true/1.
  • upsert provides special functionality such that if no document was found according to the query used in update function than it inserts a new document in the collection. And that document contains all the key values that are defined only in the update section of the update function.

 Case1:- In this case no matching condition is given and i have not use the key-word $set (if $set keyword is not used then whole document will be replaced with the field/fields given in update command). So one of the document attributes will be completely changed.

 use of $set operatorupdate with $set

 $set and $unset is also used for inserting a new field and also for removing a given field respectively. $unset takes a boolean value.


To change/rename the name of the field:-

                                     Now in my next example i am going to show you real time example that uses upsert and multi update show look into the figure below.

 The figure shown below displays a collection which contains some of the documents. In some of the documents category field is absent although they have their age field defined. And the problem domain specifies that each document must have the category column with them.


 In this given example no one is found greater than age 30 and upserts boolean flag is true resulting a new document creation with values provided in set portion of the update function.

$inc operator:-

                      $inc operator is used with the update function. As name tells this operator is used to increase the value of a field which must be of number type.When this operator is used with -(negative) sign then it will decrements the value. 
Increment using $inc
Increment a Number Value

                                            $addToSet operator used to add a specific value to an array element. If the value is already present in the document than no operation will be performed otherwise that specific value will be inserted within the field for that document.
*$each operator provides you a functionality to add multiple values to your array field at once and no duplicate values will be inserted.


                                                            MongoDB supports array type. It’s special type of container which is used for holding data under a single field. It provides index from 0 onwards to its holded data. When you perform an update to the document a field.
It’s behavior is similar with STACK used in data structure. When you want to insert an element then element is going to be inserted at the END/TOP and  when you want to remove an element then the element is removed from the same END/TOP it means you can not remove an element in between.

Simple Example displaying a normal update in MONGODB using push and pop operator.

*$pushAll operator will insert a sequence of elements to your array. No need to insert values again and again.
db.collection_name.update( { search_condition }, { $pushAll: { field1: [ value1, value2, value3 ] } } );
*If field does not exists in the document then it will automatically insert a field for you.BUT IT WILL NOT ELIMINATE DUPLICATE VALUES FOR YOU

                                                                    MonogoDB does not supports joins in between two collections. Although you can achieve the similar kind of functionality using your own codes/manually.
MongoDB Supports nesting of documents that is a document itself can hold an another document. lets say there is a document employee which contain two different documents personal and professional. Both of this document contains different type of information with them.


example for update of nested documents:                               



Continue Reading