Elastic search查詢

建模和準(zhǔn)備數(shù)據(jù)

由于賽事機(jī)構(gòu)的參加人員較多，且使用比較頻繁，為了避免單一索引引起頻繁操作，這里我們構(gòu)建動(dòng)態(tài)索引。索引的命名規(guī)則為：mock_data3_ + agencyId.

為了保證檢索的效率，采用冗余的模型。在document中，建立nested的類型。（其原因如下：1. 對(duì)業(yè)務(wù)使用場(chǎng)景來說，對(duì)查詢的效率要求較高；使用parent child的關(guān)聯(lián)，其效率為單一文檔的十分之一左右；2. 冗余的數(shù)據(jù)，在elastic search中保存在磁盤，都經(jīng)過壓縮，且磁盤空間代價(jià)較??；3. 對(duì)較大的文檔，可能在建立index的耗時(shí)相對(duì)較長(zhǎng)，這是業(yè)務(wù)可以接受的情形。）

相關(guān)的配置如下：

1. 配置命令

PUT mock_data3_180103
{
  "mappings": {
    "doc": {
      "properties": {
        "registrations": {
          "type": "nested" 
        }
      }
    }
  }
}

2. 建立index

PUT mock_data3_180103/doc/08C0B894-5CEE-4787-9DBB-00014256B7CE
{
  "agencyId": 180103,
  "addOnRevenue": 0.00,
  "openRate": 0,
  "emailRevenue": 0.00,
  "lastName":"Allabach",
  "address":{
    "city":"Centreville",
    "postalCode":"49032",
    "stateProvince":"MI",
    "line1":"27095 Marvin Rd"
   },
  "registrations": [
      {
        "eventName": "color run 2018",
        "priceCategoryName": "run 12k",
        "eventId": 12323,
        "priceCategoryId": 456,
        "id":123,
        "orderNumber": "C-0000119C",
        "registrationNumber": "R-00000DYB",
        "age": 18,
        "eventStartDate": "2009-07-20 20:07:52",
        "eventEndDate": "2018-07-21 20:07:52",
        "registrationDate": "2009-07-21 20:0:52",
        "activeUrl": "http://www.active.com",
        "location": "test location",
        "division": "division",
        "bib": "111111111111111111111",
        "wave": "tessdsdsds",
        "category": "registration category",
        "sport": "Running",
        "distance": "Marathon"
      },
      { 
        "eventName": "eventName",
        "priceCategoryName": "priceCategoryName",
        "eventId": 2382323,
        "priceCategoryId": 32322,
        "id":456,
        "orderNumber": "C-00002323C",
        "registrationNumber": "R-089DYB",
        "age": 20,
        "eventStartDate": "2010-07-20 20:07:52",
        "eventEndDate": "2018-07-21 20:07:52",
        "registrationDate": "2009-07-21 20:0:52",
        "activeUrl": "http://www.active.com",
        "location": "test location",
        "division": "division",
        "bib": "111111111111111111111",
        "wave": "tessdsdsds",
        "teamId": 123456,
        "teamName": "test team",
        "teamCaptainName": "test team captain",
        "category": "registration category",
        "sport": "Running",
        "distance": "5K"
      }
  ],  
  "gender":"FEMALE",
  "registrationRevenue": 29.99,
  "clickedCount":0,
  "clickedRate": 0,
  "dateOfBirth":"1975-09-11",
  "stars": 3,
  "sendCount":1,
  "engagementRate":"F",
  "firstName":"Shellie",
  "revenue": 29.99,
  "phoneNumber":"2697182152",
  "registrationCount":1,
  "openCount":0,
  "email":"MSSQLSERVERB128D6A6@null.activenetwork.com",
  "agencyName": "the color run"
}

Elastic search查詢相關(guān)命令

簡(jiǎn)單的嵌套查詢

GET mock_data3_180103/_search
{
  "query": {
    "nested": {
      "path": "registrations",
      "query": {
        "bool": {
          "must": [
            { "match": { "registrations.eventName": "color run 2018" }},
            { "match": { "registrations.eventId":  123232112 }} 
          ]
        }
      }
    }
  }
}

布爾過濾器編輯

一個(gè) bool 過濾器由三部分組成：

{
   "bool" : {
      "must" :     [],
      "should" :   [],
      "must_not" : [],
   }
}

must
所有的語句都 必須（must） 匹配，與 AND 等價(jià)。

must_not
所有的語句都 不能（must not） 匹配，與 NOT 等價(jià)。

should
至少有一個(gè)語句要匹配，與 OR 等價(jià)。

范圍

Elasticsearch 有 range 查詢

gt: > 大于（greater than）

lt: < 小于（less than）

gte: >= 大于或等于（greater than or equal to）

lte: <= 小于或等于（less than or equal to）

缺失查詢編輯

這個(gè) missing 查詢本質(zhì)上與 exists 恰好相反： 它返回某個(gè)特定 無 值字段的文檔，與以下 SQL 表達(dá)的意思類似：

GET /my_store/products/_search
{
   "query" : {
      "filtered" : { 
         "filter" : {
            "bool" : {
              "should" : [
                 { "term" : {"price" : 20}}, 
                 { "term" : {"productID" : "XHDK-A-1293-#fJ3"}} 
              ],
              "must_not" : {
                 "term" : {"price" : 30} 
              }
           }
         }
      }
   }
}

案列

1. 查詢滿足以下條件：

eventId= 2382323

stars between 3 to 30

first name like Shellie

GET mock_data3_180103/_search
{
  "query": {
    "bool": {
      "must": [
         {"nested": {
            "path": "registrations",
            "query": {
              "bool": {
                "must": [
                  { "match": { "registrations.eventId": 2382323 }}
                ]
              }
            }
           }
          },
          {"match": {
            "firstName": {
                  "query": "Shellie",
                  "type":  "phrase"
              }
            }
          },
          {"range": {
            "stars": {
              "gte": 3,
              "lte": 30
            }
          }
            
        }
      ] 
      
    }
  }
}