How to compare two fields in Elasticsearch as in SQL with where clause?-CodePudding

I have this kind of documents in y my index. I would like to Determine which departments belong to their divisions.

With SQL it would be:
SELECT * FROM my_table
WHERE "Division Name" == "Department Name"

document of the index:

_source " : {
    "Clothing ID": "1080",
    "Age": "49",
    "Title": "Not for the very petite",
    "Review Text": "I love tracy reese dresses,
    "Rating": "2",
    "Recommended IND": "0",
    "Positive Feedback Count": "4",
    "Division Name": "General",
    "Department Name": "Dresses",
    "Class Name": "Dresses"
}

I try this but it's not working as expected. It returns only documents which has both 'Department Name' and 'Division Name' empy.

GET /eval_news/_search 
{
  "_source": [
    "Department Name",
    "Division Name"
  ],
  "query": {
    "bool": {
      "must": [
        {
          "script": {
            "script": {
              "source": "doc['Division Name.keyword'].value ==
               doc['Department Name.keyword'].value",
              "lang": "painless"
            }
          }
        }
      ]
    }
  }
}

Any help please,

CodePudding user response：

I tried sample document using your query and it returns the documents if the division name and department name is the same. So I guess there are no documents that have the same department and division name. The only case when they are the same is when both of them are empty. Please check your your documents or your requirement please.