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