MongoDB is one of the most popular NoSQL database where data are stored in the form of documents. You can also create tables in traditional way to put your structured data but the main purpose is to store unstructured data in the form of object which may vary based on a particular requirement.

Now when you select data from collection, you may not need all data to be fetched from the database collection and you want to restrict those data based on certain criteria or conditions while fetching from database. Restriction on data fetch serves both purposes – performance of the query or command used to fetch data and the exact data what you want for your requirement.

You are generally habituated to execute the traditional RDBMS query where you have already used these clauses – WHERE, AND, OR and IN – to restrict data fetching. In addition to such operators I am also going to show you how to use like (//) and greater than (gte) operators in the queries.

Here I am going to show you how to query documents, based on conditions, which are stored in a collection under a NoSQL database like MongoDB. I had also shown how to query documents from MongoDB using find() method but without any restriction or condition.

The WHERE condition fetches data based on a particular value that matches to the available data in the collection. The AND condition displays only those records where both condition 1 and condition 2 are met. The OR condition displays those records if any one of the conditions is met. The IN condition takes multiple values and based on these values, records are displayed.

Prerequisites

MongoDB 4.4.0, MongoDB Setup

Sample Data

As a first step I want to insert some sample data into user collection under roytuts database. Execute below insert() statements in the MongoDB shell to insert these data.

db.roytuts.user.insert({"firstName":"Soumitra","secondName":"Roy","address":{"permanent":"Earth","current":"Any"},"phone":"1234567890"});
db.roytuts.user.insert({"firstName":"John","secondName":"Smith","address":{"permanent":"Earth","current":"Jupitor"},"phone":"1234562890"});
db.roytuts.user.insert({"firstName":"Ikra","secondName":"Michell","address":{"permanent":"Earth","current":"Mars"},"phone":"1234567924"});
db.roytuts.user.insert({"firstName":"Jolly","secondName":"LLB","address":{"permanent":"Earth","current":"Saturn"},"phone":"1284167924"});

WHERE Condition

let’s say you want to fetch data based on a firstName key from the user collection. The command can be written as, for example, where firstName is Soumitra.

db.roytuts.user.find({firstName:"Soumitra"});

The above command will give you the following output on your Mongo shell. If you want to pretty print your output then you can use pretty() function.

The equivalent query in MySQL can be written as:

SELECT * FROM roytuts.user WHERE firstName = "Soumitra";

AND Condition

AND condition will fetch data if both conditions match. Let’s check out the following query.

db.roytuts.user.find({firstName:"Ikra", "secondName":"Michell"});

The above command will give you the following output.

> db.roytuts.user.find({firstName:"Ikra", "secondName":"Michell"});
{ "_id" : ObjectId("5f855a91a8e8239c6a8022fa"), "firstName" : "Ikra", "secondName" : "Michell", "address" : { "permanent" : "Earth", "current" : "Mars" }, "phone" : "1234567924" }

The equivalent MySQL query would be:

SELECT * FROM roytuts.user WHERE firstName = "Ikra" and secondName = "Michell";

You can also query nested object. Let’s say you want to fetch records for permanent address having Earth.

db.roytuts.user.find({"address.permanent":"Earth"});

The output would be for the above query.

> db.roytuts.user.find({"address.permanent":"Earth"});
{ "_id" : ObjectId("5f855a90a8e8239c6a8022f8"), "firstName" : "Soumitra", "secondName" : "Roy", "address" : { "permanent" : "Earth", "current" : "Any" }, "phone" : "1234567890" }
{ "_id" : ObjectId("5f855a91a8e8239c6a8022f9"), "firstName" : "John", "secondName" : "Smith", "address" : { "permanent" : "Earth", "current" : "Jupitor" }, "phone" : "1234562890" }
{ "_id" : ObjectId("5f855a91a8e8239c6a8022fa"), "firstName" : "Ikra", "secondName" : "Michell", "address" : { "permanent" : "Earth", "current" : "Mars" }, "phone" : "1234567924" }
{ "_id" : ObjectId("5f855a92a8e8239c6a8022fb"), "firstName" : "Jolly", "secondName" : "LLB", "address" : { "permanent" : "Earth", "current" : "Saturn" }, "phone" : "1284167924" }

Let’s take an another example, you want to fetch records having permanent address Earth and phone number greater than 1234567890. Note here I have put double quote (“”) around the phone number value because I had inserted with double quotation for phone numbers.

db.roytuts.user.find({"address.permanent":"Earth", "phone":{"$gte":"1234567890"}});

The above query will give you the following output:

> db.roytuts.user.find({"address.permanent":"Earth", "phone":{"$gte":"1234567890"}});
{ "_id" : ObjectId("5f855a90a8e8239c6a8022f8"), "firstName" : "Soumitra", "secondName" : "Roy", "address" : { "permanent" : "Earth", "current" : "Any" }, "phone" : "1234567890" }
{ "_id" : ObjectId("5f855a91a8e8239c6a8022fa"), "firstName" : "Ikra", "secondName" : "Michell", "address" : { "permanent" : "Earth", "current" : "Mars" }, "phone" : "1234567924" }
{ "_id" : ObjectId("5f855a92a8e8239c6a8022fb"), "firstName" : "Jolly", "secondName" : "LLB", "address" : { "permanent" : "Earth", "current" : "Saturn" }, "phone" : "1284167924" }

You can also perform like operation, for example, you want to fetch records having permanent address as art. You can use any one of the query to achieve the same.

db.roytuts.user.find({"address.permanent":/art/});

Or

db.roytuts.user.find({"address.permanent":/.*art.*/});

The above queries will produce below outputs.

> db.roytuts.user.find({"address.permanent":/art/});
{ "_id" : ObjectId("5f855a90a8e8239c6a8022f8"), "firstName" : "Soumitra", "secondName" : "Roy", "address" : { "permanent" : "Earth", "current" : "Any" }, "phone" : "1234567890" }
{ "_id" : ObjectId("5f855a91a8e8239c6a8022f9"), "firstName" : "John", "secondName" : "Smith", "address" : { "permanent" : "Earth", "current" : "Jupitor" }, "phone" : "1234562890" }
{ "_id" : ObjectId("5f855a91a8e8239c6a8022fa"), "firstName" : "Ikra", "secondName" : "Michell", "address" : { "permanent" : "Earth", "current" : "Mars" }, "phone" : "1234567924" }
{ "_id" : ObjectId("5f855a92a8e8239c6a8022fb"), "firstName" : "Jolly", "secondName" : "LLB", "address" : { "permanent" : "Earth", "current" : "Saturn" }, "phone" : "1284167924" }

> db.roytuts.user.find({"address.permanent":/.*art.*/});
{ "_id" : ObjectId("5f855a90a8e8239c6a8022f8"), "firstName" : "Soumitra", "secondName" : "Roy", "address" : { "permanent" : "Earth", "current" : "Any" }, "phone" : "1234567890" }
{ "_id" : ObjectId("5f855a91a8e8239c6a8022f9"), "firstName" : "John", "secondName" : "Smith", "address" : { "permanent" : "Earth", "current" : "Jupitor" }, "phone" : "1234562890" }
{ "_id" : ObjectId("5f855a91a8e8239c6a8022fa"), "firstName" : "Ikra", "secondName" : "Michell", "address" : { "permanent" : "Earth", "current" : "Mars" }, "phone" : "1234567924" }
{ "_id" : ObjectId("5f855a92a8e8239c6a8022fb"), "firstName" : "Jolly", "secondName" : "LLB", "address" : { "permanent" : "Earth", "current" : "Saturn" }, "phone" : "1284167924" }

OR Condition

Matching any one of the given conditions will fetch records from database. In the AND condition you did not have to mention any AND operator but for OR condition you need to specify OR operator.

db.roytuts.user.find({"$or":[{firstName:"Ikra", "secondName":"Michell"}]});

The above query is equivalent to the following MySQL query:

SELECT * FROM roytuts.user WHERE firstName = "Ikra" or secondName = "Michell";

The above query will produce the following output:

> db.roytuts.user.find({"$or":[{firstName:"Ikra", "secondName":"Michell"}]});
{ "_id" : ObjectId("5f855a91a8e8239c6a8022fa"), "firstName" : "Ikra", "secondName" : "Michell", "address" : { "permanent" : "Earth", "current" : "Mars" }, "phone" : "1234567924" }

Let’s look at the another example.

db.roytuts.user.find({"$or":[{"address.permanent":"Earth", "phone":{"$gte":"1234567890"}}]});

The above query produces following output.

> db.roytuts.user.find({"$or":[{"address.permanent":"Earth", "phone":{"$gte":"1234567890"}}]});
{ "_id" : ObjectId("5f855a90a8e8239c6a8022f8"), "firstName" : "Soumitra", "secondName" : "Roy", "address" : { "permanent" : "Earth", "current" : "Any" }, "phone" : "1234567890" }
{ "_id" : ObjectId("5f855a91a8e8239c6a8022fa"), "firstName" : "Ikra", "secondName" : "Michell", "address" : { "permanent" : "Earth", "current" : "Mars" }, "phone" : "1234567924" }
{ "_id" : ObjectId("5f855a92a8e8239c6a8022fb"), "firstName" : "Jolly", "secondName" : "LLB", "address" : { "permanent" : "Earth", "current" : "Saturn" }, "phone" : "1284167924" }

IN Condition

This queries mainly improves multiple use of OR Queries.

db.roytuts.user.find({"address.current":{$in:["Jupitor", "Mars"]}});

The above query will produce output as follows:

> db.roytuts.user.find({"address.current":{$in:["Jupitor", "Mars"]}});
{ "_id" : ObjectId("5f855a91a8e8239c6a8022f9"), "firstName" : "John", "secondName" : "Smith", "address" : { "permanent" : "Earth", "current" : "Jupitor" }, "phone" : "1234562890" }
{ "_id" : ObjectId("5f855a91a8e8239c6a8022fa"), "firstName" : "Ikra", "secondName" : "Michell", "address" : { "permanent" : "Earth", "current" : "Mars" }, "phone" : "1234567924" }

That’s all about how to work with WHERE, AND, OR and IN operators while fetching data from MongoDB database.