# Subaggregation from scripted field

**URL:** <https://discuss.elastic.co/t/subaggregation-from-scripted-field/26499>\
**Category:** Elasticsearch\
**Created:** [July 29, 2015, 3:10pm UTC](https://discuss.elastic.co/t/subaggregation-from-scripted-field/26499 "2015-07-29T15:10:37Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![carlos](https://avatars.discourse-cdn.com/v4/letter/c/ecae2f/32.png) [@carlos](https://discuss.elastic.co/u/carlos)\
**Post date:** [July 29, 2015, 3:10pm UTC](https://discuss.elastic.co/t/subaggregation-from-scripted-field/26499/1 "2015-07-29T15:10:37Z")

</div>

Hi,

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

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

---

<div class="post-metadata">

**Author:** ![carlos](https://avatars.discourse-cdn.com/v4/letter/c/ecae2f/32.png) [@carlos](https://discuss.elastic.co/u/carlos)\
**Post date:** [July 29, 2015, 4:03pm UTC](https://discuss.elastic.co/t/subaggregation-from-scripted-field/26499/2 "2015-07-29T16:03:44Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![jpountz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jpountz/32/45836_2.png) [@jpountz](https://discuss.elastic.co/u/jpountz)\
**Post date:** [August 3, 2015, 8:12am UTC](https://discuss.elastic.co/t/subaggregation-from-scripted-field/26499/3 "2015-08-03T08:12:32Z")

</div>

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)

```bash
{
  "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"
          }
        }
      }
    }
  }
}

```

---

<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:** [July 5, 2017, 11:57pm UTC](https://discuss.elastic.co/t/subaggregation-from-scripted-field/26499/4 "2017-07-05T23:57:46Z")

</div>