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-01 07:35:11Z

32

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 1, 2015 at 7:35

answered Jun 1, 2015 at 7:27

Val

218k14 gold badges377 silver badges384 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Or Weinberger Over a year ago

This works great, thank you. This produces a float value, is there a way to run another script that will round the number?

Val Over a year ago

I've updated my answer, you can use any Math function like Math.ceil(), Math.floor() or Math.round() as you see fit.

Val Over a year ago

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?

Or Weinberger Over a year ago

If no other way, then yes, I'll handle in on the client side. Thanks for all your help.

davids Over a year ago

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 (elastic.co/guide/en/elasticsearch/reference/current/…). 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?

|

Collectives™ on Stack Overflow

Elasticsearch - Aggregate a script field

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related