View where metric aggregation is done on a substring of a certain field

[dantonag (Gabriele D'antona)]

Hello,
I have an index with some documents that are like:

The service was completed in 2.5 seconds
The service was completed in 1.7 seconds
The service was completed in 3.2 seconds
...

I want to build a view that will show the average service time, so I have to extract the numeric values from each field and do the average.

I can't use a scripted field.
Yes, I know I can do this at ingestion time, but I can't at this moment.

I have tried to use the "JSON input" field, but with no luck.

Thanks

[flash1293 (Joe Reuter)]

You can do this with "JSON Input" by using the script property of the average aggregation:

{
  "script" : {
    "source" : "your painless script extracting the number goes here"
  }
}

It's irrelevant what you select as "Field", because the script will overwrite this value.

However it is highly recommended to do this during ingest time for performance reasons - check with the people controlling your Elasticsearch cluster. It's even possible to configure this without an additional service by using ingest processors in Elasticsearch itself.

[dantonag (Gabriele D'antona)]

I tried, but I obtain an empty view. I'm using Kibana 6.8 against Elastic 6.8.

My script in JSON Input is:

{
"script":{
"source":"def timespent=doc['Evento.PARAMETRO1.raw'].value;timespent=timespent.replace('Tempo impiegato ','');timespent=timespent.replace(' secondi','');float ftime=Float.parseFloat(timespent);return ftime;"
}
}

The same script, used in a query (dev tools) works.

This is my view:

Edit: after more analysis, it seems that in the query generated by the view, the "Field" specified for the aggregation is not ignored, but is used in the aggregation as "field" parameter. If I execute that query, this field "wins" over the script, so the resulting value is null.

[flash1293 (Joe Reuter)]

Could you try setting the field to null explictly?

{
"script":{
"source":"def timespent=doc['Evento.PARAMETRO1.raw'].value;timespent=timespent.replace('Tempo impiegato ','');timespent=timespent.replace(' secondi','');float ftime=Float.parseFloat(timespent);return ftime;"
},
"field": null
}

If that doesn't help, could you share the complete request and response as shown in the inspect panel? Additionally you could check whether there are some documents where this script doesn't work and returns NaN or something like this.

[dantonag (Gabriele D'antona)]

Works with "field":null!!! Thanks!