MongoDB equivalent command for SQL’s between numbers, text or dates

SQL’s between operator selects values within a given range. The values can be numbers, text, or dates. And we know that the between operator also includes start and end values. Mongo does not have between operator, so let’s see how to achieve MongoDB between numbers, text or dates in this tutorial.

Let’s insert the following documents in the collection called “posts”

db.posts.insert({"_id":"5063114bd386d8fadbd6b004","title":"MongoDB OverView","description":"Overview and Introduction about MongoDB","by_user":"mongodb","url":"www.docs.mongodb.com","tags":["#mongodb","#mongo_tutor","#tutorials"],"likes":20,"comments":[{"user":"Mishra","message":"Nice tutorial","dateCreated":"2011-02-25T02:15:00.000Z","like":10},{"user":"Sonam","message":"Not completely correct. Partially right.","dateCreated":"2011-02-25T02:15:00.000Z","like":12}]})
db.posts.insert({"_id":"5063114bd386d8fadbd6b005","title":"MongoDB OverView 2","description":"2 Overview and Introduction about MongoDB","by_user":"mongodb","url":"www.docs.mongodb.com","tags":["#mongodb","#mongo_tutor","#tutorials"],"likes":30,"comments":[{"user":"Mishra","message":"Nice tutorial","dateCreated":"2011-02-25T02:15:00.000Z","like":10},{"user":"Sonam","message":"Not completely correct. Partially right.","dateCreated":"2011-02-25T02:15:00.000Z","like":12}]})
db.posts.insert({"_id":"5063114bd386d8fadbd6b008","title":"MongoDB Advantages","description":"Advantages of MongoDB","by_user":"sneppets","url":"https://docs.mongodb.com","tags":["#mongodb","#mongo_tutor","#tutorials"],"likes":50,"comments":[{"user":"John","message":"Nice tutorial","dateCreated":"2019-02-25T02:15:00.000Z","like":10},{"user":"Paul","message":"Good one","dateCreated":"2019-02-25T02:15:00.000Z","like":12}]})

1: MongoDB between numbers or text

Numbers and Text could be dealt in the same way as shown in the following examples i.e., you can use the combination of $gte and $lte comparisons operator in the expressions to achive SQL’s between operator in MongoDB.

The following are the two ways to achieve this operation.

Example 1:

> db.posts.find({$and: [{“likes”: {$gte: 25}},{“likes”:{$lte: 40}}]}).pretty()

{
        "_id" : ObjectId("5063114bd386d8fadbd6b005"),
        "title" : "MongoDB OverView 2",
        "description" : "2 Overview and Introduction about MongoDB",
        "by_user" : "mongodb",
        "url" : "www.docs.mongodb.com",
        "tags" : [
                "#mongodb",
                "#mongo_tutor",
                "#tutorials"
        ],
        "likes" : 30,
        "comments" : [
                {
                        "user" : "Mishra",
                        "message" : "Nice tutorial",
                        "dateCreated" : ISODate("2011-02-25T02:15:00Z"),
                        "like" : 10
                },
                {
                        "user" : "Sonam",
                        "message" : "Not completely correct. Partially right.",
                        "dateCreated" : ISODate("2011-02-25T02:15:00Z"),
                        "like" : 12
                }
        ]
}

Example 2:

> db.posts.find({“likes”: {$gte:25, $lte: 40}}).pretty()

{
        "_id" : ObjectId("5063114bd386d8fadbd6b005"),
        "title" : "MongoDB OverView 2",
        "description" : "2 Overview and Introduction about MongoDB",
        "by_user" : "mongodb",
        "url" : "www.docs.mongodb.com",
        "tags" : [
                "#mongodb",
                "#mongo_tutor",
                "#tutorials"
        ],
        "likes" : 30,
        "comments" : [
                {
                        "user" : "Mishra",
                        "message" : "Nice tutorial",
                        "dateCreated" : ISODate("2011-02-25T02:15:00Z"),
                        "like" : 10
                },
                {
                        "user" : "Sonam",
                        "message" : "Not completely correct. Partially right.",
                        "dateCreated" : ISODate("2011-02-25T02:15:00Z"),
                        "like" : 12
                }
        ]
}

2: MongoDB between dates or timestamp

Let’s assume you have another property called “timestamp” in each of the documents in the collection and you wanted to find the entries between two dates then you could try fetching entries in the following way.

>db.posts.find({ timestamp: {$gte: ISODate(“2011-02-25T02:15:00Z”), $lte: ISODate(“2011-02-29T02:15:00Z”)}})

Further Learning

1: MongoDB between numbers or text

2: MongoDB between dates or timestamp

References