Aniket Aniket - 1 month ago 18
Java Question

Aggregation in Elasticsearch

I want to apply group by clause on date field for elasticsearch query. This is my code.

SearchRequestBuilder srb = client
.prepareSearch(ConstantsValue.indexName)
.setTypes(ConstantsValue._Type)
.addAggregation(

AggregationBuilders
.dateHistogram("aggs")
.field("DTCREATED")
.interval(Interval.MONTH)
.format("yyyy-MM-dd")
.preZone("+05:30")
.preZoneAdjustLargeInterval(true)
.minDocCount(1)
)
.setSize(Integer.MAX_VALUE)
.setQuery(query);

SearchResponse response = srb
.setSearchType(SearchType.QUERY_AND_FETCH)
.setFetchSource(ConstantsValue.fieldList, null)
.execute()
.actionGet();


But query does not return expected result.

Result displayed is as follows

Value :{"DTCREATED":"2016-09-29T18:30:00.000Z"}
Key :AVfdaeSC3n3Bn-RaoFgg
Value :{"DTCREATED":"2016-09-29T18:30:00.000Z"}
Key :AVfdaeSC3n3Bn-RaoFgl
Value :{"DTCREATED":"2016-09-29T18:30:00.000Z"}
Key :AVfdaeSC3n3Bn-RaoFgq
Value :{"DTCREATED":"2016-08-31T18:30:00.000Z"}
Key :AVfdaeSC3n3Bn-RaoFgv
Value :{"DTCREATED":"2016-09-06T18:30:00.000Z"}
Key :AVfdaeSC3n3Bn-RaoFg0
Value :{"DTCREATED":"2016-09-22T18:30:00.000Z"}
Key :AVfdaeSC3n3Bn-RaoFg5
Value :{"DTCREATED":"2016-09-22T18:30:00.000Z"}
Key :AVfdaeSC3n3Bn-RaoFhA
Value :{"DTCREATED":"2016-09-12T18:30:00.000Z"}
Key :AVfdaeSC3n3Bn-RaoFhF


I am new in elasticsearch and don't know what I am missing.
Any help is greatly appreciated!

Answer

There's no groupby like clause in ES but then you could use the Aggregations in order to group by the field you want. For example I'm using the post http request below in order to group using userid and get the count for each userid.

The search query would look like this:

http://localhost:9200/response_summary/_search

In the above, response_summary is the index. i'm trying do the search.

The body of the request can be something like this:

{  
   "query":{  
      "query_string":{  
         "query":"api:\"smsmessaging\" AND operatorid:\"ROBI\""
      }
   },
   "aggs":{  
      "total":{  
         "terms":{  
            "field":"userid"
         },
         "aggs":{  
            "grades_count":{  
               "value_count":{  
                  "script":"doc['userid'].value"
               }
            }
         }
      }
   }
}

So you could mention the field you wanted to groupby within the aggs tag and get the count as a sample in the above. You could modify as you wish. Could have a look at this thread as well.