Select(find) query with like operator in MongoDB

Use of like operator in MongoDB

In Normal RDBMS we have fire normal select query from table employees:

select * from employees

In MongoDB fire select statement:

db.employees.find( {},{_id:0})

{ "name" : "abc123", "age" : 55, "status" : "A" }
{ "name" : "qwe", "age" : 20, "status" : "A" }
{ "name" : "zxc", "age" : 25, "status" : "A" }
{ "name" : "asd", "age" : 35, "status" : "A" }
{ "name" : "kkk", "age" : 23, "status" : "N" }

In MongoDB, we work as regular expression syntax: /pattern/
Syntax:

{ : { $regex: /pattern/ } }

Options
i: Case insensitivity to match upper and lower cases.
x: Extended capability to ignore all white space characters in the $regex.
m: For patterns that include (i.e. ^ for the start, $ for the end) match at the beginning or end of each line.
s: Allows the dot character (i.e. .) to match all characters including newline.

Example of fetch data with regular expression as like

-- In SQL RDBMS
select * from employees where name like 'ab%';
-- In MongoDB
db.employees.find( { name: /^ab.*/ } )

Example matching pattern use of %

-- In SQL RDBMS
select * from employees where name like '%123';
-- In MongoDB
db.employees.find( { name: { $regex: /123$/ } } )

Example matching case insensitive

db.employees.find( { name: { $regex: /^ABC/i } } )

i use in option as case insensitive functionality

Example to match multiple line Match for Lines Starting with Specified Pattern

db.employees.find( { name: { $regex: /^S/, $options: 'm' } } )

m is used for multiple line

Example use s option to allow the dot character (i.e. .) to match all characters including new line

-- Start with m character and use in multiple line having LINE in it.
db.employees.find( { address: { $regex: /m.*line/, $options: 's' } } )

s option use to allow dot char to match all characters in new line

Example use Ignore White Spaces in Pattern

var pattern = "abc #category code\n123 #item number"
db.employees.find( { name: { $regex: pattern, $options: "x" } } )

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.