Sorting after aggregation in Elasticsearch
45,102
Solution 1
You almost had it. You just need to add an order
property to your a1
terms aggregations, like this:
GET myindex/_search
{
"size":0,
"aggs": {
"a1": {
"terms": {
"field": "FIELD1",
"size":0,
"order": {"a2": "desc"} <--- add this
},
"aggs":{
"a2":{
"sum":{
"field":"FIELD2.SUBFIELD"
}
}
}
}
}
}
Solution 2
Brilliant from Val https://stackoverflow.com/users/4604579/val
Basically the same thing, but here's what worked for me to find the largest "size" for each "name", and to show the top 25 largest:
{
"size": 0,
"aggs": {
"agg1": {
"terms": {
"field": "name.keyword",
"order": {
"agg2": "desc"
},
"size": 25
},
"aggs": {
"agg2": {
"max": {
"field": "size"
}
}
}
}
}
}
Related videos on Youtube
Author by
maxv15
Updated on July 14, 2022Comments
-
maxv15 almost 2 years
I have docs with this structure:
{ FIELD1:string, FIELD2: [ {SUBFIELD:number}, {SUBFIELD:number}...] }
I want to sort on the result of the sum of numbers in FIELD2.SUBFIELDs:
GET myindex/_search { "size":0, "aggs": { "a1": { "terms": { "field": "FIELD1", "size":0 }, "aggs":{ "a2":{ "sum":{ "field":"FIELD2.SUBFIELD" } } } } } }
If I do this I obtain buckets not sorted, but I want buckets sorted by "a2" value. How I can do this? Thank you!
-
maxv15 almost 9 yearsThank you...I didn't know where place "order"
-
Erdal G. over 7 yearsThanks! Saved my day! Couldn't find this on the doc, and that was so obvious :)
-
Val over 7 years@ErdalG. the documentation or ordering terms aggregations is here
-
Saeed Zhiany over 7 years@Val this is not working when
a2
is ascripted_metric
aggregation. I used it for my cases that I have to usescripted_metric
aggregation. I got this error"reason": { "type": "aggregation_execution_exception", "reason": "Invalid terms aggregation order path [totalViews]. Terms 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." }
-
Val over 7 years@saeedzhiany Correct, there's an open PR and an open issue about this.
-
Saeed Zhiany over 7 years@Val, is there any alternate for this sort? I really need it
-
Val over 7 years@saeedzhiany not that I know of. Usually, people sort on the client side. Feel free to ask a new question, maybe someone has an idea.
-
cryptopath about 5 years@Val what if a2 isn't a sum agg but a terms agg?
-
Val about 5 years@cryptopath please ask another new question with your issue.
-
darthbhyrava over 3 yearsThanks for this! I was just about to restructure my entire schema because I got lost in documentation trying to find this one line change. :)