Elasticsearch新手求助：将带NOT IN子查询的SQL转为ES查询

原SQL的核心逻辑是：获取所有没有对应type='end'记录的conversationid，对应示例数据的预期结果为2和3。以下是两种适合新手的实现方案：

方案一：分步查询（易理解） #

第一步：获取所有含type='end'的会话ID #

在Dev Tools中执行该查询，得到所有存在结束记录的会话ID列表：

GET /conversations/_search
{
  "size": 0,
  "query": {
    "term": {
      "type": "end"
    }
  },
  "aggs": {
    "end_conversations": {
      "terms": {
        "field": "conversationid",
        "size": 10000 // 根据实际数据量调整大小
      }
    }
  }
}

执行后，返回结果的aggregations.end_conversations.buckets中会列出所有带end记录的conversationid（示例中为1）。

第二步：排除上述ID，获取目标会话ID #

将第一步得到的ID填入terms查询，再通过聚合去重得到结果：

GET /conversations/_search
{
  "size": 0,
  "query": {
    "bool": {
      "must_not": [
        {
          "terms": {
            "conversationid": [1] // 替换为第一步获取的ID列表
          }
        }
      ]
    }
  },
  "aggs": {
    "target_conversations": {
      "terms": {
        "field": "conversationid",
        "size": 10000
      }
    }
  }
}

返回结果的aggregations.target_conversations.buckets中即为预期的2和3。

方案二：单查询实现（一步到位） #

通过聚合的bucket_selector过滤无end记录的会话，无需分步操作：

GET /conversations/_search
{
  "size": 0,
  "aggs": {
    "group_by_conversation": {
      "terms": {
        "field": "conversationid",
        "size": 10000
      },
      "aggs": {
        "has_end_type": {
          "filter": {
            "term": {
              "type": "end"
            }
          }
        },
        "filter_no_end": {
          "bucket_selector": {
            "buckets_path": {
              "endCount": "has_end_type._count"
            },
            "script": "params.endCount == 0"
          }
        }
      }
    }
  }
}

该查询先按conversationid分组，统计每组内type='end'的文档数量，最终只保留数量为0的分组，直接得到目标会话ID。

注意事项 #

• 若conversationid为文本类型，需在字段后加上.keyword后缀（如conversationid.keyword），具体以你的索引映射为准。
• size参数需根据实际数据量调整，确保覆盖所有可能的会话ID。

内容的提问来源于stack exchange，提问作者samy