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

**URL:** <https://discuss.elastic.co/t/view-where-metric-aggregation-is-done-on-a-substring-of-a-certain-field/210519>\
**Category:** Kibana\
**Created:** [December 4, 2019, 11:18am UTC](https://discuss.elastic.co/t/view-where-metric-aggregation-is-done-on-a-substring-of-a-certain-field/210519 "2019-12-04T11:18:31Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![dantonag](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dantonag/32/44130_2.png) [@dantonag](https://discuss.elastic.co/u/dantonag)\
**Post date:** [December 4, 2019, 11:18am UTC](https://discuss.elastic.co/t/view-where-metric-aggregation-is-done-on-a-substring-of-a-certain-field/210519/1 "2019-12-04T11:18:31Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![flash1293](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/flash1293/32/41227_2.png) [@flash1293](https://discuss.elastic.co/u/flash1293)\
**Post date:** [December 6, 2019, 9:01am UTC](https://discuss.elastic.co/t/view-where-metric-aggregation-is-done-on-a-substring-of-a-certain-field/210519/2 "2019-12-06T09:01:38Z")

</div>

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

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

```

 ![45](https://us1.discourse-cdn.com/elastic/original/3X/f/d/fd8632078f3a868a7810de05ab9a07fce5b63e14.png)

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](https://www.elastic.co/guide/en/elasticsearch/reference/current/grok-processor.html) in Elasticsearch itself.

---

<div class="post-metadata">

**Author:** ![dantonag](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dantonag/32/44130_2.png) [@dantonag](https://discuss.elastic.co/u/dantonag)\
**Post date:** [December 6, 2019, 1:23pm UTC](https://discuss.elastic.co/t/view-where-metric-aggregation-is-done-on-a-substring-of-a-certain-field/210519/3 "2019-12-06T13:23:22Z")

</div>

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:

 ![view](https://us1.discourse-cdn.com/elastic/original/3X/8/4/84fc9594e0816edab35bca73697e30e2be485b3f.png)

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.

---

<div class="post-metadata">

**Author:** ![flash1293](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/flash1293/32/41227_2.png) [@flash1293](https://discuss.elastic.co/u/flash1293)\
**Post date:** [December 6, 2019, 2:13pm UTC](https://discuss.elastic.co/t/view-where-metric-aggregation-is-done-on-a-substring-of-a-certain-field/210519/4 "2019-12-06T14:13:10Z")

</div>

Could you try setting the field to null explictly?

```auto
{
"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.

---

<div class="post-metadata">

**Author:** ![dantonag](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/dantonag/32/44130_2.png) [@dantonag](https://discuss.elastic.co/u/dantonag)\
**Post date:** [December 6, 2019, 2:41pm UTC](https://discuss.elastic.co/t/view-where-metric-aggregation-is-done-on-a-substring-of-a-certain-field/210519/5 "2019-12-06T14:41:27Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [January 3, 2020, 2:41pm UTC](https://discuss.elastic.co/t/view-where-metric-aggregation-is-done-on-a-substring-of-a-certain-field/210519/6 "2020-01-03T14:41:35Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.