on
Italy
- Get link
- X
- Other Apps
1
2
3
4
5
6
7
| { "_id" : ObjectId( "5146bb52d8524270060001f3" ), "age" : 25, "city" : "Los Angeles" , "email" : "mark@abc.com" , "user_name" : "Mark Hanks" } |
_id
field, which is a 12-byte field that serves as a primary key for the
documents. The field is auto generated on creation of the document and
is used for uniquely identifying each document.users
and its corresponding structure in MongoDB. As shown in Fig 1, each row
in the SQL table transforms to a document and each column to a field in
MongoDB.int
type data and at the next instance it may hold an array
.address
and dob
which are not present in the second document while the second document contains fields gender
and occupation
which are not present in the first one. Imagine if we would have
designed this thing in SQL, we would have kept four extra columns for address
, dob
, gender
and occupation
, some of which would store empty (or null) values, and hence occupying unnecessary space.user_information
and contact_information
, with primary keys id
and contact_id
as shown in Fig 3. The contact_information
table would also contain a column user_id
which would be the foreign key linking to the id
field of the user_information
table.id
and contact_id
in our case) which acts as a primary column for that table. However, in MongoDB, we generally use the auto generated _id
field as the primary key to uniquely identify the documents.user_information
and contact_information
both having their unique _id
fields. We will have a field user_id
in the contact_information
document which relates to the _id
field of the user_information
document showing which user the contact corresponds to. (See Fig 4)
Note that in MongoDB, the relations and their corresponding operations
have to be taken care manually (for example, through code) as no foreign
key constraints and rules apply.user_id
field in our document is simply a field that
holds some data and all the logic associated with it has to be
implemented by us. For example, even if you will insert some user_id
in the contact_information
document that does not exist in the user_information
collection, MongoDB is not going to throw any error saying that corresponding user_id
was not found in the user_information
collection(unlike SQL where this would be an invalid foreign key constraint).contact_information
document inside the user_information
document like this (Fig 5):users
with document structure as follows:
1
2
3
4
5
6
7
| { "_id" : ObjectId( "5146bb52d8524270060001f3" ), "post_text" : "This is a sample post" , "user_name" : "mark" , "post_privacy" : "public" , "post_likes_count" : 0 } |
users
having five columns with the following structure:CREATE TABLE
query). The structure of the document is automatically created when the
first insert occurs in the collection. However, you can create an empty
collection using createCollection
command.
1
2
3
| SQL: CREATE TABLE `posts` (`id` int (11) NOT NULL AUTO_INCREMENT,`post_text` varchar (500) NOT NULL ,`user_name` varchar (20) NOT NULL ,`post_privacy` varchar (10) NOT NULL ,`post_likes_count` int (11) NOT NULL , PRIMARY KEY (`id`)) MongoDB: db.createCollection( "posts" ) |
insert
method which takes an object with key value pairs as its input. The inserted document will contain the autogenerated _id
field. However, you can also explicitly provide a 12 byte value as _id
along with the other fields.
1
2
3
| SQL: INSERT INTO `posts` (`id` ,`post_text` ,`user_name` ,`post_privacy` ,`post_likes_count`) VALUES ( NULL , 'This is a sample post' , 'mark' , 'public' , '0' ); MongoDB: db.posts. insert ({user_name: "mark" , post_text: "This is a sample post" , post_privacy: "public" , post_likes_count:0}) |
Alter Table
function in MongoDB to change
the document structure. As the documents are dynamic in schema, the
schema changes as and when any update happens on the document.find
method which is equivalent to the SELECT
command in SQL. The following statements simply read all the documents from the posts
collection.
1
2
3
| SQL: SELECT * FROM `posts` MongoDB: db.posts.find() |
user_name
field as mark
. All the criteria for fetching the documents have to be placed in the first braces {} separated by commas.
1
2
3
| SQL: SELECT * FROM `posts` WHERE `user_name` = 'mark' MongoDB: db.posts.find({user_name: "mark" }) |
post_text
and post_likes_count
as specified in the second set of braces {}.
1
2
3
| SQL: SELECT `post_text` , `post_likes_count` FROM `posts` MongoDB: db.posts.find({},{post_text:1,post_likes_count:1}) |
_id
field with each find statement. If we do not want this field in our result set, we have to specify the _id
key with a 0
value in the list of columns to be retrieved. The 0
value of the key indicates that we want to exclude this field from the result set.
1
| MongoDB: db.posts.find({},{post_text:1,post_likes_count:1,_id:0}) |
user_name
is mark
.
1
2
3
| SQL: SELECT `post_text` , `post_likes_count` FROM `posts` WHERE `user_name` = 'mark' MongoDB: db.posts.find({user_name: "mark" },{post_text:1,post_likes_count:1}) |
AND
condition. Thus, this statement will look for documents having both user_name
as mark
and post_privacy
as public
.
1
2
3
| SQL: SELECT `post_text` , `post_likes_count` FROM `posts` WHERE `user_name` = 'mark' AND `post_privacy` = 'public' MongoDB: db.posts.find({user_name: "mark" ,post_privacy: "public" },{post_text:1,post_likes_count:1}) |
OR
between the criteria in the find
method, we use the $or
operator.
1
2
3
| SQL: SELECT `post_text` , `post_likes_count` FROM `posts` WHERE `user_name` = 'mark' OR `post_privacy` = 'public' MongoDB: db.posts.find({$ or :[{user_name: "mark" },{post_privacy: "public" }]},{post_text:1,post_likes_count:1}) |
sort
method which sorts the result in ascending order of post_likes_count
(indicated by 1).
1
2
3
| SQL: SELECT * FROM `posts` WHERE `user_name` = 'mark' order by post_likes_count ASC MongoDB: db.posts.find({user_name: "mark" }).sort({post_likes_count:1}) |
-1
as the value of the field.
1
2
3
| SQL: SELECT * FROM `posts` WHERE `user_name` = 'mark' order by post_likes_count DESC MongoDB: db.posts.find({user_name: "mark" }).sort({post_likes_count:-1}) |
limit
method specifying the number of documents.
1
2
3
| SQL: SELECT * FROM `posts` LIMIT 10 MongoDB: db.posts.find().limit(10) |
offset
in SQL to skip some number of records, we use skip
function in MongoDB. For example, the following statement would fetch ten posts skipping the first five.
1
2
3
| SQL: SELECT * FROM `posts` LIMIT 10 OFFSET 5 MongoDB: db.posts.find().limit(10).skip(5) |
update
method specifies the
criteria to select the documents. The second parameter specifies the
actual update operation to be performed. For example, the following
query selects all the documents with user_name
as mark
and sets their post_privacy
as private
.update
query updates only one (and the first matched) document. To update all
the matching documents we have to provide a third parameter specifying multi
as true
indicating that we want to update multiple documents.
1
2
3
| SQL: UPDATE posts SET post_privacy = "private" WHERE user_name= 'mark' MongoDB: db.posts. update ({user_name: "mark" },{$ set :{post_privacy: "private" }},{multi: true }) |
1
2
3
| SQL: DELETE FROM posts WHERE user_name= 'mark' MongoDB: db.posts.remove({user_name: "mark" }) |
_id
field of each collection. To create new indexes on the fields, we use ensureIndex
method specifying the fields and associated sort order indicated by 1
or -1
(ascending or descending).
1
2
3
| SQL: CREATE INDEX index_posts ON posts(user_name,post_likes_count DESC ) MongoDB: db.posts.ensureIndex({user_name:1,post_likes_count:-1}) |
getIndexes
method on the same lines of SHOW INDEX
query of SQL.
1
2
3
| SQL: SHOW INDEX FROM posts MongoDB: db.posts.getIndexes() |
Comments
Post a Comment