Visualization of scripted field using relative time but sometimes field is null

[Goishin]

I'm trying to create a metric that lists how long a development pipeline has been broken for. I compute this field in the script I use to upload my documents with. The field is marked as relative time in my index. What I'd really like for this to do is when a pipeline breaks, we mark the time in the field "results.failing_since". Then, when the pipeline isn't broken, meaning all my tests pass, then the field is "null".

When I add this to a metric, it always skips over the "null" documents. Then I created a script to use, that when the field is "null", I'll just pass a string saying "pipeline not broken". However, that fails because apparently the metric visualization is expecting a number. And if I just have it return a 0, instead, then I get an exception:
class_cast_exception: org.elasticsearch.script.JodaCompatibleZonedDateTime cannot be cast to java.lang.Number"

When I try to do this in a data table, then the timestamp won't be rendered as relative time. AND, the field is missing on some pipelines, too. So, I have this state where a field can or cannot be present, can or cannot be null, and should be rendered as relative time.

I've tried something similar to this on a datatable. But this can still result in an exception, and the datatable won't format this as relative time (if it isn't busy throwing exceptions, that is):

{
"script": "if (doc.containsKey('results.failing_since') && doc['results.failing_since'].size() != 0 && doc['results.failing_since'].value != null) { return doc['results.failing_since'].value;} else { return 'Not failing.';}"
}

How do I get this data visualized!!!

[markov00 (Marco Vettorello)]

Hi @Goishin
could you send me an example of what the visualization should looks like? it's just a metric visualization with the sum of how many seconds/minutes/hours the pipelines have been broken?
How your documents are structured? because I don't understand your concerns on the skipped null documents since they should not count as part of the "failing" pipeline.

In any case: the exception seems related to one possible root cause. The scripted field is of type Number but you are returning the value of results.failing_since that is a Date value. You can cast it to Unix timestamp with something like doc['@timestamp'].value.toInstant().toEpochMilli();
If you then format your scripted field as Duration it should return a nice formatted value on the visualization