Date_histogram returns empty

Elastic Stack Elasticsearch
1

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/",
"doc_count": 25865
},
{
"key": "https://builds2.finra.org/",
"doc_count": 9709
},
{
"key": "http://builds.test.finra.org:8080/",
"doc_count": 9488
},
{
"key": "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/",
"doc_count": 245659,
"last_six_months": {
"doc_count": 0,
"builds_over_time": {
"buckets": []
}
}
},

2

You need a reverse nested aggregation 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

3

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
                           },