如何在Elasticsearch中实现PostgreSQL的NOT IN子查询等效逻辑？

你的场景是将节目、剧集、用户观看记录都存在同一个Elasticsearch索引中，要实现类似PostgreSQL的"指定节目下排除特定用户已看剧集"的查询，完全可以通过单条查询完成，核心是利用Terms Lookup Query动态获取用户已观看的剧集ID，无需手动传递ID数组。

核心查询DSL（适配ES 7+） #

假设你的索引名为media_records，且用doc_type字段区分文档类型（episode表示剧集、watched表示观看记录），查询语句如下：

{
  "query": {
    "bool": {
      // 匹配指定节目的所有剧集
      "must": [
        {"term": {"show_id": "showID"}},
        {"term": {"doc_type": "episode"}}
      ],
      // 排除该用户已观看的剧集
      "must_not": [
        {
          "terms": {
            "episode_id": {
              // 指定查询的索引（和主查询同一索引）
              "index": "media_records",
              // 要提取的字段（观看记录中的剧集ID）
              "path": "episode_id",
              // 筛选出目标用户的所有观看记录
              "query": {
                "term": {"user_id": "userId"}
              }
            }
          }
        }
      ]
    }
  }
}

关键逻辑说明 #

1. 动态获取已看剧集ID：Terms Lookup会先在索引中查询所有user_id = 'userId'的观看记录文档，自动提取这些文档的episode_id值，生成排除列表。
2. 自动适配观看列表扩容：不管用户后续新增多少观看记录，每次查询都会实时获取最新的观看数据，完全不需要手动维护或传递ID数组。
3. 字段映射要求：确保show_id、user_id、episode_id都被映射为keyword类型（避免分词导致的查询错误），doc_type字段同样建议用keyword类型。

注意事项 #

如果你的索引没有用doc_type区分文档类型，只要能通过其他字段（比如不同的字段存在性）区分剧集和观看记录，调整must里的匹配条件即可，核心的Terms Lookup逻辑不变。

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