Home > OS >  How to convert the elasticsearch query to aggregationbuilder
How to convert the elasticsearch query to aggregationbuilder

Time:06-28

I am trying to write a query using elasticsearch where response should be sum of a column with condition of other fields. I'm able to do it using elasticsearch query but unable to do is using elasticsearch aggregationbuilder in elasticsearch api. Anyone please help me to write the code in elasticsearch api same like below attached query?

{
   "query": {
    "bool": {
      "must": [
        {
          "match": {
            "customerId": "13b7e2e8-8bf0-435b-b7f7-949c2e69b1d7"
          }
        }
      ],
      "must_not": [
        {
          "match": {
            "state": "Cancelled"
          }
        }
      ],
      "should": []
    }
  },
  "aggs": {
    "totalStakes": {
      "sum": {
        "field": "totalStake"
      }
    }
  }
}

I have tried the below code but it's not working as expected.

AggregationBuilder sumOfTotalStake = AggregationBuilders.sum("totalStake").field("totalStake");
        BoolQueryBuilder bqb = boolQuery().mustNot(termQuery("state", "Cancelled").must(termQuery("customerId", "13b7e2e8-8bf0-435b-b7f7-949c2e69b1d7")));
        // create the filter aggregation and add the sub-aggregation
        FilterAggregationBuilder aggregation = AggregationBuilders.filter("totalStakes", bqb)
                .subAggregation(sumOfTotalStake);

Expected result sould be below format :

{
  "took": 3,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": 10,
    "max_score": 4.3412046,
    "hits": []
  },
  "aggregations": {
    "totalStakes": {
      "value": 65579
    }
  }
}

CodePudding user response:

You can use below code in java. This code is in ES 7.X version but same or with minor change it will work.

SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();

BoolQueryBuilder qb = QueryBuilders.boolQuery()
                .must(QueryBuilders.matchQuery("customerId", "13b7e2e8-8bf0-435b-b7f7-949c2e69b1d7"))
                .mustNot(QueryBuilders.matchQuery("state", "Cancelled"));

searchSourceBuilder.query(qb);

AggregationBuilder ab = AggregationBuilders.sum("totalStakes").field("totalStake");
searchSourceBuilder.aggregation(ab);

searchRequest.source(searchSourceBuilder);
SearchResponse searchResponse = client.search(searchRequest, RequestOptions.DEFAULT);

Updated:

you can set size param to 0 for getting your expected result. You can keep above code as it and it will return only aggregation output.

searchSourceBuilder.size(0);
  • Related