[FIXED] How to calculate the number of months between startDate and endDate in elasticsearch if the endDate value is not present?

Issue

The problem I'm facing right now is to calculate the range of months between "startDate" & "endDate". If the user has both of these values I'm not having any problem to calculate it but when he/she is still employed (which I get from the isEmployed property, which is a boolean) the "endDate" value is null which is OK but when I try to override that value I'm getting a casting error in the field runtime_mapping.

index mapping:

PUT {{elasticUrl}}/user-experiences
{
    "mappings": {
        "properties": {
            "userId": {
                "type": "integer"
            },
            "experienceId": {
                "type": "integer"
            },
            "startDate": {
                "type": "date"
            },
            "endDate": {
                "type": "date"
            }
        }
    }
}

aggregation:

POST {{elasticUrl}}/user-experiences/_search
{
    "runtime_mappings": {
        "endDate": {
            "type": "date",
            "script": {
                "source": "if (doc['isEmployed'].value == true) { emit(new Date().getTime()) } else { emit (params._source['endDate']) }"
            }
        }
    },
    "size": 0,
    "aggregations": {
        "group_by": {
            "terms": {
                "field": "userId"
            },
            "aggregations": {
                "start": {
                    "sum": {
                        "field": "startDate"
                    }
                },
                "end": {
                    "sum": {
                        "field": "endDate"
                    }
                },
                "duration": {
                    "bucket_script": {
                        "buckets_path": {
                            "start": "start.value",
                            "end": "end.value"
                        },
                        "script": {
                            "params": {
                                "month_in_milliseconds": 2628000000
                            },
                            "source": "Math.round((params.end - params.start) / params.month_in_milliseconds)"
                        }
                    }
                },
                "duration_bucket_filter": {
                    "bucket_selector": {
                        "buckets_path": {
                            "durationBucket": "duration"
                        },
                        "script": {
                            "params": {
                                "number_of_months": 1
                            },
                            "source": "params.durationBucket >= params.number_of_months"
                        }
                    }
                }
            }
        }
    }
}

Error message is as follows: reason: class java.lang.String cannot be cast to class java.lang.Number (java.lang.String and java.lang.Number are in module java.base of loader 'bootstrap')

{
    "error": {
        "root_cause": [
            {
                "type": "script_exception",
                "reason": "runtime error",
                "script_stack": [
                    "emit (params._source['endDate']) }",
                    "                    ^---- HERE"
                ],
                "script": "if (doc['isEmployed'].value == true) { emit(new Date().getTime()) } else { emit (params._source['endDate']) }",
                "lang": "painless",
                "position": {
                    "offset": 95,
                    "start": 75,
                    "end": 109
                }
            }
        ],
        "type": "search_phase_execution_exception",
        "reason": "all shards failed",
        "phase": "query",
        "grouped": true,
        "failed_shards": [
            {
                "shard": 0,
                "reason": {
                    "type": "script_exception",
                    "reason": "runtime error",
                    "script_stack": [
                        "emit (params._source['endDate']) }",
                        "                    ^---- HERE"
                    ],
                    "script": "if (doc['isEmployed'].value == true) { emit(new Date().getTime()) } else { emit (params._source['endDate']) }",
                    "lang": "painless",
                    "position": {
                        "offset": 95,
                        "start": 75,
                        "end": 109
                    },
                    "caused_by": {
                        "type": "class_cast_exception",
                        "reason": "class java.lang.String cannot be cast to class java.lang.Number (java.lang.String and java.lang.Number are in module java.base of loader 'bootstrap')"
                    }
                }
            }
        ]
    },
    "status": 400
}

Solution

To get around this issue I created a updatedEndDate runtime filed that is of type date and instead of the original end date in the mapping I used the runtime field in my aggregation, the solution looks something like this:

    "runtime_mappings": {
        "updatedEndDate": {
            "type": "date",
            "script": {
                "source": "if (doc['isEmployed'].value.equals(true)) { emit(new Date().getTime()) } else { emit(doc['endDate'].value.millis) } "
            }
        }
    }

Answered By - Dženan Džafić

Answer Checked By - Dawn Plyler (PHPFixing Volunteer)