MongoDB Fuzzy query operation Case Details like and not like of of relational database
- 2020-12-22 17:49:58
- OfStack
1. Function and grammar description
Effect: A regular expression is a string used to describe and match a series of strings that conform to a syntactic rule. Many programming languages support string manipulation using regular expressions. MongoDB uses the $regex operator to set regular expressions that match strings.
Grammar 1
{ <field>: { $regex: /pattern/, $options: '<options>' } }
{ <field>: { $regex: 'pattern', $options: '<options>' } }
{ <field>: { $regex: /pattern/<options> } }
Syntax 2
{ <field>: /pattern/<options> }
2. Case demonstration
Suppose MongoDB contains the message information of our OrderService service, and the data is as follows:
(The following 9 documents are used for the demonstration)
{
"_id" : ObjectId("5d305b1c4857fc49c0c14c81"),
"order" : "QQ00001",
"data" : "\"<?xml version='1.0' encoding='UTF-8'?><Response service=\\\"OrderService\\\"><Head>ERR</Head><ERROR> Repeat order </ERROR></Response>\""
},
{
"_id" : ObjectId("5d305b3b4857fc49c0c14c82"),
"order" : "QQ00001",
"data" : "\"<?xml version='1.0' encoding='UTF-8'?><Response service=\\\"OrderService\\\"><Head>ERR</Head><ERROR> Repeat order </ERROR></Response>\""
},
{
"_id" : ObjectId("5d305b404857fc49c0c14c83"),
"order" : "QQ00002",
"data" : "\"<?xml version='1.0' encoding='UTF-8'?><Response service=\\\"OrderService\\\"><Head>ERR</Head><ERROR> Repeat order </ERROR></Response>\""
},
{
"_id" : ObjectId("5d305b454857fc49c0c14c84"),
"order" : "QQ00002",
"data" : "\"<?xml version='1.0' encoding='UTF-8'?><Response service=\\\"OrderService\\\"><Head>ERR</Head><ERROR> Repeat order </ERROR></Response>\""
},
{
"_id" : ObjectId("5d305b4b4857fc49c0c14c85"),
"order" : "QQ00003",
"data" : "\"<?xml version='1.0' encoding='UTF-8'?><Response service=\\\"OrderService\\\"><Head>ERR</Head><ERROR> Repeat order </ERROR></Response>\""
},
{
"_id" : ObjectId("5d305b4f4857fc49c0c14c86"),
"order" : "QQ00003",
"data" : "\"<?xml version='1.0' encoding='UTF-8'?><Response service=\\\"OrderService\\\"><Head>ERR</Head><ERROR> Repeat order </ERROR></Response>\""
},
{
"_id" : ObjectId("5d305bb74857fc49c0c14c87"),
"order" : "QQ00003",
"data" : "\"<?xml version='1.0' encoding='UTF-8'?><Response service=\\\"OrderService\\\"><Head>OK</Head><Body><OrderResponse filter_mmm=\\\"2\\\" destcity=\\\"shenzheng\\\" origincode=\\\"QQ0003\\\" /></Body></Response>\""
},
{
"_id" : ObjectId("5d305bd14857fc49c0c14c88"),
"order" : "QQ00002",
"data" : "\"<?xml version='1.0' encoding='UTF-8'?><Response service=\\\"OrderService\\\"><Head>OK</Head><Body><OrderResponse filter_mmm=\\\"2\\\" destcity=\\\"zhongshan\\\" origincode=\\\"QQ0002\\\" /></Body></Response>\""
},
{
"_id" : ObjectId("5d305be94857fc49c0c14c89"),
"order" : "QQ00001",
"data" : "\"<?xml version='1.0' encoding='UTF-8'?><Response service=\\\"OrderService\\\"><Head>OK</Head><Body><OrderResponse filter_mmm=\\\"2\\\" destcity=\\\"zhongshan\\\" origincode=\\\"QQ0001\\\" /></Body></Response>\""
}
Requirement: Query data field contains characters < Head > OK < /Head > The document
The execution code is as follows:
db.dbtestregex.find({data:{ $regex:/<Head>OK<\/Head>/}})
The \ in the query code is a shift character, as is the following query statement.
The results returned are as follows:
{
"_id" : ObjectId("5d305bb74857fc49c0c14c87"),
"order" : "QQ00003",
"data" : "\"<?xml version='1.0' encoding='UTF-8'?><Response service=\\\"OrderService\\\"><Head>OK</Head><Body><OrderResponse filter_mmm=\\\"2\\\" destcity=\\\"shenzheng\\\" origincode=\\\"QQ0003\\\" /></Body></Response>\""
},
{
"_id" : ObjectId("5d305bd14857fc49c0c14c88"),
"order" : "QQ00002",
"data" : "\"<?xml version='1.0' encoding='UTF-8'?><Response service=\\\"OrderService\\\"><Head>OK</Head><Body><OrderResponse filter_mmm=\\\"2\\\" destcity=\\\"zhongshan\\\" origincode=\\\"QQ0002\\\" /></Body></Response>\""
},
{
"_id" : ObjectId("5d305be94857fc49c0c14c89"),
"order" : "QQ00001",
"data" : "\"<?xml version='1.0' encoding='UTF-8'?><Response service=\\\"OrderService\\\"><Head>OK</Head><Body><OrderResponse filter_mmm=\\\"2\\\" destcity=\\\"zhongshan\\\" origincode=\\\"QQ0001\\\" /></Body></Response>\""
}
The above query command can also be rewritten as:
db.dbtestregex.find({data:/<Head>OK<\/Head>/})
The query results are as follows:
{
"_id" : ObjectId("5d305bb74857fc49c0c14c87"),
"order" : "QQ00003",
"data" : "\"<?xml version='1.0' encoding='UTF-8'?><Response service=\\\"OrderService\\\"><Head>OK</Head><Body><OrderResponse filter_mmm=\\\"2\\\" destcity=\\\"shenzheng\\\" origincode=\\\"QQ0003\\\" /></Body></Response>\""
},
{
"_id" : ObjectId("5d305bd14857fc49c0c14c88"),
"order" : "QQ00002",
"data" : "\"<?xml version='1.0' encoding='UTF-8'?><Response service=\\\"OrderService\\\"><Head>OK</Head><Body><OrderResponse filter_mmm=\\\"2\\\" destcity=\\\"zhongshan\\\" origincode=\\\"QQ0002\\\" /></Body></Response>\""
},
{
"_id" : ObjectId("5d305be94857fc49c0c14c89"),
"order" : "QQ00001",
"data" : "\"<?xml version='1.0' encoding='UTF-8'?><Response service=\\\"OrderService\\\"><Head>OK</Head><Body><OrderResponse filter_mmm=\\\"2\\\" destcity=\\\"zhongshan\\\" origincode=\\\"QQ0001\\\" /></Body></Response>\""
}
It can also be written as:
db.dbtestregex.find({data:{ $regex:' < Head > OK < \/Head > '}})
Its implementation function 1 sample.
3. Realization of not like function in relational database
Reverse queries, while not common, are a must for us at DBA. Let's do a case study to see how to implement not like query in MongoDB.
Not included in the data field of the requirement query < Head > OK < /Head > The document
The execution script is as follows:
db.dbtestregex.find({data:{ $not: /<Head>OK<\/Head>/ }})
The returned document is as follows;
{
"_id" : ObjectId("5d305b1c4857fc49c0c14c81"),
"order" : "QQ00001",
"data" : "\"<?xml version='1.0' encoding='UTF-8'?><Response service=\\\"OrderService\\\"><Head>ERR</Head><ERROR> Repeat order </ERROR></Response>\""
},
{
"_id" : ObjectId("5d305b3b4857fc49c0c14c82"),
"order" : "QQ00001",
"data" : "\"<?xml version='1.0' encoding='UTF-8'?><Response service=\\\"OrderService\\\"><Head>ERR</Head><ERROR> Repeat order </ERROR></Response>\""
},
{
"_id" : ObjectId("5d305b404857fc49c0c14c83"),
"order" : "QQ00002",
"data" : "\"<?xml version='1.0' encoding='UTF-8'?><Response service=\\\"OrderService\\\"><Head>ERR</Head><ERROR> Repeat order </ERROR></Response>\""
},
{
"_id" : ObjectId("5d305b454857fc49c0c14c84"),
"order" : "QQ00002",
"data" : "\"<?xml version='1.0' encoding='UTF-8'?><Response service=\\\"OrderService\\\"><Head>ERR</Head><ERROR> Repeat order </ERROR></Response>\""
},
{
"_id" : ObjectId("5d305b4b4857fc49c0c14c85"),
"order" : "QQ00003",
"data" : "\"<?xml version='1.0' encoding='UTF-8'?><Response service=\\\"OrderService\\\"><Head>ERR</Head><ERROR> Repeat order </ERROR></Response>\""
},
{
"_id" : ObjectId("5d305b4f4857fc49c0c14c86"),
"order" : "QQ00003",
"data" : "\"<?xml version='1.0' encoding='UTF-8'?><Response service=\\\"OrderService\\\"><Head>ERR</Head><ERROR> Repeat order </ERROR></Response>\""
}
More content can refer to the official documentation https: / / docs mongodb. com/manual/reference/operator query/regex / # op _S_regex
conclusion