Elasticsearch 6.8.22如何筛选A列与B列值相等的记录？

问题背景 #

使用Elasticsearch 6.8.22版本，现有索引包含A、B两列，需筛选出A列值与B列值相等的记录，且无法重新索引。执行以下curl查询时出现编译错误：

curl -X POST http://localhost:9200/daily_20240301_data/_search? -H "Content-Type: application/json" -d '{"size":1,"_source":["A","B"],"query":{"bool":{"must":[{"script":{"script":{"source": "doc['A'].value == doc['B'].value", "lang":"painless"}}}]}},"aggs":{"unique_tenants":{"terms":{"field":"C.Raw","size":80000,"order":{"_key":"asc"}}}}}'

错误信息 #

{
   "error":{
      "root_cause":[
         {
            "type":"script_exception",
            "reason":"compile error",
            "script_stack":[
               "doc[A].value == doc[B ...",
               " ^---- HERE"
            ],
            "script":"doc[A].value == doc[B].value",
            "lang":"painless"
         }
      ],
      "type":"search_phase_execution_exception",
      "reason":"all shards failed",
      "phase":"query",
      "grouped":true,
      "failed_shards":[
         {
            "shard":0,
            "index":"daily_20240301_data",
            "node":"DtXgxEd0Rla4aTzRes6c9w",
            "reason":{
               "type":"query_shard_exception",
               "reason":"failed to create query: {\n \"\"bool\"\" : {\n \"\"must\"\" : [\n {\n \"\"script\"\" : {\n \"\"script\"\" : {\n \"\"source\"\" : \"\"doc\"[\n                  \"A\"\n               ]\".value == doc\"[\n                  \"B\"\n               ]\".value\"\",\n \"\"lang\"\" : \"\"painless\"\"\n },\n \"\"boost\"\" : 1.0\n }\n }\n ],\n \"\"adjust_pure_negative\"\" : true,\n \"\"boost\"\" : 1.0\n }\n}",
               "index_uuid":"cxxxk3UGRoy2ubtxxxAJ5Q",
               "index":"daily_20240301_data",
               "caused_by":{
                  "type":"script_exception",
                  "reason":"compile error",
                  "script_stack":[
                     "doc[A].value == doc[B ...",
                     " ^---- HERE"
                  ],
                  "script":"doc[A].value == doc[B].value",
                  "lang":"painless",
                  "caused_by":{
                     "type":"illegal_argument_exception",
                     "reason":"Variable [LogMN] is not defined."
                  }
               }
            }
         }
      ],
      "caused_by":{
         "type":"script_exception",
         "reason":"compile error",
         "script_stack":[
            "doc[A].value == doc[B ...",
            " ^---- HERE"
         ],
         "script":"doc[A].value == doc[B].value",
         "lang":"painless",
         "caused_by":{
            "type":"illegal_argument_exception",
            "reason":"Variable [A] is not defined."
         }
      }
   },
   "status":400
}

错误原因 #

核心问题是shell命令的单引号嵌套解析导致Painless脚本中的字段名未被正确识别：
curl命令用单引号包裹整个JSON参数，而脚本中doc['A'].value的单引号会与外层单引号冲突，shell解析时截断字符串，使得Painless脚本将A和B视为未定义变量，而非字段名，最终触发编译错误。

修复后的查询命令 #

将脚本中的字段名改用转义双引号包裹，确保shell和Painless都能正确解析，同时移除原命令中多余的/_search?问号：

curl -X POST http://localhost:9200/daily_20240301_data/_search -H "Content-Type: application/json" -d '{
  "size": 1,
  "_source": ["A", "B"],
  "query": {
    "bool": {
      "must": [
        {
          "script": {
            "script": {
              "source": "doc[\"A\"].value == doc[\"B\"].value",
              "lang": "painless"
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "unique_tenants": {
      "terms": {
        "field": "C.Raw",
        "size": 80000,
        "order": { "_key": "asc" }
      }
    }
  }
}'

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