MongoDB query language
Description
In this page we are going to discuss how mongo query language can be used with sort() and can be implemented like WHERE clause of SQL.
The MongoDB server treats all the query parameters as a single object. Some object such as $query, $orderby etc can be used to get the same result as return simple mongo query language. The $query can be evaluated as the WHERE clause of SQL and $orderby sorts the results as specified order.
Our database name is 'myinfo' and our collection name is 'userdetails'. Here, is the collection bellow.
Sample collection "userdetails"
{
"_id" : ObjectId("528cab88e1e41035b889f2bf"),
"user_id" : "user1",
"password" : "1a2b3c",
"date_of_join" : "16/10/2010",
"education" : "M.C.A.",
"profession" : "CONSULTANT",
"interest" : "MUSIC",
"community_name" : [
"MODERN MUSIC",
"CLASSICAL MUSIC",
"WESTERN MUSIC"
],
"community_moder_id" : [
"MR. Alex",
"MR. Dang",
"MR Haris"
],
"community_members" : [
700,
200,
1500
],
"friends_id" : [
"kumar",
"harry",
"anand"
],
"ban_friends_id" : [
"Amir",
"Raja",
"mont"
]
}
{
"_id" : ObjectId("528cabb5e1e41035b889f2c0"),
"user_id" : "user2",
"password" : "11aa1a",
"date_of_join" : "17/10/2009",
"education" : "M.B.A.",
"profession" : "MARKETING",
"interest" : "MUSIC",
"community_name" : [
"MODERN MUSIC",
"CLASSICAL MUSIC",
"WESTERN MUSIC"
],
"community_moder_id" : [
"MR. Roy",
"MR. Das",
"MR Doglus"
],
"community_members" : [
500,
300,
1400
],
"friends_id" : [
"pal",
"viki",
"john"
],
"ban_friends_id" : [
"jalan",
"monoj",
"evan"
]
}
{
"_id" : ObjectId("528cabd0e1e41035b889f2c1"),
"user_id" : "user3",
"password" : "b1c1d1",
"date_of_join" : "16/10/2010",
"education" : "M.C.A.",
"profession" : "IT COR.",
"interest" : "ART",
"community_name" : [
"MODERN ART",
"CLASSICAL ART",
"WESTERN ART"
],
"community_moder_id" : [
"MR. Rifel",
"MR. Sarma",
"MR Bhatia"
],
"community_members" : [
5000,
2000,
1500
],
"friends_id" : [
"philip",
"anant",
"alan"
],
"ban_friends_id" : [
"Amir",
"Raja",
"mont"
]
}
{
"_id" : ObjectId("528cabece1e41035b889f2c2"),
"user_id" : "user4",
"password" : "abczyx",
"date_of_join" : "17/8/2009",
"education" : "M.B.B.S.",
"profession" : "DOCTOR",
"interest" : "SPORTS",
"community_name" : [
"ATHELATIC",
"GAMES FAN GYES",
"FAVOURIT GAMES"
],
"community_moder_id" : [
"MR. Paul",
"MR. Das",
"MR Doglus"
],
"community_members" : [
2500,
2200,
3500
],
"friends_id" : [
"vinod",
"viki",
"john"
],
"ban_friends_id" : [
"jalan",
"monoj",
"evan"
]
}
Document written in command prompt.
MongoDB query language with sort()
If we want to fetch documents from the collection "userdetails" which contains the value of "date_of_join" is "16/10/2010" and the value of "education" is "M.C.A" and sort the fetched results in descending order, the following mongodb command can be used :
>db.userdetails.find({"date_of_join" : "16/10/2010","education":"M.C.A."}).sort({"profession":-1}).pretty();
N.B. find() method displays the documents in a non structured format but to display the results in a formatted way, the pretty() method can be used.
The SQL equivalent code is
SELECT *
FROM userdetails
WHERE date_of_join="16/10/2010"
AND education="M.C.A."
ORDER BY profession DESC;
Output:
{
"_id" : ObjectId("528cabd0e1e41035b889f2c1"),
"user_id" : "user3",
"password" : "b1c1d1",
"date_of_join" : "16/10/2010",
"education" : "M.C.A.",
"profession" : "IT COR.",
"interest" : "ART",
"community_name" : [
"MODERN ART",
"CLASSICAL ART",
"WESTERN ART"
],
"community_moder_id" : [
"MR. Rifel",
"MR. Sarma",
"MR Bhatia"
],
"community_members" : [
5000,
2000,
1500
],
"friends_id" : [
"philip",
"anant",
"alan"
],
"ban_friends_id" : [
"Amir",
"Raja",
"mont"
]
}
{
"_id" : ObjectId("528cab88e1e41035b889f2bf"),
"user_id" : "user1",
"password" : "1a2b3c",
"date_of_join" : "16/10/2010",
"education" : "M.C.A.",
"profession" : "CONSULTANT",
"interest" : "MUSIC",
"community_name" : [
"MODERN MUSIC",
"CLASSICAL MUSIC",
"WESTERN MUSIC"
],
"community_moder_id" : [
"MR. Alex",
"MR. Dang",
"MR Haris"
],
"community_members" : [
700,
200,
1500
],
"friends_id" : [
"kumar",
"harry",
"anand"
],
"ban_friends_id" : [
"Amir",
"Raja",
"mont"
]
}
Document written in command prompt.
The above output shows that, only those documents have fetched from the collection "userdetails" which contain the value of "date_of_join" is "16/10/2010" and the value of "education" is "M.C.A" and the documents have arranged in descending order according to the value of "profession".
MongoDB query language with $query and $orderby
The above example can be written as :
>db.userdetails.find({$query : {"date_of_join" : "16/10/2010","education":"M.C.A."}, $orderby : {"profession":-1}}).pretty();
N.B. find() method displays the documents in a non structured format but to display the results in a formatted way, the pretty() method can be used.
Output:
{
"_id" : ObjectId("528cabd0e1e41035b889f2c1"),
"user_id" : "user3",
"password" : "b1c1d1",
"date_of_join" : "16/10/2010",
"education" : "M.C.A.",
"profession" : "IT COR.",
"interest" : "ART",
"community_name" : [
"MODERN ART",
"CLASSICAL ART",
"WESTERN ART"
],
"community_moder_id" : [
"MR. Rifel",
"MR. Sarma",
"MR Bhatia"
],
"community_members" : [
5000,
2000,
1500
],
"friends_id" : [
"philip",
"anant",
"alan"
],
"ban_friends_id" : [
"Amir",
"Raja",
"mont"
]
}
{
"_id" : ObjectId("528cab88e1e41035b889f2bf"),
"user_id" : "user1",
"password" : "1a2b3c",
"date_of_join" : "16/10/2010",
"education" : "M.C.A.",
"profession" : "CONSULTANT",
"interest" : "MUSIC",
"community_name" : [
"MODERN MUSIC",
"CLASSICAL MUSIC",
"WESTERN MUSIC"
],
"community_moder_id" : [
"MR. Alex",
"MR. Dang",
"MR Haris"
],
"community_members" : [
700,
200,
1500
],
"friends_id" : [
"kumar",
"harry",
"anand"
],
"ban_friends_id" : [
"Amir",
"Raja",
"mont"
]
}
Document written in command prompt.
Previous:
MongoDB skip() and limit()
Next:
MongoDB or operation in query
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics