MongoDB query to perform a LIKE match - Example

This article explains you how to write a mongo query to perform a LIKE match in MongoDB. Note, MongoDB does not have “LIKE” operator like you have in SQL which can be used in a WHERE clause to search for a specified pattern in a column.

First, let’s insert the following documents in the collection called “posts” for our example

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}]})

SQL LIKE Syntax

Let’s consider the below SQL LIKE statement used to find all the records where by_user column is like “%s”

select *
from posts
where by_user like '%s'

Mongo LIKE Query Example

Below is the mongo query to match all the documents where by_user field is like “%s”

> db.posts.find({by_user: {$regex: /s$/}}).pretty()

{
        "_id" : ObjectId("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" : ISODate("2019-02-25T02:15:00Z"),
                        "like" : 10
                },
                {
                        "user" : "Paul",
                        "message" : "Good one",
                        "dateCreated" : ISODate("2019-02-25T02:15:00Z"),
                        "like" : 12
                }
        ]
}

What is $regex in Mongo?

It provides regular expression capabilities for pattern matching strings in queries. Mongo uses Perl compatible regular expressions (PCRE)

You can use any one of the following syntaxes to use $regex:

{ <field>: { $regex: /pattern/, $options: '<options>' } }
{ <field>: { $regex: 'pattern', $options: '<options>' } }
{ <field>: { $regex: /pattern/<options> } }

$options

The following are the options that are available for use with regular expressions

i – case insensitivity to match upper and lower cases

m – For patterns that include anchors (i.e. ^ for the start and $ for the end)

x – Extended capability to ignore all the white space characters in the $regex pattern

s – Allows the dot (.) character to match all the characters including newline characters.

$regex Vs. /pattern/ Syntax:

$regex has some restrictions for particular syntax use. For example, you cannot use $regex operator expressions inside an $in. So mongo allows you to use regular expressions objects as shown below to specify regular expressions.

Example for $in expressions

> db.posts.find({by_user: { $in: [/^sne/i, /^mon/]}}).pretty()

Syntax:

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

Try to run the following mongo command and check the ouput

> db.posts.find({by_user: /s/}).pretty()

Further Learning

MongoDB query to perform a LIKE match – Example

SQL LIKE Syntax

Mongo LIKE Query Example

What is $regex in Mongo?

$regex Vs. /pattern/ Syntax:

References