九、mongodb之评估查询运算符

淩亂°似流年 2022-05-14 12:57 260阅读 0赞

\#                             评估查询运算符

<table> 
 <thead> 
  <tr> 
   <th align="center">运算符</th> 
   <th align="center">含义</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td align="center">$expr</td> 
   <td align="center">允许在查询语言中使用聚合表达式</td> 
  </tr> 
  <tr> 
   <td align="center">$jsonSchema</td> 
   <td align="center">根据给定的JSON模式验证文档，限定数据类型。</td> 
  </tr> 
  <tr> 
   <td align="center">$mod</td> 
   <td align="center">对字段的值执行模运算，并选择具有指定结果的文档。</td> 
  </tr> 
  <tr> 
   <td align="center">$regex</td> 
   <td align="center">选择值与指定正则表达式匹配的文档</td> 
  </tr> 
  <tr> 
   <td align="center">$text</td> 
   <td align="center">执行文本搜索</td> 
  </tr> 
  <tr> 
   <td align="center">$where</td> 
   <td align="center">匹配满足JavaScript表达式的文档。</td> 
  </tr> 
 </tbody> 
</table>

现有数据：

{ "_id" : 1, "category" : "food", "budget": 400, "spent": 450 }
    { "_id" : 2, "category" : "drinks", "budget": 100, "spent": 150 }
    { "_id" : 3, "category" : "clothes", "budget": 100, "spent": 50 }
    { "_id" : 4, "category" : "misc", "budget": 500, "spent": 300 }
    { "_id" : 5, "category" : "travel", "budget": 200, "spent": 650 }

\#\#\#1、$expr运算符

使用$expr运算符联合多个运算，选出budget值大于spent值的数据
    
    db.monthlyBudget.find( { $expr: { $gt: [ "$spent" , "$budget" ] } } )

\#\#\#2、$jsonSchema运算符

db.createCollection("students", {
       validator: {
          $jsonSchema: {
             bsonType: "object",
             required: [ "name", "year", "major", "gpa" ],
             properties: {
                name: {
                   bsonType: "string",
                   description: "must be a string and is required"
                },
                gender: {
                   bsonType: "string",
                   description: "must be a string and is not required"
                },
                year: {
                   bsonType: "int",
                   minimum: 2017,
                   maximum: 3017,
                   exclusiveMaximum: false,
                   description: "must be an integer in [ 2017, 3017 ] and is required"
                },
                major: {
                   enum: [ "Math", "English", "Computer Science", "History", null ],
                   description: "can only be one of the enum values and is required"
                },
                gpa: {
                   bsonType: [ "double" ],
                   description: "must be a double and is required"
                }
             }
          }
       }
    })

\#\#\#3、$mod运算符

取出qty的值，对4取余等于0的数据：
    
    db.inventory.find( { qty: { $mod: [ 4, 0 ] } } )

\#\#\#4、$regex运算符

SELECT * FROM products WHERE sku like "%789";
    db.products.find( { sku: { $regex: /789$/ } } )
    
    匹配S开头，或者含有换行符的Bson
    db.products.find( { description: { $regex: /^S/, $options: 'm' } } )

Option ===== Description
    
    参数 i ====== 加了这个参数，表示不区分大小写
    
    参数 m ===== 个人理解这个参数是用来匹配value中有换行符(\n)的情形。 
    
    参数 s ===== 允许点字符（.）匹配所有的字符，包括换行符。
    
    参数 x ====== 官网的大意是忽视空白字符。

\#\#\#5、$text 运算符

匹配文本中含有coffee的BSON
    
    db.articles.find( { $text: { $search: "coffee" } } )

\#\#\#6、$where运算符

匹配name的值经过md5函数加密后匹配：
    
    db.foo.find( { $where: function() {
       return (hex_md5(this.name) == "9b53e667f30cd329dca1ec9e6a83e994")
    } } );

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25ld2JpZV85MDc0ODY4NTI_size_16_color_FFFFFF_t_70_pic_center][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L25ld2JpZV85MDc0ODY4NTI_size_16_color_FFFFFF_t_70_pic_center]: /images/20220514/f7510f4f5a044ad29295a73aba1e8a1a.png