Elasticsearch按ISIN去重并优先保留NSE交易所股票文档

直接用聚合+Top Hits就能搞定，核心逻辑是按ISIN分组，每组里优先排NSE的记录，只取第一条。这样既能保证同一ISIN只返回一条，有NSE就留NSE，没NSE的话就保留BSE的记录。

完整查询语句 #

{
  "size": 0,
  "aggs": {
    "group_by_isin": {
      "terms": {
        "field": "Isin.keyword",
        "size": 10000
      },
      "aggs": {
        "top_nse_record": {
          "top_hits": {
            "size": 1,
            "sort": [
              {
                "_script": {
                  "type": "number",
                  "script": {
                    "source": "doc['Exch.keyword'].value == 'NSE' ? 1 : 2"
                  },
                  "order": "asc"
                }
              }
            ],
            "_source": ["Exch", "Sym", "Isin"]
          }
        }
      }
    }
  }
}

关键细节说明 #

• size: 0：关掉默认的搜索结果，只返回聚合数据，避免冗余
• terms聚合：按Isin.keyword分组（必须用keyword类型，不然会被分词打乱），size设大一点覆盖所有ISIN
• 脚本排序：给NSE记录打1分，BSE打2分，升序排后NSE自然在每组最前面
• top_hits：每组只取1条，刚好拿到优先级最高的那条记录
• _source：按需指定要返回的字段，减少不必要的数据

执行后的结果示例 #

跑这个查询后，得到的聚合结果会是这样：

{
  "aggregations": {
    "group_by_isin": {
      "buckets": [
        {
          "key": "INE883A01011",
          "doc_count": 2,
          "top_nse_record": {
            "hits": {
              "total": {
                "value": 2,
                "relation": "eq"
              },
              "max_score": null,
              "hits": [
                {
                  "_index": "stkindex",
                  "_id": "2277NSEE",
                  "_score": null,
                  "_source": {
                    "Exch": "NSE",
                    "Sym": "MRF",
                    "Isin": "INE883A01011"
                  }
                }
              ]
            }
          }
        },
        {
          "key": "INE671A01010",
          "doc_count": 1,
          "top_nse_record": {
            "hits": {
              "total": {
                "value": 1,
                "relation": "eq"
              },
              "max_score": null,
              "hits": [
                {
                  "_index": "stkindex",
                  "_id": "517174BSEE",
                  "_score": null,
                  "_source": {
                    "Exch": "BSE",
                    "Isin": "INE671A01010",
                    "Sym": "HONAUT"
                  }
                }
              ]
            }
          }
        }
      ]
    }
  }
}

完美符合需求：MRF只保留NSE的记录，HONAUT只有BSE就留BSE的记录。

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