elasticsearch comparison between fields

Question

Let's say I have documents with the following fields: {field1, field2, ... fieldn}

I need to run some queries where some of the conditions will require a comparison between two or more fields. like fieldX = fieldY

In standard SQL, an example could be:

SELECT * FROM Table1 WHERE farePrice>100 AND originRegion = destinationRegion 

I'be been reading some documentation, and it looks "scripting" could be the only way to achieve this? Or are there any other options?

Answer 1

You can use the script filter -

{
  "filtered": {
    "query": {
      "range": {
        "farePrice": {
          "gt": 100
        }
      }
    },
    "filter": {
      "script": {
        "script": "doc['originRegion'].value ==  doc['destinationRegion'].value"
      }
    }
  }
}

You can find more information at here and here .