Master several uses of regex in MongoDB in 3 minutes
Master several uses of regex in MongoDB in 3 minutes
Background
Part1: write at the front
Friends who use MySQL or other relational databases know that the use of fuzzy queries is similar to:
SELECT * FROM products WHERE sku like "789"
The regex in MongoDB introduced in this article implements a similar function. Regex enables you to use regular expressions in your queries. This article will show you how to use regex in MongoDB with simple examples.
Part2: usage
When using $regex, there are several uses:
{: {$regex: / pattern/, $options:''}} {: {$regex: 'pattern', $options:''} {: {$regex: / pattern/}}
The meaning of the option parameter:
Use requirements for the meaning of options
I is not case-sensitive
Anchors are used in m query matching, such as ^ (for the beginning) and $(for the end), and the string after the match\ n
X ignores all white space characters
Require $regex to share with $option s to allow dot characters (.) to match all characters, including newline characters. Require $regex to be shared with $option
Actual combat
Usage in Part1:$in
To include regular expressions in a $in query, you can only use the JavaScript regular expression object (that is, / pattern /). For example:
{name: {$in: [/ ^ acme/i, / ^ ack/]}}
Warning: warning that $regex operator expressions cannot be used in $in.
Part2: implicit and usage
To include regular expressions in comma-separated query conditions, use the $regex operator. For example:
{name: {$regex: / acme.*corp/i, $nin: ['acmeblahcorp']}} {name: {$regex: / acme.*corp/, $options:' acmeblahcorp', $nin: ['acmeblahcorp']}} {name: {$regex:' acme.*corp', $options: 'nin: [' acmeblahcorp']}
Part3:x and s option
To use the x option or the s option, $regex is required to be used with $option. For example, to specify the I and s options, you must use $options to do the following:
{name: {$regex: / acme.*corp/, $options: "si"} {name: {$regex: 'acme.*corp', $options: "si"}
Part4: the use of indexes
For case-sensitive regular expression queries, if the field has an index, MongoDB matches the regular expression to the value in the index, which is faster than a 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 should also conform to the leftmost prefix principle; for example, the regular expression / ^ abc.*/ will be optimized by matching only index values that start with abc.
Warning: warning
1. Although / ^ a _ gray / matches the equivalent string, their performance is not the same. If there is a corresponding index, all of these expressions use the index; however, / ^ a.indexes / and / ^ a.indexes examples / are slower. This is because / ^ a / can stop scanning after matching the prefix.
two。 Case-insensitive regular expression queries usually cannot use indexes, and $regex cannot use case-insensitive indexes.
Part5: instance
In a collection of goods, the following are stored
{"_ id": 100, "sku": "abc123", "description": "Single line description."} {"_ id": 101, "sku": "abc789", "description": "First line\ nSecond line"} {"_ id": 102, "sku": "xyz456", "description": "Many spaces before line" {"_ id": 103, "sku": "xyz789" "description": "Multiple\ nline description"}
If you want to execute a query against the products collection of the item, the scope is that the content in the sku column ends with 789:
Db.products.find ({sku: {$regex: / 789 $/}})
Combined with the understanding of MySQL, the above query looks like this SQL in MySQL:
SELECT * FROM products WHERE sku like "789"
If you want to query sku with the beginning of abc and ABC, and ignore case when matching, you can use the I option:
Db.products.find ({sku: {$regex: / ^ ABC/i}}),
The query results are as follows:
{"_ id": 100, "sku": "abc123", "description": "Single line description."} {"_ id": 101,101, "sku": "abc789", "description": "First line\ nSecond line"}
The use of Part6:m
If you want to include S in the query description, and to match the S after / n, you need to add the m option.
Db.products.find ({description: {$regex: / ^ Smax, $options:'m'}})
The result returned is:
{"_ id": 100, "sku": "abc123", "description": "Single line description."} {"_ id": 101,101, "sku": "abc789", "description": "First line\ nSecond line"}
If you do not add the m option, the result returned is as follows:
{"_ id": 100, "sku": "abc123", "description": "Single line description."}
If an anchor such as ^ 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"}
The use of Part7:s
If you use the s option to execute the query, you will have a comma. Matches all characters, including newline characters. The following query starts with m in the description column, followed by the result of the line string:
Db.products.find ({description: {$regex: / m.options: 'si'}}) {"_ id": 102, "sku": "xyz456", "description": "Many spaces before line"} {"_ id": 103, "sku": "xyz789", "description": "Multiple\ nline description"}
If s is not included, it returns:
{"_ id": 102,102, "sku": "xyz456", "description": "Many spaces before line"}
The use of Part8:x
The following example uses the x option to ignore spaces and comments, denotes comments with #, and ends with\ nin the match pattern:
Var pattern = "abc # category code\ n123 # item number" db.products.find ({sku: {$regex: pattern, $options: "x"}})
The result of the query is:
{"_ id": 100, "sku": "abc123", "description": "Single line description."}
As you can see, it ignores the spaces of abc and # category and the spaces of # category and code, and the query actually executed is the result of sku being abc123.
-- Summary.
Through these examples, we can learn about the use of regex in MongoDB, as well as the meaning and usage of each option of its optional parameter $option. As the author's level is limited and the writing time is very short, it is inevitable that there will be some errors or inaccuracies in the article. I urge readers to criticize and correct them. Like the author's article, click a wave of attention in the upper right corner, thank you.
