How to compare two date fields in same document in elasticsearch

In my elastic search index, each document will have two date fields createdDate and modifiedDate. I'm trying to add a filter in kibana to fetch the documents where the modifiedDate is greater than createdDate. How to create this filter in kibana?
Tried Using below query instead of greater than it is considering as gte and fetching all records

GET index/_search
{
  "query": {
    "bool": {
      "filter": {
        "script": {
          "script" : {
                        "inline" : "doc['modifiedTime'].value.getMillis() > doc['createdTime'].value.getMillis()",
                        "lang"   : "painless"
                        }
        }
      }
    }
  }
}

Solution

There are a few options.

Option A: The easiest and most performant one is to store the difference of the two fields inside a new field of your document, e.g.

{
   "createDate": "2022-01-11T12:34:56Z",
   "modifiedDate": "2022-01-11T12:34:56Z",
   "diffMillis": 0
}
{
   "createDate": "2022-01-11T12:34:56Z",
   "modifiedDate": "2022-01-11T12:35:58",
   "diffMillis": 62000
}

Then, in Kibana you can query on diffMillis > 0 and figure out all documents that have been modified after their creation.

Option B: You can use a script query

GET index/_search
{
  "query": {
    "bool": {
      "filter": {
        "script": {
          "script": """
            return doc['createdDate'].value.millis < doc['modifiedDate'].value.millis;
          """
        }
      }
    }
  }
}

Note: depending on the amount of data you have, this option can potentially have disastrous performance, because it needs to be evaluated on ALL of your documents.

Option C: If you're using ES 7.11+, you can use runtime fields directly from the Kibana Discover view.

You can use the following script in order to add a new runtime field (e.g. name it diffMillis) to your index pattern:

emit(doc['modifiedDate'].value.millis - doc['createdDate'].value.millis)

And then you can add the following query into your search bar

diffMillis > 0