作为一个SQL玩家，接触到 ElasticSearch 以后，被其dsl奇奇怪怪的写法所困扰，比如 bool should filter 什么场景下应该嵌套，什么时候 bool query是必须的，什么时候可以干掉。排查问题时，不可避免地需要手写dsl，经常收到ES报错。

ChatGPT ElasticSearch

为了解决这个问题，我尝试让ChatGPT给我做翻译，效果还不错。下面我们按照场景来看。

场景一: 列表模糊查询

在B端后台系统的列表页里，我们通常可以看到很多筛选框。产品经理经常把按 ID 和 Name 模糊匹配的功能放到一个筛选框，名曰“更好的用户体验”。如果用SQL写的话，非常简单:

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

如果存储在MySQL，加上client_id索引就可以大大减少筛选出的数据量，性能不会太差。

如果数据量非常大，那就用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 查询结合 term 查询来实现。以下是相应的 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为 "张三" 的文档记录。

-------- 回复结束 --------

ChatGPT 不只给出了答案，也给出了解释，我们针对业务里的index做一下修改就好。

场景二: 根据聚合结果进行过滤 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 管道聚合来实现。以下是相应的 DSL 查询示例：

GET /user/_search
{
  "size": 0,
  "aggs": {
    "users": {
      "terms": {
        "field": "user_id",
        "size": 10,
        "min_doc_count": 101  // 设置最小文档数，只返回 count(ticket_id) > 100 的聚合结果
      },
      "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" 字段进行分组，并在聚合结果中嵌套了 user_name 字段的 terms 聚合和 ticket_id 字段的 value_count 聚合。

在 terms 聚合中，我们设置了 min_doc_count 参数为 101，以仅返回满足 count(ticket_id) > 100 的聚合桶。这样可以过滤掉不满足条件的聚合结果。

最终，我们使用 bucket_selector 管道聚合来进行桶过滤，仅保留满足条件 params.count > 100 的聚合桶。这样，最终的聚合结果中将只包含满足条件的 "user_id" 和对应的 "user_name"。

请注意，根据您的具体需求，您可能需要根据实际情况调整聚合查询中的参数和设置，如设置合适的 size 值来控制返回的聚合桶数量等。

-------- 回复结束 --------

场景三: 聚合以后取topK

向ChatGPT提问：

-------- 提问开始 --------

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

1. user_id string
2. user_name string
3. client_id int
4. ticket_id
   我要查询处理ticket数量top10 的 user_id 以及对应的user_name

-------- 回复开始 --------
要查询处理 ticket 数量排名前 10 的 "user_id"，以及对应的 "user_name"，可以使用 terms 聚合和 top_hits 子聚合来实现。以下是相应的 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" 字段的降序对分组结果进行排序。

在 terms 聚合中，我们设置了 size 参数为 10，以仅返回前 10 个处理 ticket 数量最多的 "user_id"。

在每个聚合桶中，我们还嵌套了 user_name 字段的 terms 聚合和 ticket_id 字段的 value_count 聚合，分别用于获取对应的 "user_name" 和计算 ticket 数量。

此外，我们使用了 top_hits 子聚合来获取每个聚合桶中的匹配文档的详细信息。在这里，我们设置了 size 参数为 1，以仅获取每个聚合桶中的一条文档信息。

最终，聚合查询的结果将包含处理 ticket 数量排名前 10 的 "user_id"，对应的 "user_name"，以及每个用户最新的一条文档信息。

请注意，根据实际情况，您可能需要根据具体需求调整聚合查询中的参数和设置，如设置合适的 size 值来控制返回的聚合桶数量，以及添加其他的聚合或过滤条件等。

-------- 回复结束 --------

本着快速出活/快速解决问题的心态，ChatGPT确实提升了工作效率。不过在解决需求之后，还是要补补课，了解下ElasticSearch的用法和工作机制。因为ChatGPT是语言模型，一旦逻辑复杂起来，ChatGPT就开始瞎说了。