22

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?

Cactus
  • 27,075
  • 9
  • 69
  • 149
jdiaz4517
  • 269
  • 1
  • 3
  • 8

1 Answers1

39

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 .

Vineeth Mohan
  • 18,633
  • 8
  • 63
  • 77
  • Thanks a lot, this works. I supposed scripting is then the only way to compare fields between them. Has scripting a negative impact in the performance? – jdiaz4517 Dec 31 '14 at 10:12
  • 3
    Scripting is generally CPU intensive. – Vineeth Mohan Aug 28 '15 at 06:19
  • 4
    For ES 5.0+ you'll need to use query instead of filtered at the top leave, e.g.: `"query":{"bool":{"must": {"range":...}},"filter":...}`: https://stackoverflow.com/questions/40519806/no-query-registered-for-filtered – AJP Aug 29 '17 at 12:44
  • How would you do this with lucene syntax? – Sam Nov 15 '19 at 20:58
  • 2
    @Sam - This operation is done on doc values and not reverse index. So queries in Lucene wont help. You will need to write a extension to custom collector interface or something and solve it – Vineeth Mohan Nov 16 '19 at 02:36