Aggregation in Elasticsearch

Question

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!

I have query for SQL ---- select distinct(SSTATUS),count(SSTATUS), TO_DATE (dtcreated, 'DD.MON.YYYY') from tableName WHERE SSTATUS LIKE 'CANCELLED' group by TO_DATE (dtcreated, 'DD.MON.YYYY') , sstatus order by SSTATUS — ASP
– ASP, Commented Oct 26, 2016 at 6:22
How're you querying it in java ? You're using the SQL query directly in your code? — Kulasangar
– Kulasangar, Commented Oct 26, 2016 at 6:24
no using SearchRequest class and passing Kibana query to its source field — ASP
– ASP, Commented Oct 26, 2016 at 6:26

Community · Accepted Answer · 2017-05-23 12:33:22Z

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.

Collectives™ on Stack Overflow

Aggregation in Elasticsearch

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related