How to write a scripted field to get the number of days between status transition

Question

I have two fields in an index named created_on and closed_on with date format July 15th 2019, 17:40:09.000, i want to write a painless scripted field to calculate the number of days between these two dates.

I have tried this:

def res_date= 0; 
def closing_date = 0;
if ( doc['status_id'].value == 51 ) {
res_date = doc['e_created_date'].date.getMillis();
}
if ( doc['status_id'].value == 20 ) {
closing_date = doc['closed_on'].date.getMillis();
}
def day = 0;
day= (closing_date - res_date) / (1000 * 60 * 60 * 24);  
return day;

I'm getting wrong day value.

Sample data

Does each document have a single value for `status_id`? if that's the case, you'll always end up with only one date (either `res_date` or `closing_date`) in the context of each document. Hence your computation cannot succeed. Also I don't see any `closed_on` field in your script. — Val, Oct 17 '19 at 12:28
Yes each document has a single value for `status_id`. I have edited the script now with `closed_on` field — Vinod Christwin, Oct 17 '19 at 13:00
ok, but you have to know that script fields execute in the context **of a single document**, so there's no way you can compute what you expect that way (i.e. with script fields). Maybe show some sample data and what you expect out of them — Val, Oct 17 '19 at 13:01
Ok, so you need to aggregate by `issue_id` and then find the number of days between the time the issue was opened (status 20) and when it was closed (status 51). Is that it? — Val, Oct 17 '19 at 13:48

score 1 · Answer 1 · answered Oct 17 '19 at 14:07

So, you need to aggregate by issue_id and then find the number of days between the time the issue was opened (status 20) and when it was closed (status 51).

You should be able to run the query below and find exactly what you need in the diff_days bucket field in the response:

GET test2/_search
{
  "size": 0,
  "aggs": {
    "issues": {
      "terms": {
        "field": "issue_id",
        "size": 10
      },
      "aggs": {
        "openedStatus": {
          "filter": {
            "term": {
              "status_id": "51"
            }
          },
          "aggs": {
            "openedDate": {
              "min": {
                "field": "created_on"
              }
            }
          }
        },
        "closedStatus": {
          "filter": {
            "term": {
              "status_id": "20"
            }
          },
          "aggs": {
            "closedDate": {
              "min": {
                "field": "closed_on"
              }
            }
          }
        },
        "diff_days": {
          "bucket_script": {
            "buckets_path": {
              "opened": "openedStatus>openedDate",
              "closed": "closedStatus>closedDate"
            },
            "script": "(params.closed - params.opened) / 86400000"
          }
        }
      }
    }
  }
}

Results =>

"issues" : {
  "doc_count_error_upper_bound" : 0,
  "sum_other_doc_count" : 0,
  "buckets" : [
    {
      "key" : 167893,
      "doc_count" : 3,
      "closedStatus" : {
        "doc_count" : 1,
        "closedDate" : {
          "value" : 1.565005134E12,
          "value_as_string" : "2019-08-05T11:38:54.000Z"
        }
      },
      "openedStatus" : {
        "doc_count" : 2,
        "openedDate" : {
          "value" : 1.559556432E12,
          "value_as_string" : "2019-06-03T10:07:12.000Z"
        }
      },
      "diff_days" : {
        "value" : 63.06368055555556                     <--- 63 days
      }
    }
  ]
}

I ran the query, it returned an error `illegal_argument_exception` the reason was `Fielddata is disabled on text fields by default.`. I changed the query from `issue_id` to `issue_id.keyword` it ran without errors but did not get the desired output. — Vinod Christwin, Oct 17 '19 at 14:35
`root_cause": [ { "type": "illegal_argument_exception", "reason": "Fielddata is disabled on text fields by default. Set fielddata=true on [issue_id] in order to load fielddata in memory by uninverting the inverted index. Note that this can however use significant memory. Alternatively use a keyword field instead." } ]` — Vinod Christwin, Nov 06 '19 at 14:26

How to write a scripted field to get the number of days between status transition

1 Answers1