Subaggregation from scripted field

Elastic Stack Elasticsearch
1

Hi,

I posted the same question at SO but I thought I may have better luck here :slight_smile:

For simplicity, let's say I have two fields: TRIP_START_DATE (in date format) and TRIP_NIGHTS.

I can run a simple aggregation which will convert dates to date of the week:

> aggs <- '{
>         "aggs": {
>           "dept": {
>             "filter": {
>               "range": {
>                 "TRIP_START_DATE": {
>                   "gte": "2014-01-01",
>                   "lte": "2014-12-31"
>                 }
>               }
>             },
>             "aggs": {
>               "weekday": {
>                 "terms": {
>                   "script": "Date date = new Date(doc[\'TRIP_START_DATE\'].value) ; java.text.SimpleDateFormat format = new java.text.SimpleDateFormat(\'EEE\');format.format(date)" 
>                 }
>               }
>             }
>           }
>         }
>       }'

Is it possible to add another aggregation which returns the average TRIP_NIGHTS per weekday?

Thanks for the help.

Carlos

2

This is my progress. I can run the following query and "create" the fields which I want to work with.

  aggs <- '{
        "query": {
          "filtered": {
            "filter": {
              "range": {
                "TRIP_START_DATE": {
                  "gte": "2014-01-01",
                  "lte": "2014-12-31"
                }
              }
            }
          }
        },
        "script_fields": {
          "weekday": {
            "script": "Date date = new Date(doc[\'TRIP_START_DATE\'].value) ; java.text.SimpleDateFormat format = new java.text.SimpleDateFormat(\'EEE\');format.format(date)" 
          },
          "nights": {
            "script": "doc[\'TRIP_NIGHTS\'].value"
          },
          "paid": {
            "script": "doc[\'PAID_FARE_USD\'].value"
          }
        },
        "aggs": {
          "dday": {
            "terms": {
              "field": "doc[\'weekday\'].value"
            }
          }
        }
      }'

But the aggreggation comes up empty. Changing "field" by "script" makes ES very unhappy. Thus, how do I access the new fields?

Any ideas?

3

Script fields only help add additional per-hit information in the response, they can't be re-used to compute aggregations.

Is it possible to add another aggregation which returns the average TRIP_NIGHTS per weekday?

You should be able to do something like this: (untested)

{
  "aggs": {
    "by_weekday": {
      "terms": {
        "script": "Date date = new Date(doc[\'TRIP_START_DATE\'].value) ; java.text.SimpleDateFormat format = new java.text.SimpleDateFormat(\'EEE\');format.format(date)"
      },
      "aggs": {
        "avg_trip_nights": {
          "avg": {
            "field": "TRIP_NIGHTS"
          }
        }
      }
    }
  }
}