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