# Scripting aggregation order **URL:** \ **Category:** Elasticsearch\ **Created:** [July 29, 2014, 6:50pm UTC](https://discuss.elastic.co/t/scripting-aggregation-order/18959 "2014-07-29T18:50:41Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![Ivan](https://avatars.discourse-cdn.com/v4/letter/i/df788c/32.png) [@Ivan](https://discuss.elastic.co/u/Ivan)\ **Post date:** [July 29, 2014, 6:50pm UTC](https://discuss.elastic.co/t/scripting-aggregation-order/18959/1 "2014-07-29T18:50:41Z")
Would it be possible to script the sort order of aggregations? Let me explain with a contrived example: { "aggs": { "agg1": { "terms": { "field": "somefield", "order": { "avg1": "desc" }, "size" : 100 }, "aggs": { "avg1": { "avg": { "field": "someotherfield" } } } } } The above example will return an aggregation where the buckets are sorted based on the average of some other field of the bucketized documents. Pretty easy. What I would actually want is to do some more complex math such as the average of the bucketized documents times the number of documents in the bucket. The metric aggregation script, as far as I can tell, does not have access to information of the bucket it is in. { "script" : "doc['someotherfield'].value \* ?bucket.count?" } The aggregation order also does not offer script based sorting. ``` "order": { "_script": { "script": "bucket.avg * bucket.count", "type": "number", "order": "desc" } } ``` Very pseudo code example above. My hope is that perhaps I missed the fact that Elasticsearch does support one of these two scenarios. If not, are there any workarounds to fine-tune sort order? Secondary sorting would also help. Cheers, Ivan -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CALY%3DcQCLxU%3DFTddW%3DesWLWHz0G-SO\_oN7Zb4OE4o%3DVdwyRw%2BkQ%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CALY%3DcQCLxU%3DFTddW%3DesWLWHz0G-SO_oN7Zb4OE4o%3DVdwyRw%2BkQ%40mail.gmail.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**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:** [July 29, 2014, 7:50pm UTC](https://discuss.elastic.co/t/scripting-aggregation-order/18959/2 "2014-07-29T19:50:42Z")
Hi Ivan, This is not supported but there is an open issue that aims at making this kind of things possible > > > Terms aggregations only allow to sort based on the UTF-8 bytes of the term, its 鈥ount or a sub-aggregation. > > We should add more flexibility here to allow for doing things like having compound orders, sorting by collation or by a script that would apply to the sub aggregations. On Tue, Jul 29, 2014 at 8:50 PM, Ivan Brusic [ivan@brusic.com](mailto:ivan@brusic.com) wrote: > Would it be possible to script the sort order of aggregations? Let me > explain with a contrived example: > > { > "aggs": { > "agg1": { > "terms": { > "field": "somefield", > "order": { > "avg1": "desc" > }, > "size" : 100 > }, > "aggs": { > "avg1": { > "avg": { > "field": "someotherfield" > } > } > } > } > } > > The above example will return an aggregation where the buckets are sorted > based on the average of some other field of the bucketized documents. > Pretty easy. What I would actually want is to do some more complex math > such as the average of the bucketized documents times the number of > documents in the bucket. > > The metric aggregation script, as far as I can tell, does not have access > to information of the bucket it is in. > > { "script" : "doc['someotherfield'].value \* ?bucket.count?" } > > The aggregation order also does not offer script based sorting. > > ``` > "order": { > "_script": { > "script": "bucket.avg * bucket.count", > "type": "number", > "order": "desc" > } > } > > ``` > > Very pseudo code example above. My hope is that perhaps I missed the fact > that Elasticsearch does support one of these two scenarios. If not, are > there any workarounds to fine-tune sort order? Secondary sorting would also > help. > > Cheers, > > Ivan > > -- > You received this message because you are subscribed to the Google Groups > "elasticsearch" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > To view this discussion on the web visit > [https://groups.google.com/d/msgid/elasticsearch/CALY%3DcQCLxU%3DFTddW%3DesWLWHz0G-SO\_oN7Zb4OE4o%3DVdwyRw%2BkQ%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CALY%3DcQCLxU%3DFTddW%3DesWLWHz0G-SO_oN7Zb4OE4o%3DVdwyRw%2BkQ%40mail.gmail.com) > [https://groups.google.com/d/msgid/elasticsearch/CALY%3DcQCLxU%3DFTddW%3DesWLWHz0G-SO\_oN7Zb4OE4o%3DVdwyRw%2BkQ%40mail.gmail.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/CALY%3DcQCLxU%3DFTddW%3DesWLWHz0G-SO_oN7Zb4OE4o%3DVdwyRw%2BkQ%40mail.gmail.com?utm_medium=email&utm_source=footer) > . > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- Adrien Grand -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAL6Z4j6tMy7AYLv4XBRW\_kDii1N2kvPibtawUMWQVbZ5s7bkBg%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAL6Z4j6tMy7AYLv4XBRW_kDii1N2kvPibtawUMWQVbZ5s7bkBg%40mail.gmail.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![Ivan](https://avatars.discourse-cdn.com/v4/letter/i/df788c/32.png) [@Ivan](https://discuss.elastic.co/u/Ivan)\ **Post date:** [July 29, 2014, 9:43pm UTC](https://discuss.elastic.co/t/scripting-aggregation-order/18959/3 "2014-07-29T21:43:31Z")
Thanks Adrien. What about secondary sorting? The issue talks more about collation, so I am not sure if it is more an umbrella issue for all aggregation sorting concepts. Should I open an issue, comment on the existing one, or just leave you be? 馃檪 Cheers, Ivan On Tue, Jul 29, 2014 at 12:50 PM, Adrien Grand \< [adrien.grand@elasticsearch.com](mailto:adrien.grand@elasticsearch.com)\> wrote: > Hi Ivan, > > This is not supported but there is an open issue that aims at making this > kind of things possible > [Aggregations: Make order more flexible for terms 路 Issue #6917 路 elastic/elasticsearch 路 GitHub](https://github.com/elasticsearch/elasticsearch/issues/6917) > > On Tue, Jul 29, 2014 at 8:50 PM, Ivan Brusic [ivan@brusic.com](mailto:ivan@brusic.com) wrote: > > > Would it be possible to script the sort order of aggregations? Let me > > explain with a contrived example: > > > > { > > "aggs": { > > "agg1": { > > "terms": { > > "field": "somefield", > > "order": { > > "avg1": "desc" > > }, > > "size" : 100 > > }, > > "aggs": { > > "avg1": { > > "avg": { > > "field": "someotherfield" > > } > > } > > } > > } > > } > > > > The above example will return an aggregation where the buckets are sorted > > based on the average of some other field of the bucketized documents. > > Pretty easy. What I would actually want is to do some more complex math > > such as the average of the bucketized documents times the number of > > documents in the bucket. > > > > The metric aggregation script, as far as I can tell, does not have access > > to information of the bucket it is in. > > > > { "script" : "doc['someotherfield'].value \* ?bucket.count?" } > > > > The aggregation order also does not offer script based sorting. > > > > ``` > > "order": { > > "_script": { > > "script": "bucket.avg * bucket.count", > > "type": "number", > > "order": "desc" > > } > > } > > > > ``` > > > > Very pseudo code example above. My hope is that perhaps I missed the fact > > that Elasticsearch does support one of these two scenarios. If not, are > > there any workarounds to fine-tune sort order? Secondary sorting would also > > help. > > > > Cheers, > > > > Ivan > > > > -- > > You received this message because you are subscribed to the Google Groups > > "elasticsearch" group. > > To unsubscribe from this group and stop receiving emails from it, send an > > email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > > To view this discussion on the web visit > > [https://groups.google.com/d/msgid/elasticsearch/CALY%3DcQCLxU%3DFTddW%3DesWLWHz0G-SO\_oN7Zb4OE4o%3DVdwyRw%2BkQ%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CALY%3DcQCLxU%3DFTddW%3DesWLWHz0G-SO_oN7Zb4OE4o%3DVdwyRw%2BkQ%40mail.gmail.com) > > [https://groups.google.com/d/msgid/elasticsearch/CALY%3DcQCLxU%3DFTddW%3DesWLWHz0G-SO\_oN7Zb4OE4o%3DVdwyRw%2BkQ%40mail.gmail.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/CALY%3DcQCLxU%3DFTddW%3DesWLWHz0G-SO_oN7Zb4OE4o%3DVdwyRw%2BkQ%40mail.gmail.com?utm_medium=email&utm_source=footer) > > . > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). > > -- > Adrien Grand > > -- > You received this message because you are subscribed to the Google Groups > "elasticsearch" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > To view this discussion on the web visit > [https://groups.google.com/d/msgid/elasticsearch/CAL6Z4j6tMy7AYLv4XBRW\_kDii1N2kvPibtawUMWQVbZ5s7bkBg%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAL6Z4j6tMy7AYLv4XBRW_kDii1N2kvPibtawUMWQVbZ5s7bkBg%40mail.gmail.com) > [https://groups.google.com/d/msgid/elasticsearch/CAL6Z4j6tMy7AYLv4XBRW\_kDii1N2kvPibtawUMWQVbZ5s7bkBg%40mail.gmail.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/CAL6Z4j6tMy7AYLv4XBRW_kDii1N2kvPibtawUMWQVbZ5s7bkBg%40mail.gmail.com?utm_medium=email&utm_source=footer) > . > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CALY%3DcQAW2OeHOruNhgqc-72zbKUCUqsyE3Ff1zOfCpHTMmu0%3Dw%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CALY%3DcQAW2OeHOruNhgqc-72zbKUCUqsyE3Ff1zOfCpHTMmu0%3Dw%40mail.gmail.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![jprante](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jprante/32/44941_2.png) [@jprante](https://discuss.elastic.co/u/jprante)\ **Post date:** [July 29, 2014, 10:46pm UTC](https://discuss.elastic.co/t/scripting-aggregation-order/18959/4 "2014-07-29T22:46:16Z")
Unicode collations know the concept of secondary sorting and much more, see > **[ICU User Guide](https://unicode-org.github.io/icu/userguide/)** > > ICU User Guide J枚rg On Tue, Jul 29, 2014 at 11:43 PM, Ivan Brusic [ivan@brusic.com](mailto:ivan@brusic.com) wrote: > Thanks Adrien. What about secondary sorting? The issue talks more about collation, > so I am not sure if it is more an umbrella issue for all aggregation > sorting concepts. Should I open an issue, comment on the existing one, or > just leave you be? 馃檪 > > Cheers, > > Ivan > > On Tue, Jul 29, 2014 at 12:50 PM, Adrien Grand \< > [adrien.grand@elasticsearch.com](mailto:adrien.grand@elasticsearch.com)\> wrote: > > > Hi Ivan, > > > > This is not supported but there is an open issue that aims at making this > > kind of things possible > > [Aggregations: Make order more flexible for terms 路 Issue #6917 路 elastic/elasticsearch 路 GitHub](https://github.com/elasticsearch/elasticsearch/issues/6917) > > > > On Tue, Jul 29, 2014 at 8:50 PM, Ivan Brusic [ivan@brusic.com](mailto:ivan@brusic.com) wrote: > > > > > Would it be possible to script the sort order of aggregations? Let me > > > explain with a contrived example: > > > > > > { > > > "aggs": { > > > "agg1": { > > > "terms": { > > > "field": "somefield", > > > "order": { > > > "avg1": "desc" > > > }, > > > "size" : 100 > > > }, > > > "aggs": { > > > "avg1": { > > > "avg": { > > > "field": "someotherfield" > > > } > > > } > > > } > > > } > > > } > > > > > > The above example will return an aggregation where the buckets are > > > sorted based on the average of some other field of the bucketized > > > documents. Pretty easy. What I would actually want is to do some more > > > complex math such as the average of the bucketized documents times the > > > number of documents in the bucket. > > > > > > The metric aggregation script, as far as I can tell, does not have > > > access to information of the bucket it is in. > > > > > > { "script" : "doc['someotherfield'].value \* ?bucket.count?" } > > > > > > The aggregation order also does not offer script based sorting. > > > > > > ``` > > > "order": { > > > "_script": { > > > "script": "bucket.avg * bucket.count", > > > "type": "number", > > > "order": "desc" > > > } > > > } > > > > > > ``` > > > > > > Very pseudo code example above. My hope is that perhaps I missed the > > > fact that Elasticsearch does support one of these two scenarios. If not, > > > are there any workarounds to fine-tune sort order? Secondary sorting would > > > also help. > > > > > > Cheers, > > > > > > Ivan > > > > > > -- > > > You received this message because you are subscribed to the Google > > > Groups "elasticsearch" group. > > > To unsubscribe from this group and stop receiving emails from it, send > > > an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > > > To view this discussion on the web visit > > > [https://groups.google.com/d/msgid/elasticsearch/CALY%3DcQCLxU%3DFTddW%3DesWLWHz0G-SO\_oN7Zb4OE4o%3DVdwyRw%2BkQ%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CALY%3DcQCLxU%3DFTddW%3DesWLWHz0G-SO_oN7Zb4OE4o%3DVdwyRw%2BkQ%40mail.gmail.com) > > > [https://groups.google.com/d/msgid/elasticsearch/CALY%3DcQCLxU%3DFTddW%3DesWLWHz0G-SO\_oN7Zb4OE4o%3DVdwyRw%2BkQ%40mail.gmail.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/CALY%3DcQCLxU%3DFTddW%3DesWLWHz0G-SO_oN7Zb4OE4o%3DVdwyRw%2BkQ%40mail.gmail.com?utm_medium=email&utm_source=footer) > > > . > > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). > > > > -- > > Adrien Grand > > > > -- > > You received this message because you are subscribed to the Google Groups > > "elasticsearch" group. > > To unsubscribe from this group and stop receiving emails from it, send an > > email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > > To view this discussion on the web visit > > [https://groups.google.com/d/msgid/elasticsearch/CAL6Z4j6tMy7AYLv4XBRW\_kDii1N2kvPibtawUMWQVbZ5s7bkBg%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAL6Z4j6tMy7AYLv4XBRW_kDii1N2kvPibtawUMWQVbZ5s7bkBg%40mail.gmail.com) > > [https://groups.google.com/d/msgid/elasticsearch/CAL6Z4j6tMy7AYLv4XBRW\_kDii1N2kvPibtawUMWQVbZ5s7bkBg%40mail.gmail.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/CAL6Z4j6tMy7AYLv4XBRW_kDii1N2kvPibtawUMWQVbZ5s7bkBg%40mail.gmail.com?utm_medium=email&utm_source=footer) > > . > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). > > -- > You received this message because you are subscribed to the Google Groups > "elasticsearch" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > To view this discussion on the web visit > [https://groups.google.com/d/msgid/elasticsearch/CALY%3DcQAW2OeHOruNhgqc-72zbKUCUqsyE3Ff1zOfCpHTMmu0%3Dw%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CALY%3DcQAW2OeHOruNhgqc-72zbKUCUqsyE3Ff1zOfCpHTMmu0%3Dw%40mail.gmail.com) > [https://groups.google.com/d/msgid/elasticsearch/CALY%3DcQAW2OeHOruNhgqc-72zbKUCUqsyE3Ff1zOfCpHTMmu0%3Dw%40mail.gmail.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/CALY%3DcQAW2OeHOruNhgqc-72zbKUCUqsyE3Ff1zOfCpHTMmu0%3Dw%40mail.gmail.com?utm_medium=email&utm_source=footer) > . > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAKdsXoF6TcnL-65g1dCaoaTyALpb%2BX7rgaV\_GWCGX-5bFecLDw%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAKdsXoF6TcnL-65g1dCaoaTyALpb%2BX7rgaV_GWCGX-5bFecLDw%40mail.gmail.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**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 1, 2016, 8:31am UTC](https://discuss.elastic.co/t/scripting-aggregation-order/18959/5 "2016-02-01T08:31:29Z")
Hi, is there any update on support this requirement? We also need to deal with this kind of sorting based on arithmetical expression result of multiple metrics. Thanks! ---
**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:19pm UTC](https://discuss.elastic.co/t/scripting-aggregation-order/18959/6 "2017-07-05T23:19:58Z")