Elastic search: Query documents for a field which is greater than another field

Question

I have elastic search documents which look something like this:

{
  "id": "1",
  "quantityPerUnitOfMeasure": 16,
  "availableQuantity": 12,
}

I'd like to compose a query which only returns documents where the availableQuantity is greater than it's own quantityPerUnitOfMeasure.

For example, if I have the following documents available:

[
  {
    "id": "1",
    "quantityPerUnitOfMeasure": 16,
    "availableQuantity": 12,
  },
  {
    "id": "2",
    "quantityPerUnitOfMeasure": 4,
    "availableQuantity": 20,
  },
  {
    "id": "3",
    "quantityPerUnitOfMeasure": 40,
    "availableQuantity": 50,
  }
]

the query should return documents with the ids "2" and "3", but not return the document with id "1".

Answer 1

It's easy with runtime_mappings

Your documents

PUT /greater_than_filter/_bulk
{"create":{"_id":1}}
{"id":"1","quantityPerUnitOfMeasure":16,"availableQuantity":12}
{"create":{"_id":2}}
{"id":"2","quantityPerUnitOfMeasure":4,"availableQuantity":20}
{"create":{"_id":3}}
{"id":"3","quantityPerUnitOfMeasure":40,"availableQuantity":50}

Query with runtime_mappings

GET /greater_than_filter/_search?filter_path=hits.hits.fields
{
  "runtime_mappings": {
    "is_quantity_greater_than_available": {
      "type": "boolean",
      "script": """
        emit(doc['availableQuantity'].value > doc['quantityPerUnitOfMeasure'].value);
      """
    }
  },
  "query": {
    "term": {
      "is_quantity_greater_than_available": true
    }
  },
  "fields": ["is_quantity_greater_than_available", "id"]
}

Response

{
  "hits" : {
    "hits" : [
      {
        "fields" : {
          "is_quantity_greater_than_available" : [
            true
          ],
          "id" : [
            "2"
          ]
        }
      },
      {
        "fields" : {
          "is_quantity_greater_than_available" : [
            true
          ],
          "id" : [
            "3"
          ]
        }
      }
    ]
  }
}