# Group by multiple fields and get count in elasticsearch

**URL:** <https://discuss.elastic.co/t/group-by-multiple-fields-and-get-count-in-elasticsearch/191575>\
**Category:** Elasticsearch\
**Created:** [July 22, 2019, 6:03am UTC](https://discuss.elastic.co/t/group-by-multiple-fields-and-get-count-in-elasticsearch/191575 "2019-07-22T06:03:42Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Gokul\_Raj](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/gokul_raj/32/48717_2.png) [@Gokul\_Raj](https://discuss.elastic.co/u/Gokul_Raj)\
**Post date:** [July 22, 2019, 6:03am UTC](https://discuss.elastic.co/t/group-by-multiple-fields-and-get-count-in-elasticsearch/191575/1 "2019-07-22T06:03:42Z")

</div>

Is there any option in elasticsearch to use aggregation for multiple fields and get total count ?.  
My query is

```auto
"SELECT COUNT(*), currency,type,status,channel FROM temp_index WHERE country='SG' and received_time=now/d group by currency,type,status,channel 

```

Trying to implement the above in Java code using RestHighLevelClient , any suggestions or assistance will be helpful.

Currently we are using COUNT API

```auto
	List<Object> dashboardsDataTotal = new ArrayList<>();
		String[] channelList = { "test1", "test2", "test3", "test4", "test5", "test6" };
		String[] currencyList = { "SGD", "HKD", "USD", "INR", "IDR", "PHP", "CNY" };
		String[] statusList = { "COMPLETED", "FAILED", "PENDING", "FUTUREPROCESSINGDATE" };
		String[] paymentTypeList = { "type1", "type2" };
		String[] countryList = { "SG", "HK"};
		
		CountRequest countRequest = new CountRequest(INDEX);
		SearchSourceBuilder searchSourceBuilder = new SearchSourceBuilder();
		try {
			for (String country : countryAccess) { // per country
				Map<String, Object> dashboardsDataPerCountry = new HashMap<>();
				for (String channel : channelList) { // per channel
					Map<String, Object> channelStore = new HashMap<>();
					for (String paymentType : paymentTypeList) {
						List<Object> paymentTypeStore = new ArrayList<>();
						for (String currency : currencyList) {
							Map<String, Object> currencyStore = new HashMap<>();
							int receivedCount = 0;
							for (String latestStatus : statusList) {
								BoolQueryBuilder searchBoolQuery = QueryBuilders.boolQuery();
								searchBoolQuery
										.must(QueryBuilders.termQuery("channel", channel.toLowerCase()));
								searchBoolQuery
										.must(QueryBuilders.termQuery("currency", currency.toLowerCase()));
								searchBoolQuery.must(QueryBuilders.matchPhraseQuery("source_country",
										country.toLowerCase()));
								if ("FUTUREPROCESSINGDATE".equalsIgnoreCase(latestStatus)) {
									searchBoolQuery.must(
											QueryBuilders.rangeQuery("processing_date").gt(currentDateS).timeZone(getTimeZone(country)));
								}
								else {
									searchBoolQuery.must(QueryBuilders.termQuery("txn_latest_status",
											latestStatus.toLowerCase()));
								}
								searchBoolQuery.must(
										QueryBuilders.termQuery("paymentType", paymentType.toLowerCase()));
								
								searchBoolQuery.must(QueryBuilders.rangeQuery("received_time").gte(currentDateS)
											.lte(currentDateS).timeZone(getTimeZone(country)));
								
								searchSourceBuilder.query(searchBoolQuery);
								countRequest.source(searchSourceBuilder);
								
								// try {
								CountResponse countResponse = restHighLevelClient.count(countRequest,
										RequestOptions.DEFAULT);
								if (!latestStatus.equals("FUTUREPROCESSINGDATE")) {
									receivedCount += countResponse.getCount();
								}
								currencyStore.put(latestStatus, countResponse.getCount());
							}
							currencyStore.put("RECEIVED", receivedCount); // received = pending + completed + failed
							currencyStore.put("currency", currency);
							paymentTypeStore.add(currencyStore);
						} // per currency end
						channelStore.put(paymentType, paymentTypeStore);
					} // paymentType end
					dashboardsDataPerCountry.put(channel, channelStore);
					dashboardsDataPerCountry.put("country", country);
				} // per channel end
				dashboardsDataTotal.add(dashboardsDataPerCountry);
			} // per country end
			restHighLevelClient.close();
		}

```

Appreciate if someone can provide a better solution to the above.

---

<div class="post-metadata">

**Author:** ![Gokul\_Raj](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/gokul_raj/32/48717_2.png) [@Gokul\_Raj](https://discuss.elastic.co/u/Gokul_Raj)\
**Post date:** [July 25, 2019, 2:48am UTC](https://discuss.elastic.co/t/group-by-multiple-fields-and-get-count-in-elasticsearch/191575/2 "2019-07-25T02:48:35Z")

</div>

Made use of CompositeAggregationBuilder and got the aggregated results

```auto
CompositeAggregationBuilder compositeAgg = new CompositeAggregationBuilder("aggregate_buckets", sources);
searchSourceBuilder.aggregation(compositeAgg);

```

```auto
SearchResponse searchResponse = restHighLevelClient.search(searchRequest, RequestOptions.DEFAULT);
Aggregations aggregations = searchResponse.getAggregations();
ParsedComposite parsedComposite = aggregations.get("aggregate_buckets");
List<ParsedBucket> list = parsedComposite.getBuckets();
Map<String,Object> data = new HashMap<>();
for (ParsedBucket parsedBucket : list) {
                data.clear();
		for (Map.Entry<String, Object> m : parsedBucket.getKey().entrySet()) {
			data.put(m.getKey(), m.getValue());
		}
		data.put("count", parsedBucket.getDocCount());
		System.out.println(data);
}

```

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [August 22, 2019, 2:48am UTC](https://discuss.elastic.co/t/group-by-multiple-fields-and-get-count-in-elasticsearch/191575/3 "2019-08-22T02:48:48Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.