Elastic Search Query to filter records where value in 2 columns of the record are equal

Question

I am working with Elastic Search Version 6.8.22 and have an index where I have 2 columns: Column A and Column B. I want to filter out all records where values for Column A is equal to Column B. How can I achieve this?

Could someone please help here?

I cannot re-index my tables. I have also tried this query:

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"}}}}}'

I am getting this error with this query

{
   "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"[
                  "A"
               ]".value == doc"[
                  "B"
               ]".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
}

Answer 1

I don't know what's a problem.

Document

PUT /field_compare/_bulk
{"create":{"_id":1}}
{"textA": "aaaaa", "textB": "aaaaa", "objectA": {"text": "xxxx"}, "objectB": {"text": "xxxx"}}

Query compares text fields and object fields. If field pairs are equal, then document is in hits

GET /field_compare/_search
{
    "query": {
        "bool": {
            "filter": {
                "script": {
                    "script": {
                        "source": """
                            boolean areFieldsEqual = doc['textA.keyword'].value == doc['textB.keyword'].value;
                            boolean areObjectsEqual = doc['objectA.text.keyword'].value == doc['objectB.text.keyword'].value;
                            
                            return areFieldsEqual && areObjectsEqual;
                        """
                    }
                }
            }
        }
    }
}