# Nested aggregation referencing outer aggregation

**URL:** <https://discuss.elastic.co/t/nested-aggregation-referencing-outer-aggregation/283164>\
**Category:** Elasticsearch\
**Created:** [September 2, 2021, 12:19pm UTC](https://discuss.elastic.co/t/nested-aggregation-referencing-outer-aggregation/283164 "2021-09-02T12:19:59Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jakob](https://avatars.discourse-cdn.com/v4/letter/j/71c47a/32.png) [@Jakob](https://discuss.elastic.co/u/Jakob)\
**Post date:** [September 2, 2021, 12:19pm UTC](https://discuss.elastic.co/t/nested-aggregation-referencing-outer-aggregation/283164/1 "2021-09-02T12:19:59Z")

</div>

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:

```auto
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:

```auto
...
"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!

---

<div class="post-metadata">

**Author:** ![spinscale](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/spinscale/32/25011_2.png) [@spinscale](https://discuss.elastic.co/u/spinscale)\
**Post date:** [September 3, 2021, 3:22pm UTC](https://discuss.elastic.co/t/nested-aggregation-referencing-outer-aggregation/283164/2 "2021-09-03T15:22:25Z")

</div>

Try

```auto
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!

---

<div class="post-metadata">

**Author:** ![Jakob](https://avatars.discourse-cdn.com/v4/letter/j/71c47a/32.png) [@Jakob](https://discuss.elastic.co/u/Jakob)\
**Post date:** [September 6, 2021, 6:29am UTC](https://discuss.elastic.co/t/nested-aggregation-referencing-outer-aggregation/283164/3 "2021-09-06T06:29:14Z")

</div>

This worked beautifully! Thanks you

---

<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:** [October 4, 2021, 6:29am UTC](https://discuss.elastic.co/t/nested-aggregation-referencing-outer-aggregation/283164/4 "2021-10-04T06:29:31Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.