Elasticsearch - group by day of the week and hour

Question

I need to make some data grouped by day of the week and hour, for example

curl -XGET http://localhost:9200/testing/hello/_search?pretty=true -d ' { "size": 0, "aggs": { "articles_over_time" : { "date_histogram" : { "field" : "date", "interval" : "hour", "format": "E - k" } } } } ' 

Gives me this:

 { "took" : 2, "timed_out" : false, "_shards" : { "total" : 5, "successful" : 5, "failed" : 0 }, "hits" : { "total" : 2857, "max_score" : 0.0, "hits" : [ ] }, "aggregations" : { "articles_over_time" : { "buckets" : [ { "key_as_string" : "Fri - 17", "key" : 1391792400000, "doc_count" : 6 }, ... { "key_as_string" : "Wed - 22", "key" : 1411596000000, "doc_count" : 1 }, { "key_as_string" : "Wed - 22", "key" : 1411632000000, "doc_count" : 1 } ] } } } 

Now I need to summarize the document counters by this value "Wed - 22", how can I do this? Maybe some other approach?

Answer 1

The same problem has been resolved in this thread .

Applying the solution to your problem, we need to make a script to convert the date to the hour of the day and day of the week:

 Date date = new Date(doc['date'].value) ; java.text.SimpleDateFormat format = new java.text.SimpleDateFormat('EEE, HH'); format.format(date) 

And use it in the query:

 { "aggs": { "perWeekDay": { "terms": { "script": "Date date = new Date(doc['date'].value) ;java.text.SimpleDateFormat format = new java.text.SimpleDateFormat('EEE, HH');format.format(date)" } } } } 

Answer 2

You can try to aggregate terms in the "key_as_string" field from aggregation results using sub-aggregation.

Hope this helps.

Answer 3

This is because you use the hour interval, but the date format is day (E - k).

Change your interval to "day" and you will no longer receive separate buckets for "Weds-22".

Or, if you want an hour, then change your format to include the hour field.