SELECT Columns/Fields: MongoDB

In this video tutorial we shall see selecting of key: value in MongoDB.

In RDBMSs like MySQL, we do the same using SELECT statement.
In MongoDB, we use findOne() and find() methods.

Documents in ‘info’ collection

> db.info.find().forEach(printjson)
{
        "_id" : ObjectId("517e829d005b19f1f0d96b25"),
        "name" : "Apple",
        "product" : "iPhone5S",
        "emp_no" : "100"
}
{
        "_id" : ObjectId("517e8377005b19f1f0d96b26"),
        "name" : "Technotip",
        "product" : "Video Tutorials - Educational",
        "emp" : [
                "Satish",
                "Kiran"
        ],
        "videos" : {
                "mongo" : "MongoDB Videos",
                "php" : "PHP Video Tutorials"
        }
}

Learn Create and Insert Documents: MongoDB.

Note:
find() returns cursor objects.
findOne() returns single object.

We can use findOne() method to select and retrieve only one record at a time.

findOne() Method

> db.info.findOne({name: 'Technotip'}).product
Video Tutorials - Educational

> db.info.findOne({name: 'Technotip'}).videos
{ "mongo" : "MongoDB Videos", "php" : "PHP Video Tutorials" }

> db.info.findOne({name: 'Technotip'}).emp;
[ "Satish", "Kiran" ]

Syntax for retrieving normal {key: value} pair, sub-object {key: value} pair and array {key: value} pair is same.

Limitation
Using findOne() method, we could select and return only 1 {key: value} pair.

To select and return more than 1 {key: value} pair, we can make use of find() method, with 2 parameters.

SELECT Columns or Fields ( { KEY: VALUE } ): MongoDB

find() Method, with two parameters

> db.info.find({name: 'Apple'}, {product: 1}).forEach(printjson);
{ "_id" : ObjectId("517e829d005b19f1f0d96b25"), "product" : "iPhone5S" }

First parameter is the condition, second parameter specifies the {key: value} pairs.

We can pass more than 1 key in second parameter; that would returns multiple {key: value} pairs.

> db.info.find({name: 'Apple'}, {product: 1, emp_no: 1}).forEach(printjson);
{
        "_id" : ObjectId("517e829d005b19f1f0d96b25"),
        "product" : "iPhone5S",
        "emp_no" : "100"
}

1 or true means, those {key: value} pairs need to be returned.
0 or false means, excluding those {key: value} pairs, all other {key: value} pairs(records) needs to be returned.

true(0)

> db.info.find({name: 'Apple'}, {product: true}).forEach(printjson);
{ "_id" : ObjectId("517e829d005b19f1f0d96b25"), "product" : "iPhone5S" }
> db.info.find({name: 'Apple'}, {product: true, emp_no: true}).forEach(printjson);
{
        "_id" : ObjectId("517e829d005b19f1f0d96b25"),
        "product" : "iPhone5S",
        "emp_no" : "100"
}

False(0): Exclusion

> db.info.find({name: 'Apple'}, {product: 0, emp_no: 0}).forEach(printjson);
{ "_id" : ObjectId("517e829d005b19f1f0d96b25"), "name" : "Apple" }

Note:
True and False combination doesn’t work

> db.info.find({name: 'Apple'}, {product: true, emp_no: false}).forEach(printjson);
Wed May 08 12:11:30.184 JavaScript execution failed: error: {
        "$err" : "You cannot currently mix including and excluding fields. 
                  Contact us if this is an issue.",
        "code" : 10053
} at src/mongo/shell/query.js:L128
> db.info.find({name: 'Apple'}, {product: 0, emp_no: 1}).forEach(printjson);
Wed May 08 12:11:40.840 JavaScript execution failed: error: {
        "$err" : "You cannot currently mix including and excluding fields. 
                  Contact us if this is an issue.",
        "code" : 10053
} at src/mongo/shell/query.js:L128

We can not combine true(1) and false(0) together in the second parameter.

Special Provision!
But we can do it with _id(ObjectId)

> db.info.find({name: 'Apple'}, 
               {product: 1, emp_no: 1, _id: 0}).forEach(printjson);
{ "product" : "iPhone5S", "emp_no" : "100" }

Operation of second document/record in ‘info’ collection.
This illustrates that the syntax for sub-objects, array and the normal key/value pair is same.

> db.info.find({name: 'Technotip'}, {product: 1}).forEach(printjson);
{
        "_id" : ObjectId("517e8377005b19f1f0d96b26"),
        "product" : "Video Tutorials - Educational"
}
{ "_id" : ObjectId("518363e2d73694e289255486") }
> db.info.find({name: 'Technotip'}, {product: 1, videos: 1}).forEach(printjson);

{
        "_id" : ObjectId("517e8377005b19f1f0d96b26"),
        "product" : "Video Tutorials - Educational",
        "videos" : {
                "mongo" : "MongoDB Videos",
                "php" : "PHP Video Tutorials"
        }
}

> db.info.find({name: 'Technotip'}, 
               {product: 1, videos: 1, emp: 1}).forEach(printjson);
{
        "_id" : ObjectId("517e8377005b19f1f0d96b26"),
        "product" : "Video Tutorials - Educational",
        "emp" : [
                "Satish",
                "Kiran"
        ],
        "videos" : {
                "mongo" : "MongoDB Videos",
                "php" : "PHP Video Tutorials"
        }
}

> db.info.find({name: 'Technotip'}, 
               {product: 1, videos: 1, emp: 1, _id: 0}).forEach(printjson);
{
        "product" : "Video Tutorials - Educational",
        "emp" : [
                "Satish",
                "Kiran"
        ],
        "videos" : {
                "mongo" : "MongoDB Videos",
                "php" : "PHP Video Tutorials"
        }
}

Note:
Two documents with same name

{
        "_id" : ObjectId("517e8377005b19f1f0d96b26"),
        "name" : "Technotip",
        "product" : "Video Tutorials - Educational",
        "emp" : [
                "Satish",
                "Kiran"
        ],
        "videos" : {
                "mongo" : "MongoDB Videos",
                "php" : "PHP Video Tutorials"
        }
}
{ "_id" : ObjectId("518363e2d73694e289255486"), "name" : "Technotip" }

> db.info.findOne({name: 'Technotip'})._id;
ObjectId("517e8377005b19f1f0d96b26")

If we use findOne() method on this collection, and the condition being the name key, then the oldest document will be returned.
Only one key: value pair is returned, as findOne() returns only single object.