CODEWITHSUNDEEP
javaelasticsearch
Igor
Igor

Reputation: 866

Elasticsearch aggregate and sum by field (group by)

I have the next data in my index:

{
  "category": "fruit",
  "name": "apple",
  "price": 2.6,
},
{
  "category": "fruit",
  "name": "orange",
  "price": 1.8,
},
{
  "category": "vegs",
  "name": "tomato",
  "price": 0.95,
}

I would like to sum the prices by category that will lead to a result like:

fruit - 4.4
vegs - 0.95

I do realize that I need to use nested aggregation, but I fail to see how exactly. Here is the code I got so far:

    SearchRequest searchRequest = new SearchRequest("products");
    SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();

    searchSourceBuilder.
            aggregation(AggregationBuilders.
                    nested("category_price", "products").
                    subAggregation(
                            AggregationBuilders
                                    .terms("field").field("category")).
                    subAggregation(
                            AggregationBuilders.avg("avg_price").field("price"))
            );

    searchRequest.source(searchSourceBuilder);

    SearchResponse response = client.search(searchRequest);
    Nested agg = response.getAggregations().get("category_price");

    Terms name = agg.getAggregations().get("field");
    for (Terms.Bucket bucket : name.getBuckets()) {
        ReverseNested resellerToProduct = bucket.getAggregations().get("avg_price");
        System.out.println(resellerToProduct.getDocCount());
        System.out.println(resellerToProduct.getName());
    }

Upvotes: 0

Views: 1719

Answers (1)

Grigory Rubstein
Grigory Rubstein

Reputation: 26

you created the second aggregation as sibling and you need just sub-aggregation. + you don't have to use here nested aggregation .

AggregationBuilder aggregationBuilder = AggregationBuilders.global("agg")
    .subAggregation(AggregationBuilders.terms("by_category").field("category")
    .subAggregation(AggregationBuilders.sum("sum_price").field("price")));

Upvotes: 1

Related Questions