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

為了解決這個(gè)問(wèn)題，我嘗試讓ChatGPT給我做翻譯，效果還不錯(cuò)。下面我們按照?qǐng)鼍皝?lái)看。

場(chǎng)景一: 列表模糊查詢

在B端后臺(tái)系統(tǒng)的列表頁(yè)里，我們通?？梢钥吹胶芏嗪Y選框。產(chǎn)品經(jīng)理經(jīng)常把按 ID 和 Name 模糊匹配的功能放到一個(gè)篩選框，名曰“更好的用戶體驗(yàn)”。如果用SQL寫的話，非常簡(jiǎn)單:

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

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

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

提問(wèn)開(kāi)始：

ES index 名字 "user"，有三個(gè)字段

1. user_id string
2. user_name string
3. client_id int

我要查詢帶有以下條件的記錄：client_id = 123 and (user_name = '張三' or user_id = '張三')。dsl應(yīng)該怎么寫？

回復(fù)開(kāi)始：

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

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

在這個(gè)查詢中，我們使用了 bool 查詢來(lái)確保指定的條件被用于過(guò)濾文檔。

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

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

回復(fù)結(jié)束：

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

場(chǎng)景二: 根據(jù)聚合結(jié)果進(jìn)行過(guò)濾 having

基于上面的 user 表，我們?cè)偌右粋€(gè) ticket_id 字段，表示處理過(guò)的工單，我們要查出處理工單超過(guò)100個(gè)的用戶。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提問(wèn):

提問(wèn)開(kāi)始：

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對(duì)應(yīng)的 user_name

回復(fù)開(kāi)始：
要按照 "user_id" 字段進(jìn)行聚合，并查詢符合條件 count(ticket_id) > 100 的 "user_id"，以及對(duì)應(yīng)的 "user_name"，可以使用聚合查詢中的 terms 聚合和 bucket_selector 管道聚合來(lái)實(shí)現(xiàn)。以下是相應(yīng)的 DSL 查詢示例：

GET /user/_search
{
  "size": 0,
  "aggs": {
    "users": {
      "terms": {
        "field": "user_id",
        "size": 10,
        "min_doc_count": 101  // 設(shè)置最小文檔數(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 的聚合桶
          }
        }
      }
    }
  }
}

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

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

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

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

回復(fù)結(jié)束。

場(chǎng)景三: 聚合以后取topK

向ChatGPT提問(wèn)：

提問(wèn)開(kāi)始：

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

1. user_id string
2. user_name string
3. client_id int
4. ticket_id

我要查詢處理ticket數(shù)量top10 的 user_id 以及對(duì)應(yīng)的user_name

回復(fù)開(kāi)始：
要查詢處理 ticket 數(shù)量排名前 10 的 "user_id"，以及對(duì)應(yīng)的 "user_name"，可以使用 terms 聚合和 top_hits 子聚合來(lái)實(shí)現(xiàn)。以下是相應(yīng)的 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
          }
        }
      }
    }
  }
}

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

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

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

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

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

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

回復(fù)結(jié)束。

本著快速出活/快速解決問(wèn)題的心態(tài)，ChatGPT確實(shí)提升了工作效率。不過(guò)在解決需求之后，還是要補(bǔ)補(bǔ)課，了解下ElasticSearch的用法和工作機(jī)制。因?yàn)镃hatGPT是語(yǔ)言模型，一旦邏輯復(fù)雜起來(lái)，ChatGPT就開(kāi)始瞎說(shuō)了。