Elasticsearch - Aggregate a script field

Question

I'm trying to create a script field that will calculate a time difference between two timestamps and then aggregate an avg on that script field.

I first tried:

{
   "query": {
      "filtered": {
         "query": {
            "match_all": {}
         },
         "filter": {
            "and": [
               {
                  "exists": {
                     "field": "time.new_time"
                  }
               },
               {
                  "exists": {
                     "field": "time.first_alert_time"
                  }
               }
            ]
         }
      }
   },
   "script_fields": {
      "timedifference": {
         "script": "doc['time.new_time'].value - doc['time.first_alert_time'].value"
      }
   },
   "aggs": {
      "avg_timedifference": {
         "avg": {
            "field" : "timedifference"
         }
      }
   }
}

Which resulted in null value under the aggregated avg avg_timedifference.

Then I tried:

{
   "query": {
      "filtered": {
         "query": {
            "match_all": {}
         },
         "filter": {
            "and": [
               {
                  "exists": {
                     "field": "time.new_time"
                  }
               },
               {
                  "exists": {
                     "field": "time.first_alert_time"
                  }
               }
            ]
         }
      }
   },
   "script_fields": {
      "timedifference": {
         "script": "doc['time.new_time'].value - doc['time.first_alert_time'].value"
      }
   },
   "aggs": {
      "avg_timedifference": {
         "avg": {
            "script" : "doc['timedifference'].value"
         }
      }
   }
}

Which generated an error message saying: "No field found for [timedifference] in mapping"

Val · Accepted Answer · 2015-06-01T07:35:11.307

29

How about simply moving the script to the aggregation?

{
   "query": {
      "filtered": {
         "query": {
            "match_all": {}
         },
         "filter": {
            "and": [
               {
                  "exists": {
                     "field": "time.new_time"
                  }
               },
               {
                  "exists": {
                     "field": "time.first_alert_time"
                  }
               }
            ]
         }
      }
   },
   "aggs": {
      "avg_timedifference": {
         "avg": {
            "script" : "Math.ceil(doc['time.new_time'].value - doc['time.first_alert_time'].value)"
         }
      }
   }
}

edited Jun 01 '15 at 07:35

answered Jun 01 '15 at 07:27

Val

207,596
13
358
360

This works great, thank you. This produces a float value, is there a way to run another script that will round the number? – Or Weinberger Jun 01 '15 at 07:34
I've updated my answer, you can use any `Math` function like `Math.ceil()`, `Math.floor()` or `Math.round()` as you see fit. – Val Jun 01 '15 at 07:36
Hmm, yes of course since the result of the `avg` aggregation is most probably not an integer. Can you handle it on the client side? – Val Jun 01 '15 at 07:40
If no other way, then yes, I'll handle in on the client side. Thanks for all your help. – Or Weinberger Jun 01 '15 at 07:43
Thanks @Val, this is just fantastic, it gave me the hint I needed to complete my query. In my case, I just wanted to create an histogram based on the output of a script, and kept on failing. I got lost into the ES docs, hoping that I could find something useful in the Histogram Agg section (https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-histogram-aggregation.html). However, in that page there's not a single reference to scripts. How do you normally figure out how to write queries in ES? Are the docs sufficient or you check other sources? – davids Jun 20 '19 at 09:11
@davids In the [date_histogram docs](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-bucket-datehistogram-aggregation.html#_scripts_2) there's a mention that scripts are supported. In addition to the docs (which I think are of great quality by the way, and anyone can improve them), I'm also very often looking into the [code base and issues](https://github.com/elastic/elasticsearch) – Val Jun 20 '19 at 09:20
What about if I need the scripted field in hits and in aggregation as well? Is it possible to define the scripted field only once (maybe better that repeating the script in the field and in the aggregation) – Lubbo Jul 16 '19 at 15:20
@Lubbo feel free to ask a new question with your exact needs, – Val Jul 17 '19 at 02:29

Elasticsearch - Aggregate a script field

1 Answers1

Linked