How to sort and limit aggregations in ElasticSearch


(Jack Wang) #1

For example I have the following records with the columns as:(Country,City,Date,Income)

USA SF 2015-01 80
USA SF 2015-03 60
USA SF 2015-05 20
USA NY 2015-09 70
USA NY 2015-02 30
U.K LD 2015-05 30
U.K LD 2015-01 20

My sql as: select country,city,max(date) as maxDate,sum(income) as sumIncome from testTable group by country,city order by maxDate desc,sumIncome desc limit 2.
So the result should be:

USA NY 2015-09 100
USA SF 2015-05 160

I wrote the ES aggregates as following, but it's wrong:

"aggs":{"sub1": {"terms":{"field":"contry"},
   "aggs":{"sub2":{"terms":{"field":"city",
       "order":[{"submax":"DESC"},{"subsum":"DESC"}]},
     "aggs":{"submax":{"max":{"field":"date"}},"subsum":{"sum":{"field":"income"}}}}}}}

First Problem: it's not sorted globally, it seems that just sort in each bucket,
Another problem is that I don't know how to limit the size to 2. Thanks in Advance!


(system) #2