Sub aggregations

[jlarribe]

Hello, my question is about aggregation and sub aggregation.
I would like to use ES as a replacement of my aggregation system (classical SGBRr).
With a classic SQL language, it is possible to do a "group by" on more than one field.
Example :
SELECT Continent, Country, Sum(Visits) Visits
FROM TABLE
GROUP BY Continent, Country
ORDER BY Visits DESC
LIMIT 5

Here i want the top 5 couples of "Continent, Country" ordered by descending visits.

With ES, i do a first aggregation of the Continent dimension, and then a sub aggregation on the Country dimension. But i have no guarantee to get the same result than with a group by on two columns.
Would it be possible with the future SQL language directly accessible on ES ?

Thanks,

Julien

[polyfractal (Zachary Tong)]

Regarding SQL with Elasticsearch, this may interest you: https://www.elastic.co/elasticon/conf/2017/sf/elasticsearch-sql

@jlarribe

With ES, i do a first aggregation of the Continent dimension, and then a sub aggregation on the Country dimension. But i have no guarantee to get the same result than with a group by on two columns.

I'm not sure I understand this, however. Nesting aggregations in this manner will give you the same data as a SQL group on two fields, albeit in a slightly different format (nested trees rather than rows).

[basharz (bashar)]

try this hope it will help you
{
"size": 0,

"aggs": {
"CityAgg": {
"terms": {
"field": "city.raw",
"size": 5,
"order": {
"SumPriceCity": "desc"
}
},
"aggs": {
"SumPriceCity": {
"sum": {
"field": "price"
}
},
"AreaAgg": {
"terms": {
"field": "area",
"size": 5,
"order": {
"sumpricearea": "desc"
}
},
"aggs": {
"sumpricearea": {
"sum": {
"field": "price"
}
}
}
}
}
}
}
}