Fetch documents from collection with selective fields
Description
In this page, we are going to discuss how to fetch data for specific columns or except a specific column from a collection in MongoDB.
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"
]
}
Sample documents shown in command prompt
Fetch selective field from collection based on a criteria
If we want to fetch only the "user_id" for all documents from the collection 'userdetails' which hold the educational qualification "M.C.A.", the following mongodb command can be used :
>db.userdetails.find({"education":"M.C.A."},{"user_id" : 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 user_id
FROM userdetails
WHERE education="M.C.A.";
Output of the command
{ "_id" : ObjectId("528cab88e1e41035b889f2bf"), "user_id" : "user1" }
{ "_id" : ObjectId("528cabd0e1e41035b889f2c1"), "user_id" : "user3" }
Documents shown in command prompt
The above output shows that two documents have fetched from the collection "userdetails" but only the data of user_id have appeared for those documents who have the qualification "M.C.A.".
N.B. Here in the example, a "1" after "user_id" have introduced for fetching that field. So which fields you want to fetch you have to mention the "field_name" followed by a ":" and a "1". 1 indicates true. Show the example below.
Fetch more than one fields from collection based on a criteria
If we want to fetch the "user_id" , "password" and "date_of_jon" for all documents from the collection 'userdetails' which hold the educational qualification "M.C.A.", the following mongodb command can be used :
>db.userdetails.find({"education":"M.C.A."},{"user_id" : 1,"password":1,"date_of_join":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 user_id,password,date_of_join FROM userdetails WHERE education="M.C.A.";
Output:
{
"_id" : ObjectId("528cab88e1e41035b889f2bf"),
"user_id" : "user1",
"password" : "1a2b3c",
"date_of_join" : "16/10/2010"
}
{
"_id" : ObjectId("528cabd0e1e41035b889f2c1"),
"user_id" : "user3",
"password" : "b1c1d1",
"date_of_join" : "16/10/2010"
}
Documents written in command prompt
The above output shows that two documents have fetched from the collection "userdetails" but only the data of user_id, password and date_of_join have appeared for those documents who have the qualification "M.C.A.".
Fetch all data except selective field from collection based on a criteria
If we want to fetch all data except the "user_id" field for all documents from the collection 'userdetails' which hold the educational qualification "M.C.A.", the following mongodb command can be used :
>db.userdetails.find({"education":"M.C.A."},{"user_id" : 0}).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("528cab88e1e41035b889f2bf"),
"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("528cabd0e1e41035b889f2c1"),
"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"
]
}
Documents written in command prompt
The above output shows that two documents have fetched from the collection "userdetails" and all the fields have appeared except the data of user_id, for those documents who have the qualification "M.C.A."
Previous:
A simple MongoDB query
Next:
MongoDB sorting
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics