# How to make Average sub aggregation to consider parent aggregation count instead of current aggregation count?

**URL:** <https://discuss.elastic.co/t/how-to-make-average-sub-aggregation-to-consider-parent-aggregation-count-instead-of-current-aggregation-count/298403>\
**Category:** Elasticsearch\
**Created:** [February 28, 2022, 12:51pm UTC](https://discuss.elastic.co/t/how-to-make-average-sub-aggregation-to-consider-parent-aggregation-count-instead-of-current-aggregation-count/298403 "2022-02-28T12:51:40Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Nishikant\_Tayade](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nishikant_tayade/32/84159_2.png) [@Nishikant\_Tayade](https://discuss.elastic.co/u/Nishikant_Tayade)\
**Post date:** [February 28, 2022, 12:51pm UTC](https://discuss.elastic.co/t/how-to-make-average-sub-aggregation-to-consider-parent-aggregation-count-instead-of-current-aggregation-count/298403/1 "2022-02-28T12:51:40Z")

</div>

I am using ES 7.10.2

Mappings for fields are as follows:

```auto
{
  "properties": {
    "ItemInfo": {
      "type": "nested",
      "include_in_parent": true,
      "properties": {
        "ShipInfo": {
          "type": "nested",
          "include_in_parent": true,
          "properties": {
            "City": {
              "type": "keyword",
              "store": true
            }
          }
        },
        "OriginalAmount": {
          "type": "double",
          "store": true
        }
      }
    }
  }
}

```

Then I indexed the document where some document have "OriginalAmount" while others not.

Now when I query ES for aggregation, where I want to calculate Avg of OriginalAmount for each city, as below:-

```auto
POST myindex/_search
  {
  "from": 0,
  "size": 0,
  "query": {
    "match_all": {
      "boost": 1
    }
  },
  "aggregations": {
    "City": {
      "terms": {
        "field": "ItemInfo.ShipInfo.City",
        "size": 10
      },
      "aggregations": {
        "OriginalAmount": {
          "avg": {
            "field": "OriginalAmount"
          }
        }
      }
    }
  }
}

```

The result I get is :-

```auto
"buckets" : [
        {
          "key" : "Littleton",
          "doc_count" : 5,
          "OriginalAmount" : {
            "value" : 76.98666666666666
          }
        },
        {
          "key" : "Paris",
          "doc_count" : 1,
          "OriginalAmount" : {
            "value" : null
          }
        }
      ]

```

> As you can see for document with City "Paris" the avg I am getting is null, as that document is not indexed with OriginalAmount.

Is there any property on ES or any configuration within Aggregation, where it considers parent agg count instead of current aggregation count, so that instead of getting null I will get 0?

---

<div class="post-metadata">

**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\
**Post date:** [February 28, 2022, 1:03pm UTC](https://discuss.elastic.co/t/how-to-make-average-sub-aggregation-to-consider-parent-aggregation-count-instead-of-current-aggregation-count/298403/2 "2022-02-28T13:03:55Z")

</div>

Reverse nested aggregation?

---

<div class="post-metadata">

**Author:** ![Nishikant\_Tayade](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nishikant_tayade/32/84159_2.png) [@Nishikant\_Tayade](https://discuss.elastic.co/u/Nishikant_Tayade)\
**Post date:** [March 1, 2022, 4:54am UTC](https://discuss.elastic.co/t/how-to-make-average-sub-aggregation-to-consider-parent-aggregation-count-instead-of-current-aggregation-count/298403/3 "2022-03-01T04:54:19Z")

</div>

@Tomo_M Thanks for reply!! will check this out.

---

<div class="post-metadata">

**Author:** ![Nishikant\_Tayade](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nishikant_tayade/32/84159_2.png) [@Nishikant\_Tayade](https://discuss.elastic.co/u/Nishikant_Tayade)\
**Post date:** [March 2, 2022, 1:25pm UTC](https://discuss.elastic.co/t/how-to-make-average-sub-aggregation-to-consider-parent-aggregation-count-instead-of-current-aggregation-count/298403/4 "2022-03-02T13:25:30Z")

</div>

@Tomo_M I think if I provide a missing configuration to my avg aggregation, then it will consider the parent aggregation count indirectly, like in above example for if I provide "missing":"0", then for document with city "Paris" which doesn't have OriginalAmount, it will be treated as 0, and then Avg will be calculated as Avg=sum/count=0/0, which will result in 0, instead of null.

Is this the right approach?

---

<div class="post-metadata">

**Author:** ![Tomo\_M](https://avatars.discourse-cdn.com/v4/letter/t/848f3c/32.png) [@Tomo\_M](https://discuss.elastic.co/u/Tomo_M)\
**Post date:** [March 2, 2022, 2:33pm UTC](https://discuss.elastic.co/t/how-to-make-average-sub-aggregation-to-consider-parent-aggregation-count-instead-of-current-aggregation-count/298403/5 "2022-03-02T14:33:14Z")

</div>

> [@Nishikant\_Tayade](#):
>
> parent agg count

I suppose I misunderstood you were using nested aggregation and wanted to know how to aggregate out of the nested objects. **I wonder why you need nested objects in this case.**

You need `reverse nested aggregation` when `include_in_parent` is `false`:

```auto
PUT /test_reverse_nested_aggregation/
{
  "mappings": {
    "properties": {
      "ItemInfo": {
        "type": "nested",
        "include_in_parent": false,
        "properties": {
          "ShipInfo": {
            "type": "nested",
            "include_in_parent": false,
            "properties": {
              "City": {
                "type": "keyword",
                "store": true
              }
            }
          }
        }
      },
      "OriginalAmount": {
        "type": "double",
        "store": true
      }
    }
  }
}

POST /test_reverse_nested_aggregation/_doc
{
  "ItemInfo":[
    {
      "ShipInfo":[
        {"City": "Tokyo"}
      ]
    },
    {
      "ShipInfo":[
        {"City": "New York"},
        {"City": "London"}
      ]
    }
  ],
  "OriginalAmount":100
}

POST /test_reverse_nested_aggregation/_doc
{
  "ItemInfo":[
    {
      "ShipInfo":[
        {"City": "Paris"}
      ]
    }
  ]
}

POST test_reverse_nested_aggregation/_search
  {
  "from": 0,
  "size": 0,
  "query": {
    "match_all": {
      "boost": 1
    }
  },
  "aggregations": {
    "n":{
      "nested": {
        "path": "ItemInfo.ShipInfo"
      },
      "aggs":{
        "City":{
          "terms":{
            "field":"ItemInfo.ShipInfo.City"
          },
          "aggs":{
            "p":{
              "reverse_nested": {},
              "aggs":{
                "OriginalAmount":{
                  "avg":{
                    "field":"OriginalAmount",
                    "missing":0
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

```

> [@Nishikant\_Tayade](#):
>
> parent aggregation count

As you set `"include_in_parent": true` and used usual aggregations without nested aggregation, the average aggregation were just culculated on OriginalAmount Field for usual document level aggregation. No need to use "parent".

Anyway setting "missing":"0" is the right approach, I suppose. If you set "missing": "0", the sum will be 0 and the count should be doc\_count (not 0). Then Avg = 0/doc\_count = 0.

---

<div class="post-metadata">

**Author:** ![Nishikant\_Tayade](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nishikant_tayade/32/84159_2.png) [@Nishikant\_Tayade](https://discuss.elastic.co/u/Nishikant_Tayade)\
**Post date:** [March 3, 2022, 4:13am UTC](https://discuss.elastic.co/t/how-to-make-average-sub-aggregation-to-consider-parent-aggregation-count-instead-of-current-aggregation-count/298403/6 "2022-03-03T04:13:09Z")

</div>

@Tomo_M Thanks for all the help!!

---

<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:** [March 31, 2022, 4:13am UTC](https://discuss.elastic.co/t/how-to-make-average-sub-aggregation-to-consider-parent-aggregation-count-instead-of-current-aggregation-count/298403/7 "2022-03-31T04:13:29Z")

</div>

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