作為一個SQL玩家，接觸到 ElasticSearch 以后，被其dsl奇奇怪怪的寫法所困擾，比如 bool should filter 什么場景下應該嵌套，什么時候 bool query是必須的，什么時候可以干掉。排查問題時，不可避免地需要手寫dsl，經(jīng)常收到ES報錯。

ChatGPT ElasticSearch

為了解決這個問題，我嘗試讓ChatGPT給我做翻譯，效果還不錯。下面我們按照場景來看。

場景一: 列表模糊查詢

在B端后臺系統(tǒng)的列表頁里，我們通常可以看到很多篩選框。產(chǎn)品經(jīng)理經(jīng)常把按 ID 和 Name 模糊匹配的功能放到一個篩選框，名曰“更好的用戶體驗”。如果用SQL寫的話，非常簡單:

select * from user 
where client_id = 123 
and  user_id like '%張三%' or user_name like '%張三%';

如果存儲在MySQL，加上client_id索引就可以大大減少篩選出的數(shù)據(jù)量，性能不會太差。

如果數(shù)據(jù)量非常大，那就用ES吧。我們可以對ChatGPT這樣提問：

-------- 提問開始 --------

ES index 名字 "user"，有三個字段

1. user_id string
2. user_name string
3. client_id int
   我要查詢帶有以下條件的記錄：client_id = 123 and (user_name = '張三' or user_id = '張三')。dsl應該怎么寫？

-------- 回復開始 --------

要查詢 "user" 索引中滿足以下條件的記錄：client_id = 123 and (user_name = '張三' or user_id = '張三')，可以使用 bool 查詢結(jié)合 term 查詢來實現(xiàn)。以下是相應的 DSL 查詢示例：

GET /user/_search
{
  "query": {
    "bool": {
      "filter": [
        {
          "term": {
            "client_id": 123
          }
        },
        {
          "bool": {
            "should": [
              {
                "term": {
                  "user_name": "張三"
                }
              },
              {
                "term": {
                  "user_id": "張三"
                }
              }
            ]
          }
        }
      ]
    }
  }
}

在這個查詢中，我們使用了 bool 查詢來確保指定的條件被用于過濾文檔。

在 filter 子句中，我們有兩個條件：一個是針對 "client_id" 字段的值為 123 的條件，使用了 term 查詢。第二個條件是一個嵌套的 bool 查詢，其中包含一個 should 子句。should 子句中包含了兩個 term 查詢：一個是針對 "user_name" 字段，另一個是針對 "user_id" 字段，它們都檢查值是否為 "張三"。通過使用 should 子句，我們表示其中任一條件匹配即可。

這個 DSL 查詢將返回滿足 client_id 為 123，并且用戶名為 "張三" 或用戶ID為 "張三" 的文檔記錄。

-------- 回復結(jié)束 --------

ChatGPT 不只給出了答案，也給出了解釋，我們針對業(yè)務里的index做一下修改就好。

場景二: 根據(jù)聚合結(jié)果進行過濾 having

基于上面的 user 表，我們再加一個 ticket_id 字段，表示處理過的工單，我們要查出處理工單超過100個的用戶。SQL可以這樣寫:

select 
  user_id, 
  max(user_name) as user_name,
  count(ticket_id) as ticket_cnt 
from user 
where client_id = 123
having count(ticket_id) > 100;

向ChatGPT提問:

-------- 提問開始 --------

ES index 名字 "user"，包含以下字段：

1. user_id string
2. user_name string
3. client_id int
4. ticket_id
   按照 user_id 聚合，查詢 count(ticket_id) > 100 的 user_id，以及user_id對應的 user_name

-------- 回復開始 --------
要按照 "user_id" 字段進行聚合，并查詢符合條件 count(ticket_id) > 100 的 "user_id"，以及對應的 "user_name"，可以使用聚合查詢中的 terms 聚合和 bucket_selector 管道聚合來實現(xiàn)。以下是相應的 DSL 查詢示例：

GET /user/_search
{
  "size": 0,
  "aggs": {
    "users": {
      "terms": {
        "field": "user_id",
        "size": 10,
        "min_doc_count": 101  // 設置最小文檔數(shù)，只返回 count(ticket_id) > 100 的聚合結(jié)果
      },
      "aggs": {
        "user_name": {
          "terms": {
            "field": "user_name",
            "size": 1
          }
        },
        "ticket_count": {
          "value_count": {
            "field": "ticket_id"
          }
        },
        "bucket_filter": {
          "bucket_selector": {
            "buckets_path": {
              "count": "ticket_count"
            },
            "script": "params.count > 100"  // 僅保留 count(ticket_id) > 100 的聚合桶
          }
        }
      }
    }
  }
}

在這個查詢中，我們使用了 terms 聚合對 "user_id" 字段進行分組，并在聚合結(jié)果中嵌套了 user_name 字段的 terms 聚合和 ticket_id 字段的 value_count 聚合。

在 terms 聚合中，我們設置了 min_doc_count 參數(shù)為 101，以僅返回滿足 count(ticket_id) > 100 的聚合桶。這樣可以過濾掉不滿足條件的聚合結(jié)果。

最終，我們使用 bucket_selector 管道聚合來進行桶過濾，僅保留滿足條件 params.count > 100 的聚合桶。這樣，最終的聚合結(jié)果中將只包含滿足條件的 "user_id" 和對應的 "user_name"。

請注意，根據(jù)您的具體需求，您可能需要根據(jù)實際情況調(diào)整聚合查詢中的參數(shù)和設置，如設置合適的 size 值來控制返回的聚合桶數(shù)量等。

-------- 回復結(jié)束 --------

場景三: 聚合以后取topK

向ChatGPT提問：

-------- 提問開始 --------

ES index 名字 "user"，包含以下字段：

1. user_id string
2. user_name string
3. client_id int
4. ticket_id
   我要查詢處理ticket數(shù)量top10 的 user_id 以及對應的user_name

-------- 回復開始 --------
要查詢處理 ticket 數(shù)量排名前 10 的 "user_id"，以及對應的 "user_name"，可以使用 terms 聚合和 top_hits 子聚合來實現(xiàn)。以下是相應的 DSL 查詢示例：

GET /user/_search
{
  "size": 0,
  "aggs": {
    "top_users": {
      "terms": {
        "field": "user_id",
        "size": 10,
        "order": {
          "ticket_count": "desc"
        }
      },
      "aggs": {
        "user_name": {
          "terms": {
            "field": "user_name",
            "size": 1
          }
        },
        "ticket_count": {
          "value_count": {
            "field": "ticket_id"
          }
        },
        "top_user_hits": {
          "top_hits": {
            "size": 1
          }
        }
      }
    }
  }
}

在這個查詢中，我們使用了 terms 聚合對 "user_id" 字段進行分組，并按照 "ticket_count" 字段的降序?qū)Ψ纸M結(jié)果進行排序。

在 terms 聚合中，我們設置了 size 參數(shù)為 10，以僅返回前 10 個處理 ticket 數(shù)量最多的 "user_id"。

在每個聚合桶中，我們還嵌套了 user_name 字段的 terms 聚合和 ticket_id 字段的 value_count 聚合，分別用于獲取對應的 "user_name" 和計算 ticket 數(shù)量。

此外，我們使用了 top_hits 子聚合來獲取每個聚合桶中的匹配文檔的詳細信息。在這里，我們設置了 size 參數(shù)為 1，以僅獲取每個聚合桶中的一條文檔信息。

最終，聚合查詢的結(jié)果將包含處理 ticket 數(shù)量排名前 10 的 "user_id"，對應的 "user_name"，以及每個用戶最新的一條文檔信息。

請注意，根據(jù)實際情況，您可能需要根據(jù)具體需求調(diào)整聚合查詢中的參數(shù)和設置，如設置合適的 size 值來控制返回的聚合桶數(shù)量，以及添加其他的聚合或過濾條件等。

-------- 回復結(jié)束 --------

本著快速出活/快速解決問題的心態(tài)，ChatGPT確實提升了工作效率。不過在解決需求之后，還是要補補課，了解下ElasticSearch的用法和工作機制。因為ChatGPT是語言模型，一旦邏輯復雜起來，ChatGPT就開始瞎說了。