# Invalid aggregation order path

**URL:** <https://discuss.elastic.co/t/invalid-aggregation-order-path/228472>\
**Category:** Elasticsearch\
**Created:** [April 17, 2020, 8:10am UTC](https://discuss.elastic.co/t/invalid-aggregation-order-path/228472 "2020-04-17T08:10:13Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Damiano](https://avatars.discourse-cdn.com/v4/letter/d/ebca7d/32.png) [@Damiano](https://discuss.elastic.co/u/Damiano)\
**Post date:** [April 17, 2020, 8:10am UTC](https://discuss.elastic.co/t/invalid-aggregation-order-path/228472/1 "2020-04-17T08:10:14Z")

</div>

Hello everybody!

I am using ElasticSearch 7.6 with a `test` index that has the following mapping:

```auto
{
  "test" : {
    "mappings" : {
      "properties" : {
        "amount" : {
          "type" : "long"
        },
        "group_id" : {
          "type" : "long"
        },
        "id" : {
          "type" : "long"
        }
      }
    }
  }
}

```

Now, i am using the following query:

```auto
{
  "size": 0,
  "track_total_hits": false,
  "aggs": {
    "strategies": {
      "terms": {
        "field": "group_id",
        "order": {
          "drawdown": "asc"
        },
        "size": 100,
        "include": {
          "partition": 1,
          "num_partitions": 100
        }
      },
      "aggs": {
        "drawdown": {
          "scripted_metric": {
            "init_script": "state.id = 0; state.cumsum = 0; state.max = 0; state.min = 0; state.diff = 0",
            "map_script": "state.cumsum += doc.amount.value; if (state.cumsum>state.max) {state.max=state.cumsum;state.id=doc.id.value} if (state.max - state.cumsum >= state.diff) {state.min=state.cumsum;state.diff=state.max - state.cumsum;}",
            "combine_script": "return state;",
            "reduce_script": "states.sort((x, y) -> x.id - y.id); int min = states[0].min; int max = states[0].max; for(int i = 1; i < states.length; i++) { int nextMin = states[i].min; int nextMax = states[i].max; if (max > nextMax && min > nextMin) { min = nextMin; } else if (max < nextMax && max-min < nextMax-nextMin) { max = nextMax; min = nextMin;} } return max-min;"
          }
        }
      }
    }
  }
}

```

If i remove the order part the query works good, i am referring to the following piece:

```auto
"order": {"drawdown": "asc"}

```

How can i sort by the value returned by **drawdown** aggregation?

The error that i get is:

```auto
{
  "error": {
    "root_cause": [
      {
        "type": "aggregation_execution_exception",
        "reason": "Invalid aggregation order path [drawdown]. Buckets can only be sorted on a sub-aggregator path that is built out of zero or more single-bucket aggregations within the path and a final single-bucket or a metrics aggregation at the path end."
      }
    ],
    "type": "search_phase_execution_exception",
    "reason": "all shards failed",
    "phase": "query",
    "grouped": true,
    "failed_shards": [
      {
        "shard": 0,
        "index": "test",
        "node": "jHJ3_eOLQ3iZZeHZPI_dFA",
        "reason": {
          "type": "aggregation_execution_exception",
          "reason": "Invalid aggregation order path [drawdown]. Buckets can only be sorted on a sub-aggregator path that is built out of zero or more single-bucket aggregations within the path and a final single-bucket or a metrics aggregation at the path end."
        }
      }
    ]
  },
  "status": 500
}

```

---

<div class="post-metadata">

**Author:** ![Damiano](https://avatars.discourse-cdn.com/v4/letter/d/ebca7d/32.png) [@Damiano](https://discuss.elastic.co/u/Damiano)\
**Post date:** [May 2, 2020, 1:04am UTC](https://discuss.elastic.co/t/invalid-aggregation-order-path/228472/2 "2020-05-02T01:04:10Z")

</div>

Any workaround?

---

<div class="post-metadata">

**Author:** ![Luca\_Belluccini](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/luca_belluccini/32/33239_2.png) [@Luca\_Belluccini](https://discuss.elastic.co/u/Luca_Belluccini)\
**Post date:** [May 2, 2020, 1:21am UTC](https://discuss.elastic.co/t/invalid-aggregation-order-path/228472/3 "2020-05-02T01:21:35Z")

</div>

Hello @Damiano

I see you've already posted on [https://github.com/elastic/elasticsearch/issues/8486](https://github.com/elastic/elasticsearch/issues/8486).

I am wondering if it would be possible to test with [`composite aggregations`](https://www.elastic.co/guide/en/elasticsearch/reference/7.6/search-aggregations-bucket-composite-aggregation.html#_order).

Another approach would be to use the aggregation you've written in a [Transform Job](https://www.elastic.co/guide/en/elasticsearch/reference/current/ecommerce-transforms.html) so you will have one document per `group_id` and you'll be able to do a search over the documents sorting by `drawdown`.

---

<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:** [May 30, 2020, 1:21am UTC](https://discuss.elastic.co/t/invalid-aggregation-order-path/228472/4 "2020-05-30T01:21:39Z")

</div>

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