Regex usage in MongoDB

Regex usage in MongoDB

Background

Part1: Preface

Anyone who uses MySQL or other relational databases knows that fuzzy queries are used in similar ways:
SELECT * FROM products WHERE sku like "% 789 ";

The regex in MongoDB described in this article implements similar functions. regex enables you to use regular expressions in queries. This article will use a simple example to show you how to use regex in MongoDB ~

Part2: Usage

When using $ regex, you can use the following methods:
{<Field >:{ $ regex:/pattern/, $ options: '<options> '}}
{<Field >:{ $ regex: 'pattern', $ options: '<options> '}}
{<Field >:{$ regex:/pattern/<options> }}

Meaning of option parameter:

Option	Description	Usage requirements
i	Case Insensitive
m	An anchor is used for query matching, such as ^ (indicating the beginning), $ (indicating the end), and a string matching \ n.
x	Ignore all blank characters	$ Regex and $ option are required.
s	The dot character (.) is allowed to match all characters, including line breaks.	$ Regex and $ option are required.

Practice

Part1: $ in usage

To include a regular expression in a $ in query, you can only use a JavaScript Regular Expression object (that is,/pattern /). For example:
{Name: {$ in: [/^ acme/I,/^ ack/]}

Warning: Warning $ in cannot use the $ regex operator expression.

Part2: Implicit and usage

To include a regular expression in a comma-separated query condition, use the $ regex operator. For example:
{Name: {$ regex:/acme. * corp/I, $ nin: ['acmeblahcorp ']}
{Name: {$ regex:/acme. * corp/, $ options: 'I', $ nin: ['acmeblahcorp ']}
{Name: {$ regex: 'acme. * corp ', $ options:' I ', $ nin: ['acmeblahcorp']}

Part3: x and s options

To use the x or s option, use $ regex with $ option. For example, to specify the I and s options, you must use $ options to perform the following operations:
{Name: {$ regex:/acme. * corp/, $ options: "si "}}
{Name: {$ regex: 'acme. * corp ', $ options: "si "}}

Part4: Index usage

For case-sensitive regular expression queries, if the field has an index, MongoDB matches the regular expression with the value in the index, which is faster than the full table scan. If the regular expression is a "prefix expression", the query speed can be optimized and the query results will start with the same string.

Regular Expressions must also comply with the "leftmost prefix principle". For example, regular expressions/^ abc. */will be optimized by matching only the index values starting with abc.

Warning: Warning

1. Although/^ a/,/^ a. */and/^ a. * $/match equivalent strings, their performance is different. If a corresponding index exists, all these expressions use indexes. However,/^ a. */and/^ a. * $/are slow. This is because/^ a/can stop scanning after matching the prefix.

2. indexes cannot be used in case-insensitive Regular Expression queries. $ regex cannot use case-insensitive indexes.

Part5: instance

The following content is saved in a collection of items:

{"_ Id": 100, "sku": "abc123", "description": "Single line description ."}
{"_ Id": 101, "sku": "abc789", "description": "First line \ nSecond line "}
{"_ Id": 102, "sku": "xyz456", "description": "spaces before line "}
{"_ Id": 103, "sku": "xyz789", "description": "Multiple \ nline description "}

If you want to perform a query on the products set of the product, the range is that the content in the sku column is ending with 789:

Db. products. find ({sku :{$ regex:/789 $ /}})

In combination with MySQL, the preceding query uses the following SQL statement in MySQL:
SELECT * FROM products WHERE sku like "% 789 ";

You can use the I option to query sku names starting with abc and case-insensitive when matching:
Db. products. find ({sku: {$ regex:/^ ABC/I }}),

The query result is:
{"_ Id": 100, "sku": "abc123", "description": "Single line description ."}
{"_ Id": 101, "sku": "abc789", "description": "First line \ nSecond line "}

Part6: Use of m

To query a description that starts with "S" and matches the description with "S" after "/n", you must add the "m" option.
Db. products. find ({description: {$ regex:/^ S/, $ options: 'M '}})

The returned result is:
{"_ Id": 100, "sku": "abc123", "description": "Single line description ."}
{"_ Id": 101, "sku": "abc789", "description": "First line \ nSecond line "}

If the m option is not added, the returned result is as follows:
{"_ Id": 100, "sku": "abc123", "description": "Single line description ."}

If ^ is not used, all results will be returned:
Db. products. find ({description: {$ regex:/S /}})
{"_ Id": 100, "sku": "abc123", "description": "Single line description ."}
{"_ Id": 101, "sku": "abc789", "description": "First line \ nSecond line "}

Part7: Use of s

If you use the "s" option to perform a query, a comma (.) will be used to match all characters, including line breaks. The following describes the results of a line string that starts with m in the description column:
Db. products. find ({description: {$ regex:/m. * line/, $ options: 'si '}})
{"_ Id": 102, "sku": "xyz456", "description": "spaces before line "}
{"_ Id": 103, "sku": "xyz789", "description": "Multiple \ nline description "}

If s is not included, return:

{"_ Id": 102, "sku": "xyz456", "description": "spaces before line "}

Part8: Use of x

The following example uses the x option to ignore spaces and comments, # To indicate comments, and end with \ n in matching mode:
Var pattern = "abc # category code \ n123 # item number"
Db. products. find ({sku: {$ regex: pattern, $ options: "x "}})

The query result is:
{"_ Id": 100, "sku": "abc123", "description": "Single line description ."}

It can be seen that the space between abc and # category and the space between # category and code are ignored. The actual query result is that sku is the result of abc123.

-- Conclusion --

Through these cases, we can understand the regex usage in MongoDB and the meaning and usage of each option of its optional parameter $ option.

For more MongoDB tutorials, see the following:

Introduction to MongoDB documents, collections, and databases

MongoDB 3 shard deployment and Fault Simulation

Install MongoDB in CentOS 6.5 yum

Quick installation of MongoDB using CentOS 7 yum

MongoDB query operations

Quickly build MongoDB clusters on Azure virtual machines

Principles of MongoDB replica set

MongoDB 3.4 remote connection authentication failed

Install MongoDB3.4 database system in Ubuntu 16.04

MongoDB authoritative guide 2nd PDF complete with bookmarks directory download see

MongoDB details: click here
MongoDB: click here