MongoDB query with null

Description

In this page, we are going to discuss how null value in mongodb documents can be fetched using $type, $exists operators.

The $type operator in mongodb matches values based on their BSON type.

The $exist operator checks whether the particular field exist or not.

Our database name is 'myinfo' and our collection name is 'testtable'. Here, is the collection bellow.

Sample collection "testtable"

{
        "_id" : ObjectId("528f22140fe5e6467e58ae73"),
        "user_id" : "user1",
        "password" : "1a2b3c",
        "sex" : "Male",
        "age" : 17,
        "date_of_join" : "16/10/2010",
        "education" : "M.C.A.",
        "profession" : "CONSULTANT",
        "interest" : "MUSIC"
}
{
        "_id" : ObjectId("528f222e0fe5e6467e58ae74"),
        "user_id" : "user2",
        "password" : "11aa1a",
        "sex" : "Male",
        "age" : 24,
        "date_of_join" : "17/10/2009",
        "education" : "M.B.A.",
        "profession" : "MARKETING",
        "interest" : null
}
{
        "_id" : ObjectId("528f22390fe5e6467e58ae75"),
        "user_id" : "user3",
        "password" : "b1c1d1",
        "sex" : "Female",
        "age" : 19,
        "date_of_join" : "16/10/2010",
        "education" : "M.C.A.",
        "profession" : "IT COR.",
        "interest" : "ART"
}
{
        "_id" : ObjectId("528f22430fe5e6467e58ae76"),
        "user_id" : "user4",
        "password" : "abczyx",
        "sex" : "Female",
        "age" : 22,
        "date_of_join" : "17/8/2009",
        "education" : "M.B.B.S.",
        "profession" : "DOCTOR"
}

Document written in command prompt.

MongoDB fetch documents containing 'null'

If we want to fetch documents from the collection "testtable" which contains the value of "interest" is null, the following mongodb command can be used :

>db.testtable.find( { "interest" : null } ).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("528f222e0fe5e6467e58ae74"),
        "user_id" : "user2",
        "password" : "11aa1a",
        "sex" : "Male",
        "age" : 24,
        "date_of_join" : "17/10/2009",
        "education" : "M.B.A.",
        "profession" : "MARKETING",
        "interest" : null
}
{
        "_id" : ObjectId("528f22430fe5e6467e58ae76"),
        "user_id" : "user4",
        "password" : "abczyx",
        "sex" : "Female",
        "age" : 22,
        "date_of_join" : "17/8/2009",
        "education" : "M.B.B.S.",
        "profession" : "DOCTOR"
}

Document written in command prompt.

The above output shows that two documents have fetched. In the first document, the value of 'interest' is 'null' but in the second one 'interest' column does not exist.

MongoDB fetch documents with $type separator

If we want to fetch documents from the collection "testtable" which contains the value of "interest" is only null, the following mongodb command can be used :

> db.testtable.find( { "interest" : { $type : 10 } } ).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.

In the above example, $type operator followed by 10 have used. The number 10 indicates that the type is null and this statement returns only the document which contains the value of 'interest' is 'null'.

Output:

{
        "_id" : ObjectId("528f222e0fe5e6467e58ae74"),
        "user_id" : "user2",
        "password" : "11aa1a",
        "sex" : "Male",
        "age" : 24,
        "date_of_join" : "17/10/2009",
        "education" : "M.B.A.",
        "profession" : "MARKETING",
        "interest" : null
}

Document written in command prompt.

MongoDB fetch documents with $exists separator

If we want to fetch documents from the collection "testtable" which does not contain the "interest" column, the following mongodb command can be used :

> db.testtable.find( { "interest" : { $exists : false } } ).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.

In the above example, $exists operator followed by 'false' have used. The value 'false' indicates that the field 'interest' not exist within the document. If it is used 'true' followed by $exists all the documents except those which does not contain the 'interest' will be fetched.

Output:

{
        "_id" : ObjectId("528f22430fe5e6467e58ae76"),
        "user_id" : "user4",
        "password" : "abczyx",
        "sex" : "Female",
        "age" : 22,
        "date_of_join" : "17/8/2009",
        "education" : "M.B.B.S.",
        "profession" : "DOCTOR"
}

Document written in command prompt.

Previous: MongoDB or operation in query
Next: MongoDB Dot notation