Elasticsearch: Aggregate documents based on date range

Question

I have a set of documents in ElasticSearch 5.5 with two date fields: start_date and end_date. I want to aggregate them into date histogram buckets (ex: weekly) such that if the start_date < week X < end_date, then document would be in "week X" bucket. This means that a single document might be in multiple buckets.

Consider the following concrete example: I have a set of documents describing company employees, and for each employee you have hire date and (optionally) termination date. I want to build date histogram of number of active employees for trailing twelve months.

Sample doc content:

{
   "start_date": "2013-01-12T00:00:00.000Z",
   "end_date": "2016-12-08T00:00:00.000Z",
   "id": "123123123"
}

Is there a way to do this in ES?

always support your questions with proper json to show example to usecases. thx — user3775217
– user3775217, Commented Aug 2, 2017 at 9:43
Added one, although it doesn't feel like it's adding a lot of value — Tofig Hasanov
– Tofig Hasanov, Commented Aug 2, 2017 at 9:47
its not about value, its about when i read the post i should be able to visualize your problem better and obviously pushing a formatted json does't hurt much. — user3775217
– user3775217, Commented Aug 2, 2017 at 13:08
I doubt that one document can appear in multiple buckets of one aggregation. I can suggest checking out scripting for date aggregation, using pipeline aggregations and to denormalize data, e.g. precompute all the weeks between start_date and end_date and store them as a list in a field active_weeks. — Nikolay Vasiliev
– Nikolay Vasiliev, Commented Aug 3, 2017 at 10:19
@NikolayVasiliev I have found one way to do that (check my answer), but I am still not happy about this. Perhaps scripting is a better way forward — Tofig Hasanov
– Tofig Hasanov, Commented Aug 3, 2017 at 10:30

Tofig Hasanov · Accepted Answer · 2017-08-03 10:29:25Z

3

I have found one way to do this, using filter aggregations ( https://www.elastic.co/guide/en/elasticsearch/reference/master/search-aggregations-bucket-filter-aggregation.html). If I need, say, 12 trailing months report, then I would create 12 buckets, where each bucket defines filter conditions, such as:

"bool":{
 "must":[{
  "range":{
   "start_date":{
    "lte":"2016-01-01T00:00:00.000Z"
   }
  }
 },{
 {
  "range":{
   "end_date":{
    "gt":"2016-02-01T00:00:00.000Z"
   }
  }
 }]
}

However, I feel that it would be nice if there was an easier way to do this, since if I want say trailing 365 days, that means I have to create 365 bucket filters, which makes resultant query very large.

answered Aug 3, 2017 at 10:29

Tofig Hasanov

3,77911 gold badges59 silver badges93 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Surbhi Harsh · Accepted Answer · 2019-06-11 11:08:13Z

-1

I know this question is quite old but as it's still open I am sharing my knowledge on this. Also this question does not clearly explains that what kind of output is expected but still I think this can be achieved using the "Date Histogram Aggregation" and "Bucket Script Aggregation".

Here are the documentation links for both of these aggregations.

https://www.elastic.co/guide/en/elasticsearch/reference/5.5/search-aggregations-bucket-datehistogram-aggregation.html

https://www.elastic.co/guide/en/elasticsearch/reference/5.5/search-aggregations-pipeline-bucket-script-aggregation.html

answered Jun 11, 2019 at 11:08

Surbhi Harsh

699 bronze badges

2 Comments

mastisa Over a year ago

Please add summery of your links content to your answer

Surbhi Harsh Over a year ago

The links should be able to describe you everything. And if you want me to provide some sample query around it then I would need the expected result format.

Collectives™ on Stack Overflow

Elasticsearch: Aggregate documents based on date range

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related