Search code examples
javaelasticsearchresthighlevelclient

How to get Average from AvgAggregationBuilder elastic search for spesific keys

I'm am trying to get results from Elastic search with spesic keys example:

average salary, by city, gender

so i check at metrics_aggregations

and I was able 

AvgAggregationBuilder totalDuration = AggregationBuilders.avg("salaryAvg").field("salary");

it did return the most high level - average salary without specific fields

So I tried using subAggregation

AvgAggregationBuilder totalDuration =     AggregationBuilders
            .avg("salaryAvg").field("salary")
            .subAggregation(AggregationBuilders.terms("city").field("gender"));

but i got below error

Server Error. 21525\nElasticsearchStatusException[Elasticsearch exception [type=aggregation_initialization_exception, reason=Aggregator [salaryAvg] of type [avg] cannot accept sub-aggregations]]\n\tat org.elasticsearch.rest.BytesRestResponse.errorFromXConten

how can I query it with elastic search what I want is something quite similar to SQL - group by

each row of my elastic response data seems like that:

widgetResponse.getHits().getHits()[0].getSourceAsMap().get("attributes")


{HashMap$Node@53898} "city" -> "new York"
{HashMap$Node@53899} "pageId" -> "123"
{HashMap$Node@53900} "salary" -> "45765"
{HashMap$Node@53900} "gender" -> "female"
{HashMap$Node@53901} "userId" -> "324"
{HashMap$Node@53902} "accountId" -> "456"


widgetResponse.getHits().getHits()[1].getSourceAsMap().get("attributes")


{HashMap$Node@53898} "city" -> "new Jersy"
{HashMap$Node@53899} "pageId" -> "123"
{HashMap$Node@53899} "salary" -> "77777"
{HashMap$Node@53900} "gender" -> "female"
{HashMap$Node@53901} "userId" -> "334"
{HashMap$Node@53902} "accountId" -> "999"

How can I support it using Elastic search JAVA API?

I tried also

running it:

        AvgAggregationBuilder totalDuration0 = AggregationBuilders.avg(TOTAL_DURATION_AVG).field(TOTAL_DURATION);
    TermsAggregationBuilder totalDuration = AggregationBuilders.terms(TOTAL_DURATION_AVG)
            .field(ATTRIBUTES_WIDGET_ID).subAggregation(totalDuration0);

I got this response

there's seems to have a different average and how much rows for each - row in this list but I have no idea which "key" is pointing to seems like a byte array

but it doesn't match to the actual key I have also [key] seems to be duplicated with a different average so what am I missing here. ?

enter image description here

Solution

  • I was able to get avg by a single field, but not more than one field

        final AvgAggregationBuilder totalDuration = AggregationBuilders.avg(avgSalary).field(SALARY_AVG);
    final TermsAggregationBuilder totalDurationByWidgetId = AggregationBuilders.terms(SALARY_AVG)
            .field(GENDER).subAggregation(avgSalary);