# Ordering terms aggregation based on Pipeline metric **URL:** \ **Category:** Elasticsearch\ **Created:** [October 8, 2015, 11:49am UTC](https://discuss.elastic.co/t/ordering-terms-aggregation-based-on-pipeline-metric/31839 "2015-10-08T11:49:00Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![Nick\_Pentreath](https://avatars.discourse-cdn.com/v4/letter/n/7ea924/32.png) [@Nick\_Pentreath](https://discuss.elastic.co/u/Nick_Pentreath)\ **Post date:** [October 8, 2015, 11:49am UTC](https://discuss.elastic.co/t/ordering-terms-aggregation-based-on-pipeline-metric/31839/1 "2015-10-08T11:49:00Z")
Hi, I'm looking into the new pipeline aggregations for computing things like ratios per term bucket (e.g. click-through rate). I can compute the CTR per item using something like this: ```auto q = { "query": { "match_all" : {} }, "aggs" : { "ctr" : { "terms" : { "field" : "item_id", "size" : 10, }, "aggs": { "numer": { "filter": { "term": { "event": "click" } } }, "denom": { "filter": { "term": { "event": "impression" } } }, "ratio": { "bucket_script": { "buckets_path": { "numer_total": "numer>_count", "denom_total": "denom>_count" }, "script": "numer_total / denom_total" } } } } } } ``` Now, say I wanted to rank results by CTR, I try this: ```auto q = { "query": { "match_all" : {} }, "aggs" : { "ctr" : { "terms" : { "field" : "item_id", "size" : 10, "order": { "ratio" : "desc" } }, "aggs": { "numer": { "filter": { "term": { "event": "click" } } }, "denom": { "filter": { "term": { "event": "impression" } } }, "ratio": { "bucket_script": { "buckets_path": { "numer_total": "numer>_count", "denom_total": "denom>_count" }, "script": "numer_total / denom_total" } } } } } } ``` But it doesn't seem that the `ratio` aggregation can be used for sorting, even though it is a single-valued metric? I get this error: ```auto RemoteTransportException[[Madame Menace][127.0.0.1:9300][indices:data/read/search[phase/query]]]; nested: AggregationExecutionException[Invalid term-aggregator order path [ratio]. Unknown aggregation [ratio]]; Caused by: AggregationExecutionException[Invalid term-aggregator order path [ratio]. Unknown aggregation [ratio]] at org.elasticsearch.search.aggregations.support.AggregationPath.validate(AggregationPath.java:293) at org.elasticsearch.search.aggregations.bucket.terms.InternalOrder.validate(InternalOrder.java:145) at org.elasticsearch.search.aggregations.bucket.terms.InternalOrder.validate(InternalOrder.java:138) at org.elasticsearch.search.aggregations.bucket.terms.TermsAggregator.(TermsAggregator.java:143) ... ``` Is this not supported? Or is there another way to achieve the same result? I previously tried to use the result of a Scripted Metric Agg to sort, but that is not supported either. ---
**Author:** ![colings86](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/colings86/32/44960_2.png) [@colings86](https://discuss.elastic.co/u/colings86)\ **Post date:** [October 8, 2015, 12:16pm UTC](https://discuss.elastic.co/t/ordering-terms-aggregation-based-on-pipeline-metric/31839/2 "2015-10-08T12:16:13Z")
In order to be able to use an aggregation for sorting the terms aggregation it must currently be a numeric metric aggregation. Pipeline Aggregations are a different family of aggregations than the Metric Aggregations and cannot be used for sorting. You can't sort using a Pipeline aggregation because they are only executed in the reduce phase on the coordinating node and so you do not have the information on the shard in order to be able to sort the shards buckets to pick the top N to send to the coordinating node. The Scripted Metric Aggregation is part of the Metric Aggregation family but is not a Numeric Metric Aggregation since it returns an arbitrary object. However, there is an issue open in the Elasticsearch repo to add the ability to sort by an attribute of the Scripted Metric Aggregation: [https://github.com/elastic/elasticsearch/issues/8486](https://github.com/elastic/elasticsearch/issues/8486) ---
**Author:** ![Nick\_Pentreath](https://avatars.discourse-cdn.com/v4/letter/n/7ea924/32.png) [@Nick\_Pentreath](https://discuss.elastic.co/u/Nick_Pentreath)\ **Post date:** [October 8, 2015, 1:38pm UTC](https://discuss.elastic.co/t/ordering-terms-aggregation-based-on-pipeline-metric/31839/3 "2015-10-08T13:38:41Z")
Thanks - ok that makes sense if the pipelines are done only on the coordinating node. I did come across that issue but it seemed like it wasn't progressing. What are the major blockers remaining there (I see [https://github.com/elastic/elasticsearch/pull/8421](https://github.com/elastic/elasticsearch/pull/8421) is done but [https://github.com/elastic/elasticsearch/issues/8434](https://github.com/elastic/elasticsearch/issues/8434) is outstanding / closed)? It would seem on the surface that using getProperty to retrieve some value of the Scripted Metric Agg to sort on should be do-able? ---
**Author:** ![colings86](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/colings86/32/44960_2.png) [@colings86](https://discuss.elastic.co/u/colings86)\ **Post date:** [October 9, 2015, 8:38am UTC](https://discuss.elastic.co/t/ordering-terms-aggregation-based-on-pipeline-metric/31839/4 "2015-10-09T08:38:06Z")
Unfortunately it's not just a case of implementing the getProperty method to retrieve values. Currently the comparator that sorts the terms buckets does not use the getProperty method to sort, which is why it is limited to only Numeric Metric Aggregations since these have the value() or value(String) methods it currently uses. Moving to use the getProperty method is not a trivial change as both the comparator and probably some of the AggregationPath parsing code will need changing to allow for more complex paths within an aggregation. There aren't any blocker issues stopping this from progressing, it just needs someone to have the time to implement it. ---
**Author:** ![Nick\_Pentreath](https://avatars.discourse-cdn.com/v4/letter/n/7ea924/32.png) [@Nick\_Pentreath](https://discuss.elastic.co/u/Nick_Pentreath)\ **Post date:** [October 26, 2015, 1:23pm UTC](https://discuss.elastic.co/t/ordering-terms-aggregation-based-on-pipeline-metric/31839/5 "2015-10-26T13:23:55Z")
Ok. Out of interest - I have a decent understanding of USING aggregations but haven't delved into the code within ES much. Where would be a good place to start understanding the aggregation path parsing and sorting code, to better understand the work involved for this change? ---
**Author:** ![Laurent\_Querel](https://avatars.discourse-cdn.com/v4/letter/l/3ab097/32.png) [@Laurent\_Querel](https://discuss.elastic.co/u/Laurent_Querel)\ **Post date:** [December 9, 2015, 12:28pm UTC](https://discuss.elastic.co/t/ordering-terms-aggregation-based-on-pipeline-metric/31839/6 "2015-12-09T12:28:34Z")
The inability to sort the result by a scripted metric is major blocker to transform ES to usable analytics engine. IMO it's a big limitation to not support something like 'select sum(x)/sum(y) as ratio from table group by term order by ratio'. Is there an ETA for the support of this feature? ---
**Author:** ![Laurent\_Querel](https://avatars.discourse-cdn.com/v4/letter/l/3ab097/32.png) [@Laurent\_Querel](https://discuss.elastic.co/u/Laurent_Querel)\ **Post date:** [December 30, 2015, 10:21pm UTC](https://discuss.elastic.co/t/ordering-terms-aggregation-based-on-pipeline-metric/31839/7 "2015-12-30T22:21:18Z")
> [@Nick\_Pentreath](#): > > [Use JSONPath syntax for aggregation paths · Issue #8434 · elastic/elasticsearch · GitHub](https://github.com/elastic/elasticsearch/issues/8434) Here an attempt to fix this issue [Ordering term aggregation based on scripted metric. by lquerel · Pull Request #15718 · elastic/elasticsearch · GitHub](https://github.com/elastic/elasticsearch/pull/15718) specifically for scripted metrics returning a number. Because it's a vacation period, we probably need to wait a little before bit to get some feedback and may be an approval. ---
**Author:** ![gchen1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/gchen1/32/7495_2.png) [@gchen1](https://discuss.elastic.co/u/gchen1)\ **Post date:** [February 3, 2016, 12:01pm UTC](https://discuss.elastic.co/t/ordering-terms-aggregation-based-on-pipeline-metric/31839/8 "2016-02-03T12:01:26Z")
+1 It will be very helpful if ES can support this feature! ---
**Author:** ![buonzz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/buonzz/32/10192_2.png) [@buonzz](https://discuss.elastic.co/u/buonzz)\ **Post date:** [June 7, 2016, 8:59pm UTC](https://discuss.elastic.co/t/ordering-terms-aggregation-based-on-pipeline-metric/31839/9 "2016-06-07T20:59:38Z")
+1 I terribly need this feature. ---
**Author:** ![bentools](https://avatars.discourse-cdn.com/v4/letter/b/839c29/32.png) [@bentools](https://discuss.elastic.co/u/bentools)\ **Post date:** [November 21, 2016, 3:20pm UTC](https://discuss.elastic.co/t/ordering-terms-aggregation-based-on-pipeline-metric/31839/10 "2016-11-21T15:20:11Z")
> The inability to sort the result by a scripted metric is major blocker to transform ES to usable analytics engine. Couldn't agree more. I have migrated a whole app from SolR to ES, thinking that sorting and paginating aggregations would work out of the box. These 2 missing features require hacking the query to fetch 9999 results per aggregation, sort them manually, and slice the desired offset / limit. Add the pain of sorting on different types (aggregations can be made of dates, integers, strings, ...) Please, implement this and ES will be the more powerful BI engine ever ! ---
**Author:** ![small-tomorrow](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/small-tomorrow/32/13319_2.png) [@small-tomorrow](https://discuss.elastic.co/u/small-tomorrow)\ **Post date:** [March 2, 2017, 9:22am UTC](https://discuss.elastic.co/t/ordering-terms-aggregation-based-on-pipeline-metric/31839/11 "2017-03-02T09:22:44Z")
+1 considering you have to get top n ratio , the results may be wrong if the size in your terms is too small . but a large size number like 10,000 would hurt performance . > [@Nick\_Pentreath](#): > > "terms" : { > "field" : "item\_id", > "size" : 10, > "order": { > "ratio" : "desc" > } > } ---
**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, 10:02pm UTC](https://discuss.elastic.co/t/ordering-terms-aggregation-based-on-pipeline-metric/31839/12 "2017-07-05T22:02:44Z")