# Date\_histogram returns empty

**URL:** <https://discuss.elastic.co/t/date-histogram-returns-empty/52108>\
**Category:** Elasticsearch\
**Created:** [June 7, 2016, 4:02pm UTC](https://discuss.elastic.co/t/date-histogram-returns-empty/52108 "2016-06-07T16:02:53Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![mfcarbo](https://avatars.discourse-cdn.com/v4/letter/m/258eb7/32.png) [@mfcarbo](https://discuss.elastic.co/u/mfcarbo)\
**Post date:** [June 7, 2016, 4:02pm UTC](https://discuss.elastic.co/t/date-histogram-returns-empty/52108/1 "2016-06-07T16:02:53Z")

</div>

I have an aggregation which works but I would like the results to be organized differently so I can more easily map it to several data series for highcharts graphs.

This works:  
{  
"size": 0,  
"aggs": {  
"last\_six\_months": {  
"filter": {  
"bool": {  
"must": [  
{  
"range": {  
"startTime": {  
"gte": "now-6M/M",  
"lte": "now"  
}  
}

```
                  }
               ]
            }
         },
         "aggs": {
            "builds_over_time": {
               "date_histogram": {
                  "field": "startTime",
                  "interval": "month",
                  "format": "MMM YY"
               },
               "aggs": {
                  "env": {
                     "nested": {
                        "path": "environment"
                     },
                     "aggs": {
                        "group_by_master": {
                           "terms": {
                              "field": "environment.JENKINS_URL"
                           }

```

...

the result looks like this:

"aggregations": {  
"last\_six\_months": {  
"doc\_count": 337338,  
"builds\_over\_time": {  
"buckets": [  
{  
"key\_as\_string": "Dec 15",  
"key": 1448928000000,  
"doc\_count": 46504,  
"env": {  
"doc\_count": 46487,  
"group\_by\_master": {  
"doc\_count\_error\_upper\_bound": 0,  
"sum\_other\_doc\_count": 0,  
"buckets": [  
{  
"key": "[https://builds.finra.org/](https://builds.finra.org/)",  
"doc\_count": 25865  
},  
{  
"key": "[https://builds2.finra.org/](https://builds2.finra.org/)",  
"doc\_count": 9709  
},  
{  
"key": "[http://builds.test.finra.org:8080/](http://builds.test.finra.org:8080/)",  
"doc\_count": 9488  
},  
{  
"key": "[https://builds.aws.finra.org/](https://builds.aws.finra.org/)",  
"doc\_count": 1425  
}  
]  
}  
}  
},

What I would like is to group\_by\_master first since these buckets will be the series on the chart.

This is what I have tried:

```
GET jenkins/_search
{
   "size": 0,
   "aggs": {
      "env": {
         "nested": {
            "path": "environment"
         },
         "aggs": {
            "group_by_master": {
               "terms": {
                  "field": "environment.JENKINS_URL"
               },
               "aggs": {
                  "last_six_months": {
                     "filter": {
                        "bool": {
                           "must": [
                              {
                                 "range": {
                                    "startTime": {
                                       "gte": "now-6M/M",
                                       "lte": "now"
                                    }
                                 }
                              }
                           ]
                        }
                     },
                        "aggs": {
                           "builds_over_time": {
                              "date_histogram": {
                                 "field": "startTime",
                                 "interval": "month",
                                 "format": "MMM YY"
                              }
                           }
                        }
                     }
                  }
               }
            }
         }
      }
   }

```

And the result:  
"aggregations": {  
"env": {  
"doc\_count": 477188,  
"group\_by\_master": {  
"doc\_count\_error\_upper\_bound": 0,  
"sum\_other\_doc\_count": 0,  
"buckets": [  
{  
"key": "[https://builds.finra.org/](https://builds.finra.org/)",  
"doc\_count": 245659,  
"last\_six\_months": {  
"doc\_count": 0,  
"builds\_over\_time": {  
"buckets": []  
}  
}  
},

---

<div class="post-metadata">

**Author:** ![colings86](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/colings86/32/44960_2.png) [@colings86](https://discuss.elastic.co/u/colings86)\
**Post date:** [June 8, 2016, 8:06am UTC](https://discuss.elastic.co/t/date-histogram-returns-empty/52108/2 "2016-06-08T08:06:20Z")

</div>

You need a [`reverse nested` aggregation](https://www.elastic.co/guide/en/elasticsearch/reference/2.3/search-aggregations-bucket-reverse-nested-aggregation.html) wrapping your `builds_over_time` date histogram aggregation. Without that you are still in the nested document context rather than in the root context. That should solve your issue.

Also, you should move the filter in your filter aggregation to be in the search query instead of as a filter aggregation. This is much more efficient as it can make use to the inverted index

Hope that helps

---

<div class="post-metadata">

**Author:** ![mfcarbo](https://avatars.discourse-cdn.com/v4/letter/m/258eb7/32.png) [@mfcarbo](https://discuss.elastic.co/u/mfcarbo)\
**Post date:** [June 10, 2016, 2:43pm UTC](https://discuss.elastic.co/t/date-histogram-returns-empty/52108/3 "2016-06-10T14:43:01Z")

</div>

Thank you, Colin! It worked perfectly:

```
GET jenkins/_search
{
   "size": 0,
   "query": {
      "bool": {
         "must": [
            {
               "range": {
                  "startTime": {
                     "gte": "now-6M/M",
                     "lte": "now"
                  }
               }
            }
         ]
      }
   },
   "aggs": {
      "environment": {
         "nested": {
            "path": "environment"
         },
         "aggs": {
            "group_by_master": {
               "terms": {
                  "field": "environment.JENKINS_URL"
               },
               "aggs": {
                  "reverse": {
                     "reverse_nested": {},
                     "aggs": {
                        "builds_over_time": {
                           "date_histogram": {
                              "field": "startTime",
                              "interval": "month",
                              "format": "MMM YY"
                           }
                        }
                     }
                  }
               }
            }
         }
      }
   }
}

```

Result:

```
"aggregations": {
      "environment": {
         "doc_count": 337264,
         "group_by_master": {
            "doc_count_error_upper_bound": 0,
            "sum_other_doc_count": 0,
            "buckets": [
               {
                  "key": "https://builds.finra.org/",
                  "doc_count": 162478,
                  "reverse": {
                     "doc_count": 162478,
                     "builds_over_time": {
                        "buckets": [
                           {
                              "key_as_string": "Dec 15",
                              "key": 1448928000000,
                              "doc_count": 25865
                           },
                           {
                              "key_as_string": "Jan 16",
                              "key": 1451606400000,
                              "doc_count": 24037
                           },
```

---

<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:** [July 5, 2017, 10:44pm UTC](https://discuss.elastic.co/t/date-histogram-returns-empty/52108/4 "2017-07-05T22:44:44Z")

</div>