MongoDB where query

MongoDB is one of the most popular NoSQL databases, which is open-source. Mongodb uses JSON format to query and filter out the data, which will be overwhelming for some users. In this blog, I will explain the MongoDB where query, and we will also understand how we can write other complex questions in mongodb.

In mongoDB $where operator is used to match documents that satisfy a JavaScript expression. Users can pass either a string or JavaScript expression or a JavaScript function. The $where gives the user greater flexibility but requires that the database processes the JavaScript expression or function for each document in the collection. More details about the where operator can be found here

The syntax for $where operates is

{ $where: <string|JavaScript Code> }

Mongodb where example

In this session, we will learn how to use MongoDB $where operator queries the data.

Insert the below data into the MongoDB student collection

l=[{
        "student_name" : "Peter",
        "sem" : "sem1",
        "english" : "B",
        "maths" : "A+",
        "science" : "B"
},
{
        "student_name" : "Jack",
        "sem" : "sem1",
        "english" : "A+",
        "maths" : "B+",
        "science" : "A"
},
{
        "student_name" : "Akash",
        "sem" : "sem1",
        "english" : "A+",
        "maths" : "A",
        "science" : "A+"
}]

type the below command to insert the data into the mongo collection

 db.student.insertMany(l)
{
	"acknowledged" : true,
	"insertedIds" : [
		ObjectId("6161907e576b620286b47d12"),
		ObjectId("6161907e576b620286b47d13"),
		ObjectId("6161907e576b620286b47d14")
	]
}

Now find such students where student science and English graders are the same using $where the operator

db.student.find( { $where: function() { return (this.english == this.science) }}).pretty();
{
	"_id" : ObjectId("6161907e576b620286b47d12"),
	"student_name" : "Peter",
	"sem" : "sem1",
	"english" : "B",
	"maths" : "A+",
	"science" : "B"
}
{
	"_id" : ObjectId("6161907e576b620286b47d14"),
	"student_name" : "Akash",
	"sem" : "sem1",
	"english" : "A+",
	"maths" : "A",
	"science" : "A+"
}

We have used this to filter out the data in the above example. We can also achieve the same result by using obj as well.

 db.student.find( { $where: function() { return (obj.english == obj.science)}}).pretty();

Another simple way to achieve the same result is by typing the below query.

db.student.find( "this.english == this.science").pretty();
{
	"_id" : ObjectId("6161907e576b620286b47d12"),
	"student_name" : "Peter",
	"sem" : "sem1",
	"english" : "B",
	"maths" : "A+",
	"science" : "B"
}
{
	"_id" : ObjectId("6161907e576b620286b47d14"),
	"student_name" : "Akash",
	"sem" : "sem1",
	"english" : "A+",
	"maths" : "A",
	"science" : "A+"
}

Fantastic, now in the subsequent sessions, we will understand how to query MongoDB data using various where conditions.

Mongodb find query

In MongoDB, a find query is used to retrieve the data from the mongo collection. The syntax for the find command follows the below pattern.

db.<collection_name>.find()

Type the below command to retrieve all data from the student collection

db.student.find()
{ "_id" : ObjectId("6161907e576b620286b47d12"), "student_name" : "Peter", "sem" : "sem1", "english" : "B", "maths" : "A+", "science" : "B" }
{ "_id" : ObjectId("6161907e576b620286b47d13"), "student_name" : "Jack", "sem" : "sem1", "english" : "A+", "maths" : "B+", "science" : "A" }
{ "_id" : ObjectId("6161907e576b620286b47d14"), "student_name" : "Akash", "sem" : "sem1", "english" : "A+", "maths" : "A", "science" : "A+" }

The find() command returns all the documents present in a particular collection; If you wish to retrieve only one document, you can use MongoDB findOne() command.

db.student.findOne()
{
	"_id" : ObjectId("6161907e576b620286b47d12"),
	"student_name" : "Peter",
	"sem" : "sem1",
	"english" : "B",
	"maths" : "A+",
	"science" : "B"
}

Mongodb find in the query

$in operator in MongoDB is used to search the document in the mongodb collection where the value of a field equals any value in the specified filter array.

The syntax of $in the operator follows the below pattern

{ field: { $in: [<value1>, <value2>, ... <valueN> ] } }

where

field: the document field you want to search in the mongo document.

<value1>, <value2>..<valueN> is the list of fields to be matched.

Now let’s use the find in() command to find students who scope A or A+ in math.

db.student.find({"maths": {$in: ["A","A+"]}})
{ "_id" : ObjectId("6161907e576b620286b47d12"), "student_name" : "Peter", "sem" : "sem1", "english" : "B", "maths" : "A+", "science" : "B" }
{ "_id" : ObjectId("6161907e576b620286b47d14"), "student_name" : "Akash", "sem" : "sem1", "english" : "A+", "maths" : "A", "science" : "A+" }

From the above output, it is clear that only Peter and Akash scored A or A+ in maths

I have written a detailed blog about how the mongodb find in() commands works. You can read this blog to understand the find in() command better.

Mongodb find in() command

Mongodb find specific field

Mongodb documents sometimes contain a lot of fields, and users might be interested in fetching only a few fields from the record. In mongodb, we can achieve this by setting up the field value to 1, which we want to retrieve. Let’s understand this with a simple example.

Ex-1: Fetch only the student_name from the student collection

db.student.find({},{"student_name":1})
{ "_id" : ObjectId("6161907e576b620286b47d12"), "student_name" : "Peter" }
{ "_id" : ObjectId("6161907e576b620286b47d13"), "student_name" : "Jack" }
{ "_id" : ObjectId("6161907e576b620286b47d14"), "student_name" : "Akash" }

The above query returned student_name along with _id this is because _id is the default field added to all documents. Use the below command if you wish to retrieve only student_name.

db.student.find({},{"student_name":1,_id:0})
{ "student_name" : "Peter" }
{ "student_name" : "Jack" }
{ "student_name" : "Akash" }

We have made _id:0 to return only student_name in the above command.

Similarly, you can use the below command to fetch all columns except the _id and sem columns.

 db.student.find({},{"_id":0,"sem":0})
{ "student_name" : "Peter", "english" : "B", "maths" : "A+", "science" : "B" }
{ "student_name" : "Jack", "english" : "A+", "maths" : "B+", "science" : "A" }
{ "student_name" : "Akash", "english" : "A+", "maths" : "A", "science" : "A+" }

Mongodb select query

Mongodb uses JSON syntax to query the receive the documents from the mongo collection. The JSON syntax is pretty different from the standard SQL commands.

To retrieve all records from a table, we write.

select * from student

In the same way, type the below command to retrieve all documents from the mongodb student collection.

db.student.find()
{ "_id" : ObjectId("6161907e576b620286b47d12"), "student_name" : "Peter", "sem" : "sem1", "english" : "B", "maths" : "A+", "science" : "B" }
{ "_id" : ObjectId("6161907e576b620286b47d13"), "student_name" : "Jack", "sem" : "sem1", "english" : "A+", "maths" : "B+", "science" : "A" }
{ "_id" : ObjectId("6161907e576b620286b47d14"), "student_name" : "Akash", "sem" : "sem1", "english" : "A+", "maths" : "A", "science" : "A+" }

In the same way, retrieve the document from the mongo collection where student_name=Peter

SQL query

select * from student where student_name="peter"

In mongodb, the same result can be achieved by typing

db.student.find({"student_name": "Peter"})
{ "_id" : ObjectId("6161907e576b620286b47d12"), "student_name" : "Peter", "sem" : "sem1", "english" : "B", "maths" : "A+", "science" : "B" }

In the same way, you can write more complex queries as well. I have written a few blogs which might help you to write some complex queries in mongodb

Mongodb sort() command

Mongodb connection strings

Mongodb Insert() command

Ways to create a collection in mongodb

Mongodb like query

Suppose the user wishes to write a query like

select * from student where student_name like "%J%"

In mongodb, this can be achieved by using $regex.It uses regular expression capabilities for pattern-matching strings in queries.

The $regex follows the below syntax.

{ <field>: /pattern/<options> }

Let’s understand the behavior of the$regex command by a simple.

Use case: Select records from student collection student_name contains a letter J

db.student.find({"student_name": /J/ })
{ "_id" : ObjectId("6161907e576b620286b47d13"), "student_name" : "Jack", "sem" : "sem1", "english" : "A+", "maths" : "B+", "science" : "A" }

In the same manner, select you can use$regex to find students whose name does not contain J

 db.student.find({"student_name":{$not: /J/ }})
{ "_id" : ObjectId("6161907e576b620286b47d12"), "student_name" : "Peter", "sem" : "sem1", "english" : "B", "maths" : "A+", "science" : "B" }
{ "_id" : ObjectId("6161907e576b620286b47d14"), "student_name" : "Akash", "sem" : "sem1", "english" : "A+", "maths" : "A", "science" : "A+" }

Conclusion

In this tutorial, we have studied the usage of the mongodb where command and understand how we can query mongodb using the where condition. We have also learned the usage of find, find in(), like, and select query.

I hope you like this tutorial; please do let me know if you face any issues while following along.