ElasticSearch 7.X版本19個常用的查詢語句

PassZhang發表於2020-08-11

整理一篇常用的CRUD查詢語句，之前這篇檔案是在17年左右發表的，從英文翻譯過來，現在採用7.x 版本進行實驗，棄用的功能或者引數，我這邊會進行更新，一起來學習吧。

為了演示不同型別的 ElasticSearch 的查詢，我們將使用書文件資訊的集合（有以下欄位：title（標題）, authors（作者）, summary（摘要）, publish_date（釋出日期）和 num_reviews（瀏覽數））。

在這之前，首先我們應該先建立一個新的索引（index），並批量匯入一些文件：

建立索引：

PUT /bookdb_index
    { "settings": { "number_of_shards": 1 }}

批量上傳文件：

注意：現在7.x 已經啟用types 型別了，對應的操作語句也要修改下，POST /bookdb_index/book/_bulk換成POST /bookdb_index/_bulk ，然後進行操作。

POST /bookdb_index/_bulk
    { "index": { "_id": 1 }}
    { "title": "Elasticsearch: The Definitive Guide", "authors": ["clinton gormley", "zachary tong"], "summary" : "A distibuted real-time search and analytics engine", "publish_date" : "2015-02-07", "num_reviews": 20, "publisher": "oreilly" }
    { "index": { "_id": 2 }}
    { "title": "Taming Text: How to Find, Organize, and Manipulate It", "authors": ["grant ingersoll", "thomas morton", "drew farris"], "summary" : "organize text using approaches such as full-text search, proper name recognition, clustering, tagging, information extraction, and summarization", "publish_date" : "2013-01-24", "num_reviews": 12, "publisher": "manning" }
    { "index": { "_id": 3 }}
    { "title": "Elasticsearch in Action", "authors": ["radu gheorge", "matthew lee hinman", "roy russo"], "summary" : "build scalable search applications using Elasticsearch without having to do complex low-level programming or understand advanced data science algorithms", "publish_date" : "2015-12-03", "num_reviews": 18, "publisher": "manning" }
    { "index": { "_id": 4 }}
    { "title": "Solr in Action", "authors": ["trey grainger", "timothy potter"], "summary" : "Comprehensive guide to implementing a scalable search engine using Apache Solr", "publish_date" : "2014-04-05", "num_reviews": 23, "publisher": "manning" }

例子：

1. 基本的匹配（Query）查詢

有兩種方式來執行一個全文匹配查詢：

使用 Search Lite API，它從 url 中讀取所有的查詢引數
使用完整 JSON 作為請求體，這樣你可以使用完整的 Elasticsearch DSL

下面是一個基本的匹配查詢，查詢任一欄位包含 Guide 的記錄

GET /bookdb_index/_search?q=guide

[Results]
"hits": [
      {
        "_index": "bookdb_index",
        "_type": "book",
        "_id": "1",
        "_score": 0.28168046,
        "_source": {
          "title": "Elasticsearch: The Definitive Guide",
          "authors": ["clinton gormley", "zachary tong"],
          "summary": "A distibuted real-time search and analytics engine",
          "publish_date": "2015-02-07",
          "num_reviews": 20,
          "publisher": "manning"
        }
      },
      {
        "_index": "bookdb_index",
        "_type": "book",
        "_id": "4",
        "_score": 0.24144039,
        "_source": {
          "title": "Solr in Action",
          "authors": ["trey grainger", "timothy potter"],
          "summary": "Comprehensive guide to implementing a scalable search engine using Apache Solr",
          "publish_date": "2014-04-05",
          "num_reviews": 23,
          "publisher": "manning"
        }
      }
    ]

下面是完整 Search Profiler版本的查詢，生成相同的內容：

{
    "query": {
        "multi_match" : {
            "query" : "guide",
            "fields" : [ "*" ]
        }
    }
}

multi_match 是 match 的作為在多個欄位執行相同操作的一個速記法。fields 屬性用來指定查詢針對的欄位，*代表所有欄位，同時也可以使用單個欄位進行查詢，用逗號分隔開就可以。

在這個例子中，我們想要對文件的所有欄位進行匹配。兩個 API 都允許你指定要查詢的欄位。例如，查詢 title 欄位中包含 in Action 的書：

GET /bookdb_index/_search?q=title:in action

[Results]
"hits": [
      {
        "_index": "bookdb_index",
        "_type": "book",
        "_id": "4",
        "_score": 0.6259885,
        "_source": {
          "title": "Solr in Action",
          "authors": [
            "trey grainger",
            "timothy potter"
          ],
          "summary": "Comprehensive guide to implementing a scalable search engine using Apache Solr",
          "publish_date": "2014-04-05",
          "num_reviews": 23,
          "publisher": "manning"
        }
      },
      {
        "_index": "bookdb_index",
        "_type": "book",
        "_id": "3",
        "_score": 0.5975345,
        "_source": {
          "title": "Elasticsearch in Action",
          "authors": [
            "radu gheorge",
            "matthew lee hinman",
            "roy russo"
          ],
          "summary": "build scalable search applications using Elasticsearch without having to do complex low-level programming or understand advanced data science algorithms",
          "publish_date": "2015-12-03",
          "num_reviews": 18,
          "publisher": "manning"
        }
      }
    ]

然而，完整的 DSL 給予你靈活建立更復雜查詢和指定返回結果的能力（後面，我們會一一闡述）。在下面例子中，我們指定 size 限定返回的結果條數，from 指定起始位子，_source 指定要返回的欄位，以及語法高亮

POST /bookdb_index/_search
{
    "query": {
        "match" : {
            "title" : "in action"
        }
    },
    "size": 2,
    "from": 0,
    "_source": [ "title", "summary", "publish_date" ],
    "highlight": {
        "fields" : {
            "title" : {}
        }
    }
}

[Results]
"hits": {
    "total": 2,
    "max_score": 0.9105287,
    "hits": [
      {
        "_index": "bookdb_index",
        "_type": "book",
        "_id": "3",
        "_score": 0.9105287,
        "_source": {
          "summary": "build scalable search applications using Elasticsearch without having to do complex low-level programming or understand advanced data science algorithms",
          "title": "Elasticsearch in Action",
          "publish_date": "2015-12-03"
        },
        "highlight": {
          "title": [
            "Elasticsearch <em>in</em> <em>Action</em>"
          ]
        }
      },
      {
        "_index": "bookdb_index",
        "_type": "book",
        "_id": "4",
        "_score": 0.9105287,
        "_source": {
          "summary": "Comprehensive guide to implementing a scalable search engine using Apache Solr",
          "title": "Solr in Action",
          "publish_date": "2014-04-05"
        },
        "highlight": {
          "title": [
            "Solr <em>in</em> <em>Action</em>"
          ]
        }
      }
    ]
  }

注意：對於多個詞查詢，match 允許指定是否使用 and 操作符來取代預設的 or 操作符。你還可以指定 mininum_should_match 選項來調整返回結果的相關程度。具體看後面的例子。

2. 多欄位（Multi-filed）查詢

正如我們已經看到來的，為了根據多個欄位檢索（e.g. 在 title 和 summary 欄位都是相同的查詢字串的結果），你可以使用 multi_match 語句

POST /bookdb_index/_search
{
    "query": {
        "multi_match" : {
            "query" : "elasticsearch guide",
            "fields": ["title", "summary"]
        }
    }
}

[Results]
"hits": {
    "total": 3,
    "max_score": 0.9448582,
    "hits": [
      {
        "_index": "bookdb_index",
        "_type": "book",
        "_id": "1",
        "_score": 0.9448582,
        "_source": {
          "title": "Elasticsearch: The Definitive Guide",
          "authors": [
            "clinton gormley",
            "zachary tong"
          ],
          "summary": "A distibuted real-time search and analytics engine",
          "publish_date": "2015-02-07",
          "num_reviews": 20,
          "publisher": "manning"
        }
      },
      {
        "_index": "bookdb_index",
        "_type": "book",
        "_id": "3",
        "_score": 0.17312013,
        "_source": {
          "title": "Elasticsearch in Action",
          "authors": [
            "radu gheorge",
            "matthew lee hinman",
            "roy russo"
          ],
          "summary": "build scalable search applications using Elasticsearch without having to do complex low-level programming or understand advanced data science algorithms",
          "publish_date": "2015-12-03",
          "num_reviews": 18,
          "publisher": "manning"
        }
      },
      {
        "_index": "bookdb_index",
        "_type": "book",
        "_id": "4",
        "_score": 0.14965448,
        "_source": {
          "title": "Solr in Action",
          "authors": [
            "trey grainger",
            "timothy potter"
          ],
          "summary": "Comprehensive guide to implementing a scalable search engine using Apache Solr",
          "publish_date": "2014-04-05",
          "num_reviews": 23,
          "publisher": "manning"
        }
      }
    ]
  }

注：第三條被匹配，因為 guide 在 summary 欄位中被找到。

3. Boosting

由於我們是多個欄位查詢，我們可能需要提高某一個欄位的分值。在下面的例子中，我們把 summary 欄位的分數提高三倍，為了提升 summary 欄位的重要度；因此，我們把文件 4 的相關度提高了。

POST /bookdb_index/_search
{
    "query": {
        "multi_match" : {
            "query" : "elasticsearch guide",
            "fields": ["title", "summary^3"]
        }
    },
    "_source": ["title", "summary", "publish_date"]
}

[Results]
"hits": [
      {
        "_index": "bookdb_index",
        "_type": "book",
        "_id": "1",
        "_score": 0.31495273,
        "_source": {
          "summary": "A distibuted real-time search and analytics engine",
          "title": "Elasticsearch: The Definitive Guide",
          "publish_date": "2015-02-07"
        }
      },
      {
        "_index": "bookdb_index",
        "_type": "book",
        "_id": "4",
        "_score": 0.14965448,
        "_source": {
          "summary": "Comprehensive guide to implementing a scalable search engine using Apache Solr",
          "title": "Solr in Action",
          "publish_date": "2014-04-05"
        }
      },
      {
        "_index": "bookdb_index",
        "_type": "book",
        "_id": "3",
        "_score": 0.13094766,
        "_source": {
          "summary": "build scalable search applications using Elasticsearch without having to do complex low-level programming or understand advanced data science algorithms",
          "title": "Elasticsearch in Action",
          "publish_date": "2015-12-03"
        }
      }
    ]

注：提升不是簡簡單單通過提升因子把計算分數加成。實際的 boost 值通過歸一化和一些內部優化給出的。相關資訊請見 Elasticsearch guide

4. Bool 組合查詢

為了提供更相關或者特定的結果，AND/OR/NOT 操作符可以用來調整我們的查詢。它是以 布林查詢 的方式來實現的。布林查詢 接受如下引數：

must 等同於 AND
must_not 等同於 NOT
should 等同於 OR

上面的關鍵字中在一個query中只能出現一次

打比方，如果我想要查詢這樣型別的書：書名包含 ElasticSearch 或者（OR） Solr，並且（AND）它的作者是 Clinton Gormley 不是（NOT）Radu Gheorge

POST /bookdb_index/_search
{
  "query": {
    "bool": {
      "must":     { "match": { "authors": "clinton gormely" }},
      "must_not": { "match": { "authors": "radu gheorge"  }},
      "should": [
                  { "match": { "title": "Elasticsearch" }},
                  { "match": { "title": "Solr"   }}
      ]
    }
  }
}

格式化版本：
POST /bookdb_index/_search
{
  "query": {
    "bool": {
      "must": {
        "match": {
          "authors": "clinton gormely"
        }
      },
      "must_not": {
        "match": {
          "authors": "radu gheorge"
        }
      },
      "should": [
        {
          "match": {
            "title": "Elasticsearch"
          }
        },
        {
          "match": {
            "title": "Solr"
          }
        }
      ]
    }
  }
}

[Results]
"hits": [
      {
        "_index": "bookdb_index",
        "_type": "book",
        "_id": "1",
        "_score": 0.3672021,
        "_source": {
          "title": "Elasticsearch: The Definitive Guide",
          "authors": [
            "clinton gormley",
            "zachary tong"
          ],
          "summary": "A distibuted real-time search and analytics engine",
          "publish_date": "2015-02-07",
          "num_reviews": 20,
          "publisher": "oreilly"
        }
      }
    ]

注：正如你所看到的，布林查詢 可以包裝任何其他查詢型別，包括其他布林查詢，以建立任意複雜或深度巢狀的查詢。

5. 模糊（Fuzzy）查詢

在進行匹配和多項匹配時，可以啟用模糊匹配來捕捉拼寫錯誤，模糊度是基於原始單詞的編輯距離來指定的。

POST /bookdb_index/_search
{
    "query": {
        "multi_match" : {
            "query" : "comprihensiv guide",
            "fields": ["title", "summary"],
            "fuzziness": "AUTO"
        }
    },
    "_source": ["title", "summary", "publish_date"],
    "size": 1
}


[Results]
"hits": [
      {
        "_index": "bookdb_index",
        "_type": "book",
        "_id": "4",
        "_score": 0.5961596,
        "_source": {
          "summary": "Comprehensive guide to implementing a scalable search engine using Apache Solr",
          "title": "Solr in Action",
          "publish_date": "2014-04-05"
        }
      }
]

注：當術語長度大於 5 個字元時，AUTO 的模糊值等同於指定值 “2”。但是，80％拼寫錯誤的編輯距離為 1，所以，將模糊值設定為 1 可能會提高您的整體搜尋效能。更多詳細資訊，請參閱Elasticsearch指南中的“排版和拼寫錯誤”（Typos and Misspellings）。

6. 萬用字元（Wildcard）查詢

萬用字元查詢 允許你指定匹配的模式，而不是整個術語。

？ 匹配任何字元
* 匹配零個或多個字元。

例如，要查詢名稱以字母’t’開頭的所有作者的記錄：

POST /bookdb_index/_search
{
    "query": {
        "wildcard" : {
            "authors" : "t*"
        }
    },
    "_source": ["title", "authors"],
    "highlight": {
        "fields" : {
            "authors" : {}
        }
    }
}

[Results]
"hits": [
      {
        "_index": "bookdb_index",
        "_type": "book",
        "_id": "1",
        "_score": 1,
        "_source": {
          "title": "Elasticsearch: The Definitive Guide",
          "authors": [
            "clinton gormley",
            "zachary tong"
          ]
        },
        "highlight": {
          "authors": [
            "zachary <em>tong</em>"
          ]
        }
      },
      {
        "_index": "bookdb_index",
        "_type": "book",
        "_id": "2",
        "_score": 1,
        "_source": {
          "title": "Taming Text: How to Find, Organize, and Manipulate It",
          "authors": [
            "grant ingersoll",
            "thomas morton",
            "drew farris"
          ]
        },
        "highlight": {
          "authors": [
            "<em>thomas</em> morton"
          ]
        }
      },
      {
        "_index": "bookdb_index",
        "_type": "book",
        "_id": "4",
        "_score": 1,
        "_source": {
          "title": "Solr in Action",
          "authors": [
            "trey grainger",
            "timothy potter"
          ]
        },
        "highlight": {
          "authors": [
            "<em>trey</em> grainger",
            "<em>timothy</em> potter"
          ]
        }
      }
    ]

7. 正則（Regexp）查詢

正則查詢 讓你可以使用比 萬用字元查詢 更復雜的模式進行查詢：

POST /bookdb_index/_search
{
    "query": {
        "regexp" : {
            "authors" : "t[a-z]*y"
        }
    },
    "_source": ["title", "authors"],
    "highlight": {
        "fields" : {
            "authors" : {}
        }
    }
}

[Results]
"hits": [
      {
        "_index": "bookdb_index",
        "_type": "book",
        "_id": "4",
        "_score": 1,
        "_source": {
          "title": "Solr in Action",
          "authors": [
            "trey grainger",
            "timothy potter"
          ]
        },
        "highlight": {
          "authors": [
            "<em>trey</em> grainger",
            "<em>timothy</em> potter"
          ]
        }
      }
    ]

8. 短語匹配(Match Phrase)查詢

短語匹配查詢 要求在請求字串中的所有查詢項必須都在文件中存在，文中順序也得和請求字串一致，且彼此相連。預設情況下，查詢項之間必須緊密相連，但可以設定 slop 值來指定查詢項之間可以分隔多遠的距離，結果仍將被當作一次成功的匹配。

POST /bookdb_index/_search
{
    "query": {
        "multi_match" : {
            "query": "search engine",
            "fields": ["title", "summary"],
            "type": "phrase",
            "slop": 3
        }
    },
    "_source": [ "title", "summary", "publish_date" ]
}

[Results]
"hits": [
      {
        "_index": "bookdb_index",
        "_type": "book",
        "_id": "4",
        "_score": 0.22327082,
        "_source": {
          "summary": "Comprehensive guide to implementing a scalable search engine using Apache Solr",
          "title": "Solr in Action",
          "publish_date": "2014-04-05"
        }
      },
      {
        "_index": "bookdb_index",
        "_type": "book",
        "_id": "1",
        "_score": 0.16113183,
        "_source": {
          "summary": "A distibuted real-time search and analytics engine",
          "title": "Elasticsearch: The Definitive Guide",
          "publish_date": "2015-02-07"
        }
      }
    ]

注：在上述例子中，對於非整句型別的查詢，_id 為 1 的文件一般會比 _id 為 4 的文件得分高，結果位置也更靠前，因為它的欄位長度較短，但是對於 短語匹配型別 查詢，由於查詢項之間的接近程度是一個計算因素，因此 _id 為 4 的文件得分更高。

9. 短語字首（Match Phrase Prefix）查詢

短語字首式查詢 能夠進行 即時搜尋（search-as-you-type） 型別的匹配，或者說提供一個查詢時的初級自動補全功能，無需以任何方式準備你的資料。和 match_phrase 查詢類似，它接收slop 引數（用來調整單詞順序和不太嚴格的相對位置）和 max_expansions 引數（用來限制查詢項的數量，降低對資源需求的強度）。

POST /bookdb_index/_search
{
    "query": {
        "match_phrase_prefix" : {
            "summary": {
                "query": "search en",
                "slop": 3,
                "max_expansions": 10
            }
        }
    },
    "_source": [ "title", "summary", "publish_date" ]
}

[Results]
"hits": [
      {
        "_index": "bookdb_index",
        "_type": "book",
        "_id": "4",
        "_score": 0.5161346,
        "_source": {
          "summary": "Comprehensive guide to implementing a scalable search engine using Apache Solr",
          "title": "Solr in Action",
          "publish_date": "2014-04-05"
        }
      },
      {
        "_index": "bookdb_index",
        "_type": "book",
        "_id": "1",
        "_score": 0.37248808,
        "_source": {
          "summary": "A distibuted real-time search and analytics engine",
          "title": "Elasticsearch: The Definitive Guide",
          "publish_date": "2015-02-07"
        }
      }
    ]

注：採用 查詢時即時搜尋 具有較大的效能成本。更好的解決方案是採用 索引時即時搜尋。更多資訊，請檢視 自動補齊介面（Completion Suggester API） 或 邊緣分詞器（Edge-Ngram filters）的用法。

10. 查詢字串（Query String）

查詢字串 型別（query_string）的查詢提供了一個方法，用簡潔的簡寫語法來執行 多匹配查詢、 布林查詢 、 提權查詢、 模糊查詢、 萬用字元查詢、 正則查詢 和範圍查詢。下面的例子中，我們在那些作者是 “grant ingersoll” 或 “tom morton” 的某本書當中，使用查詢項 “search algorithm” 進行一次模糊查詢，搜尋全部欄位，但給 summary 的權重提升 2 倍。

POST /bookdb_index/_search
{
  "query": {
    "query_string": {
      "query": "(saerch~1 algorithm~1) AND (grant ingersoll)  OR (tom morton)",
      "fields": [
        "*",
        "summary^2"
      ]
    }
  },
  "_source": [
    "title",
    "summary",
    "authors"
  ],
  "highlight": {
    "fields": {
      "summary": {}
    }
  }
}


[Results]
"hits": [
      {
        "_index": "bookdb_index",
        "_type": "book",
        "_id": "2",
        "_score": 0.14558059,
        "_source": {
          "summary": "organize text using approaches such as full-text search, proper name recognition, clustering, tagging, information extraction, and summarization",
          "title": "Taming Text: How to Find, Organize, and Manipulate It",
          "authors": [
            "grant ingersoll",
            "thomas morton",
            "drew farris"
          ]
        },
        "highlight": {
          "summary": [
            "organize text using approaches such as full-text <em>search</em>, proper name recognition, clustering, tagging, information extraction, and summarization"
          ]
        }
      }
    ]

11. 簡單查詢字串（Simple Query String）

簡單請求字串 型別（simple_query_string）的查詢是請求字串型別（query_string）查詢的一個版本，它更適合那種僅暴露給使用者一個簡單搜尋框的場景；因為它用 +/\|/- 分別替換了 AND/OR/NOT，並且自動丟棄了請求中無效的部分，不會在使用者出錯時，丟擲異常。

POST /bookdb_index/_search
{
    "query": {
        "simple_query_string" : {
            "query": "(saerch~1 algorithm~1) + (grant ingersoll)  | (tom morton)",
            "fields": ["*", "summary^2"]
        }
    },
    "_source": [ "title", "summary", "authors" ],
    "highlight": {
        "fields" : {
            "summary" : {}
        }
    }
} 


[Results]
  "hits" : {
    "total" : {
      "value" : 1,
      "relation" : "eq"
    },
    "max_score" : 3.5710216,
    "hits" : [
      {
        "_index" : "bookdb_index",
        "_type" : "book",
        "_id" : "2",
        "_score" : 3.5710216,
        "_source" : {
          "summary" : "organize text using approaches such as full-text search, proper name recognition, clustering, tagging, information extraction, and summarization",
          "title" : "Taming Text: How to Find, Organize, and Manipulate It",
          "authors" : [
            "grant ingersoll",
            "thomas morton",
            "drew farris"
          ]
        },
        "highlight" : {
          "summary" : [
            "organize text using approaches such as full-text <em>search</em>, proper name recognition, clustering, tagging"
          ]
        }
      }
    ]
  }

12. 詞條（Term）/多詞條（Terms）查詢

以上例子均為 full-text(全文檢索) 的示例。有時我們對結構化查詢更感興趣，希望得到更準確的匹配並返回結果，詞條查詢 和 多詞條查詢 可幫我們實現。在下面的例子中，我們要在索引中找到所有由 Manning 出版的圖書。

POST /bookdb_index/_search
{
    "query": {
        "term" : {
            "publisher": "manning"
        }
    },
    "_source" : ["title","publish_date","publisher"]
}

[Results]
"hits": [
      {
        "_index": "bookdb_index",
        "_type": "book",
        "_id": "2",
        "_score": 1.2231436,
        "_source": {
          "publisher": "manning",
          "title": "Taming Text: How to Find, Organize, and Manipulate It",
          "publish_date": "2013-01-24"
        }
      },
      {
        "_index": "bookdb_index",
        "_type": "book",
        "_id": "3",
        "_score": 1.2231436,
        "_source": {
          "publisher": "manning",
          "title": "Elasticsearch in Action",
          "publish_date": "2015-12-03"
        }
      },
      {
        "_index": "bookdb_index",
        "_type": "book",
        "_id": "4",
        "_score": 1.2231436,
        "_source": {
          "publisher": "manning",
          "title": "Solr in Action",
          "publish_date": "2014-04-05"
        }
      }
    ]

可使用詞條關鍵字來指定多個詞條，將搜尋項用陣列傳入。

{
    "query": {
        "terms" : {
            "publisher": ["oreilly", "packt"]
        }
    }
}

13. 詞條（Term）查詢 - 排序（Sorted）

詞條查詢 的結果（和其他查詢結果一樣）可以被輕易排序，多級排序也被允許：

POST /bookdb_index/_search
{
  "query": {
    "term": {
      "publisher": "manning"
    }
  },
  "_source": [
    "publish_date",
    "publisher"
  ],
  "sort": [
    {
      "publish_date": {
        "order": "desc"
      }
    }
  ]
}



[Results]
  "hits" : {
    "total" : {
      "value" : 3,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [
      {
        "_index" : "bookdb_index",
        "_type" : "book",
        "_id" : "3",
        "_score" : null,
        "_source" : {
          "publisher" : "manning",
          "publish_date" : "2015-12-03"
        },
        "sort" : [
          1449100800000
        ]
      },
      {
        "_index" : "bookdb_index",
        "_type" : "book",
        "_id" : "4",
        "_score" : null,
        "_source" : {
          "publisher" : "manning",
          "publish_date" : "2014-04-05"
        },
        "sort" : [
          1396656000000
        ]
      },
      {
        "_index" : "bookdb_index",
        "_type" : "book",
        "_id" : "2",
        "_score" : null,
        "_source" : {
          "publisher" : "manning",
          "publish_date" : "2013-01-24"
        },
        "sort" : [
          1358985600000
        ]
      }
    ]
  }

14. 範圍查詢

另一個結構化查詢的例子是 範圍查詢。在這個例子中，我們要查詢 2015 年出版的書。

POST /bookdb_index/_search
{
  "query": {
    "range": {
      "publish_date": {
        "gte": "2015-01-01",
        "lte": "2015-12-31"
      }
    }
  },
  "_source": [
    "title",
    "publish_date",
    "publisher"
  ]
}


[Results]
"hits": [
      {
        "_index": "bookdb_index",
        "_type": "book",
        "_id": "1",
        "_score": 1,
        "_source": {
          "publisher": "oreilly",
          "title": "Elasticsearch: The Definitive Guide",
          "publish_date": "2015-02-07"
        }
      },
      {
        "_index": "bookdb_index",
        "_type": "book",
        "_id": "3",
        "_score": 1,
        "_source": {
          "publisher": "manning",
          "title": "Elasticsearch in Action",
          "publish_date": "2015-12-03"
        }
      }
    ]

注：範圍查詢 用於日期、數字和字串型別的欄位。

15. 過濾(Filtered)查詢

過濾查詢允許你可以過濾查詢結果。對於我們的例子中，要在標題或摘要中檢索一些書，查詢項為 Elasticsearch，但我們又想篩出那些僅有 20 個以上評論的。

新版本不支援filtered 查詢，已經棄用這個關鍵字

POST /bookdb_index/_search
{
    "query": {
        "filtered": {
            "query" : {
                "multi_match": {
                    "query": "elasticsearch",
                    "fields": ["title","summary"]
                }
            },
            "filter": {
                "range" : {
                    "num_reviews": {
                        "gte": 20
                    }
                }
            }
        }
    },
    "_source" : ["title","summary","publisher", "num_reviews"]
}


[Results]
"hits": [
      {
        "_index": "bookdb_index",
        "_type": "book",
        "_id": "1",
        "_score": 0.5955761,
        "_source": {
          "summary": "A distibuted real-time search and analytics engine",
          "publisher": "oreilly",
          "num_reviews": 20,
          "title": "Elasticsearch: The Definitive Guide"
        }
      }
    ]

注：過濾查詢 並不強制它作用於其上的查詢必須存在。如果未指定查詢，match_all 基本上會返回索引內的全部文件。實際上，過濾只在第一次執行，以減少所需的查詢面積，並且，在第一次使用後過濾會被快取，大大提高了效能。

更新：過濾查詢 將在 ElasticSearch 5 中移除，使用 布林查詢 替代。下面有個例子使用 布林查詢 重寫上面的例子：

POST /bookdb_index/_search
{
    "query": {
        "bool": {
            "must" : {
                "multi_match": {
                    "query": "elasticsearch",
                    "fields": ["title","summary"]
                }
            },
            "filter": {
                "range" : {
                    "num_reviews": {
                        "gte": 20
                    }
                }
            }
        }
    },
    "_source" : ["title","summary","publisher", "num_reviews"]
}

在後續的例子中，我們將會把它使用在 多重過濾 中。

16. 多重過濾（Multiple Filters）

多重過濾 可以結合 布林查詢 使用，下一個例子中，過濾查詢決定只返回那些包含至少20條評論，且必須在 2015 年前出版，且由 O’Reilly 出版的結果。

POST /bookdb_index/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "title": "Elasticsearch"
          }
        }
      ],
      "filter": [
        {
          "term": {
            "publisher": "oreilly"
          }
        },
        {
          "range": {
            "publish_date": {
              "gte": "2014-12-31"
            }
          }
        }
      ]
    }
  },
  "_source": [
    "title",
    "publisher",
    "publish_date"
  ]
}




[Results]
"hits": [
      {
        "_index": "bookdb_index",
        "_type": "book",
        "_id": "1",
        "_score": 0.5955761,
        "_source": {
          "summary": "A distibuted real-time search and analytics engine",
          "publisher": "oreilly",
          "num_reviews": 20,
          "title": "Elasticsearch: The Definitive Guide",
          "publish_date": "2015-02-07"
        }
      }
    ]

17. 作用分值: 域值（Field Value）因子

也許在某種情況下，你想把文件中的某個特定域作為計算相關性分值的一個因素，比較典型的場景是你想根據普及程度來提高一個文件的相關性。在我們的示例中，我們想把最受歡迎的書（基於評論數判斷）的權重進行提高，可使用 field_value_factor 用以影響分值。

POST /bookdb_index/_search
{
    "query": {
        "function_score": {
            "query": {
                "multi_match" : {
                    "query" : "search engine",
                    "fields": ["title", "summary"]
                }
            },
            "field_value_factor": {
                "field" : "num_reviews",
                "modifier": "log1p",
                "factor" : 2
            }
        }
    },
    "_source": ["title", "summary", "publish_date", "num_reviews"]
}

[Results]
"hits": [
      {
        "_index": "bookdb_index",
        "_type": "book",
        "_id": "1",
        "_score": 0.44831306,
        "_source": {
          "summary": "A distibuted real-time search and analytics engine",
          "num_reviews": 20,
          "title": "Elasticsearch: The Definitive Guide",
          "publish_date": "2015-02-07"
        }
      },
      {
        "_index": "bookdb_index",
        "_type": "book",
        "_id": "4",
        "_score": 0.3718407,
        "_source": {
          "summary": "Comprehensive guide to implementing a scalable search engine using Apache Solr",
          "num_reviews": 23,
          "title": "Solr in Action",
          "publish_date": "2014-04-05"
        }
      },
      {
        "_index": "bookdb_index",
        "_type": "book",
        "_id": "3",
        "_score": 0.046479136,
        "_source": {
          "summary": "build scalable search applications using Elasticsearch without having to do complex low-level programming or understand advanced data science algorithms",
          "num_reviews": 18,
          "title": "Elasticsearch in Action",
          "publish_date": "2015-12-03"
        }
      },
      {
        "_index": "bookdb_index",
        "_type": "book",
        "_id": "2",
        "_score": 0.041432835,
        "_source": {
          "summary": "organize text using approaches such as full-text search, proper name recognition, clustering, tagging, information extraction, and summarization",
          "num_reviews": 12,
          "title": "Taming Text: How to Find, Organize, and Manipulate It",
          "publish_date": "2013-01-24"
        }
      }
    ]

注1: 我們可能剛執行了一個常規的 multi_match (多匹配)查詢，並對 num_reviews 域進行了排序，這讓我們失去了評估相關性分值的好處。

注2: 有大量的附加引數可用來調整提升原始相關性分值效果的程度，比如 modifier, factor, boost_mode 等等，至於細節可在 Elasticsearch 指南中探索。

18. 作用分值: 衰變（Decay）函式

假設不想使用域值做遞增提升，而你有一個理想目標值，並希望用這個加權因子來對這個離你較遠的目標值進行衰減。有個典型的用途是基於經緯度、價格或日期等數值域的提升。在如下的例子中，我們查詢在2014年6月左右出版的，查詢項是 search engines 的書。

POST /bookdb_index/_search
{
    "query": {
        "function_score": {
            "query": {
                "multi_match" : {
                    "query" : "search engine",
                    "fields": ["title", "summary"]
                }
            },
            "functions": [
                {
                    "exp": {
                        "publish_date" : {
                            "origin": "2014-06-15",
                            "offset": "7d",
                            "scale" : "30d"
                        }
                    }
                }
            ],
            "boost_mode" : "replace"
        }
    },
    "_source": ["title", "summary", "publish_date", "num_reviews"]
}

[Results]
"hits": [
      {
        "_index": "bookdb_index",
        "_type": "book",
        "_id": "4",
        "_score": 0.27420625,
        "_source": {
          "summary": "Comprehensive guide to implementing a scalable search engine using Apache Solr",
          "num_reviews": 23,
          "title": "Solr in Action",
          "publish_date": "2014-04-05"
        }
      },
      {
        "_index": "bookdb_index",
        "_type": "book",
        "_id": "1",
        "_score": 0.005920768,
        "_source": {
          "summary": "A distibuted real-time search and analytics engine",
          "num_reviews": 20,
          "title": "Elasticsearch: The Definitive Guide",
          "publish_date": "2015-02-07"
        }
      },
      {
        "_index": "bookdb_index",
        "_type": "book",
        "_id": "2",
        "_score": 0.000011564,
        "_source": {
          "summary": "organize text using approaches such as full-text search, proper name recognition, clustering, tagging, information extraction, and summarization",
          "num_reviews": 12,
          "title": "Taming Text: How to Find, Organize, and Manipulate It",
          "publish_date": "2013-01-24"
        }
      },
      {
        "_index": "bookdb_index",
        "_type": "book",
        "_id": "3",
        "_score": 0.0000059171475,
        "_source": {
          "summary": "build scalable search applications using Elasticsearch without having to do complex low-level programming or understand advanced data science algorithms",
          "num_reviews": 18,
          "title": "Elasticsearch in Action",
          "publish_date": "2015-12-03"
        }
      }
    ]

19. 函式分值: 指令碼評分

當內建的評分函式無法滿足你的需求時，還可以用 Groovy 指令碼。在我們的例子中，想要指定一個指令碼，能在決定把 num_reviews 的因子計算多少之前，先將 publish_date 考慮在內。因為很新的書也許不會有評論，分值不應該被懲罰。

評分指令碼如下：

publish_date = doc['publish_date'].value
num_reviews = doc['num_reviews'].value

if (publish_date > Date.parse('yyyy-MM-dd', threshold).getTime()) {
  my_score = Math.log(2.5 + num_reviews)
} else {
  my_score = Math.log(1 + num_reviews)
}
return my_score

在 script_score 引數內動態呼叫評分指令碼：

POST /bookdb_index/book/_search
{
    "query": {
        "function_score": {
            "query": {
                "multi_match" : {
                    "query" : "search engine",
                    "fields": ["title", "summary"]
                }
            },
            "functions": [
                {
                    "script_score": {
                        "params" : {
                            "threshold": "2015-07-30"
                        },
                        "script": "publish_date = doc['publish_date'].value; num_reviews = doc['num_reviews'].value; if (publish_date > Date.parse('yyyy-MM-dd', threshold).getTime()) { return log(2.5 + num_reviews) }; return log(1 + num_reviews);"
                    }
                }
            ]
        }
    },
    "_source": ["title", "summary", "publish_date", "num_reviews"]
}

[Results]
"hits": {
    "total": 4,
    "max_score": 0.8463001,
    "hits": [
      {
        "_index": "bookdb_index",
        "_type": "book",
        "_id": "1",
        "_score": 0.8463001,
        "_source": {
          "summary": "A distibuted real-time search and analytics engine",
          "num_reviews": 20,
          "title": "Elasticsearch: The Definitive Guide",
          "publish_date": "2015-02-07"
        }
      },
      {
        "_index": "bookdb_index",
        "_type": "book",
        "_id": "4",
        "_score": 0.7067348,
        "_source": {
          "summary": "Comprehensive guide to implementing a scalable search engine using Apache Solr",
          "num_reviews": 23,
          "title": "Solr in Action",
          "publish_date": "2014-04-05"
        }
      },
      {
        "_index": "bookdb_index",
        "_type": "book",
        "_id": "3",
        "_score": 0.08952084,
        "_source": {
          "summary": "build scalable search applications using Elasticsearch without having to do complex low-level programming or understand advanced data science algorithms",
          "num_reviews": 18,
          "title": "Elasticsearch in Action",
          "publish_date": "2015-12-03"
        }
      },
      {
        "_index": "bookdb_index",
        "_type": "book",
        "_id": "2",
        "_score": 0.07602123,
        "_source": {
          "summary": "organize text using approaches such as full-text search, proper name recognition, clustering, tagging, information extraction, and summarization",
          "num_reviews": 12,
          "title": "Taming Text: How to Find, Organize, and Manipulate It",
          "publish_date": "2013-01-24"
        }
      }
    ]
  }

注1: 要在 Elasticsearch 例項中使用動態指令碼，必須在 config/elasticsearch.yaml 檔案中啟用它；也可以使用儲存在 Elasticsearch 伺服器上的指令碼。建議看看 Elasticsearch 指南文件獲取更多資訊。

注2: 因 JSON 不能包含嵌入式換行符，請使用分號來分割語句。

引用自：23 USEFUL ELASTICSEARCH EXAMPLE QUERIES

mysql dba常用的查詢語句
2018-12-10
MySql
postgresql dba常用sql查詢語句
2019-08-27
SQL
Oracle常用的系統查詢語句整理
2018-08-03
Oracle
MySQL 查詢常用操作(0) —— 查詢語句的執行順序
2023-03-01
MySql
Elasticsearch查詢及聚合類DSL語句寶典
2022-12-27
Elasticsearch
MySQL資料庫：7、SQL常用查詢語句
2022-11-25
MySql資料庫
mysql查詢語句
2024-04-04
MySql
請教一個mysql查詢語句！！
2021-10-22
MySql
記一個實用的sql查詢語句
2019-02-07
SQL
MySql常用30種SQL查詢語句優化方法
2018-03-23
MySql優化
Elasticsearch 7.x Nested 巢狀型別查詢 | ES 乾貨
2019-07-23
Elasticsearch巢狀型別
Mysql之查詢語句
2024-03-09
MySql
SQL mother查詢語句
2024-03-09
SQL
sql查詢語句流程
2024-05-05
SQL
SQL查詢語句 (Oracle)
2020-10-31
SQLOracle
mysql查詢語句集
2020-04-05
MySql
mysql查詢語句5：連線查詢
2020-12-22
MySql
PostgreSQL 原始碼解讀（19）- 查詢語句#4（ParseTree詳解）
2018-08-15
SQL原始碼
MySQL的簡單查詢語句
2019-01-15
MySql
Mysql常用sql語句（20）- 子查詢重點知識
2020-05-14
MySql
關於使用plsql操作oracle的一點小技巧和幾個常用的查詢語句
2024-10-12
SQLOracle
使用sql語句查詢平均值,使用sql語句查詢資料總條數, not in 篩選語句的使用
2024-06-13
SQL
PostgreSQL 原始碼解讀（34）- 查詢語句#19（查詢優化-表示式預處理#4）
2018-09-05
SQL原始碼優化
mysql高階查詢語句
2020-11-04
MySql
多表查詢建表語句
2020-10-05
Laravel 子查詢語句用法
2021-01-28
Laravel
資料庫查詢語句
2018-12-21
資料庫
ORACLE 資料庫查詢語句與DML語句
2019-10-16
Oracle資料庫
mysql查詢效率慢的SQL語句
2018-12-12
MySql
關於使用plsql操作oracle的一點小技巧和幾個常用的查詢語句BU
2024-10-13
SQLOracle
MongoDB 常用查詢語法
2019-10-09
MongoDB
ElasticSearch基礎及查詢語法
2019-05-03
Elasticsearch
史上最全：PostgreSQL DBA常用SQL查詢語句（建議收藏學習）
2019-12-18
SQL
MySQL內連線查詢語句
2020-05-29
MySql
ORACLE結構化查詢語句
2020-10-18
Oracle
hisql ORM 查詢語句使用教程
2022-01-08
SQLORM
oracle查詢語句查詢增加一列內容
2018-06-05
Oracle
一條查詢語句的執行流程
2020-07-16