Nested aggregation referencing outer aggregation

[Jakob (Jakob Erlandsson)]

I am learning about aggregations by looking at the "kibana_sample_data_flights" index and I'm trying to find how many flights have the same weather on departure and arrival.
Here is what I have so far:

GET /kibana_sample_data_flights/_search
{
  "size": 0,
  "aggs": {
    "Weather_Conditions_Departure": {
      "terms": {
        "field": "OriginWeather"
      },
      "aggs": {
        "Weather_Conditions_Arrival": {
          "terms": {
            "field": "DestWeather"
          }
        }
      }
    }
  }
}

This is a simple aggregation getting the arrival conditions for each type of departure condition:

...
"aggregations" : {
    "Weather_Conditions_Departure" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "Clear",
          "doc_count" : 2324,
          "Weather_Conditions_Arrival" : {
            "doc_count_error_upper_bound" : 0,
            "sum_other_doc_count" : 0,
            "buckets" : [
              {
                "key" : "Cloudy",
                "doc_count" : 434
              },
              {
                "key" : "Rain",
                "doc_count" : 426
              },
              {
                "key" : "Clear",
                "doc_count" : 413
              },
              {
                "key" : "Sunny",
                "doc_count" : 389
              },
              {
                "key" : "Hail",
                "doc_count" : 176
              },
              {
                "key" : "Heavy Fog",
                "doc_count" : 166
              },
              {
                "key" : "Damaging Wind",
                "doc_count" : 161
              },
              {
                "key" : "Thunder & Lightning",
                "doc_count" : 159
              }
            ]
          }
        },
...

So from this response we can see that 413 flights have clear skies on both departure and arrival, but how can I filter out the keys in the inner aggregation that does not correspond to the key in the outer one?

Thanks!

[spinscale (Alexander Reelsen)]

Try

GET /kibana_sample_data_flights/_search
{
  "size": 0,
  "query": {
    "bool": {
      "filter": [
        {
          "script": {
            "script": {
              "lang": "painless",
              "source": "doc['OriginWeather'].value == doc['DestWeather'].value"
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "Weather_Conditions_Departure": {
      "terms": {
        "field": "OriginWeather"
      }
    }
  }
}

This adds a filter to only return documents with the same source/destination weather, so you only need a single aggregation. This happens at query time, so this could be speed up by creating another field on index time to store that information, but for testing this might be good enough!

[Jakob (Jakob Erlandsson)]

This worked beautifully! Thanks you