# Calculation across the root document and the nested objects

**URL:** <https://discuss.elastic.co/t/calculation-across-the-root-document-and-the-nested-objects/59431>\
**Category:** Elasticsearch\
**Created:** [August 31, 2016, 2:08pm UTC](https://discuss.elastic.co/t/calculation-across-the-root-document-and-the-nested-objects/59431 "2016-08-31T14:08:30Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![Aludirk](https://avatars.discourse-cdn.com/v4/letter/a/ad7895/32.png) [@Aludirk](https://discuss.elastic.co/u/Aludirk)\
**Post date:** [August 31, 2016, 2:08pm UTC](https://discuss.elastic.co/t/calculation-across-the-root-document-and-the-nested-objects/59431/1 "2016-08-31T14:08:30Z")

</div>

I have a data model:

```auto
PUT /test
{
  "mappings": {
    "trade": {
      "properties": {
        "trade_from": {
          "type": "string",
          "index": "not_analyzed"
        },
        "trade_to": {
          "type": "string",
          "index": "not_analyzed"
        },
        "trade_gold": {
          "type": "integer"
        },
        "trade": {
          "type": "nested",
          "properties": {
            "item_name": {
              "type": "string",
              "index": "not_analyzed"
            },
            "item_count": {
              "type": "integer"
            }
          }
        }
      }
    }
  }
}

```

and data:

```auto
PUT /test/trade/1
{
  "trade_from": "PlayerA",
  "trade_to": "PlayerB",
  "trade_gold": 10000,
  "trade": [
    {
      "item_name": "PortionA",
      "item_count": 3
    },
    {
      "item_name": "PortionB",
      "item_count": 5
    },
    {
      "item_name": "PortionC",
      "item_count": 10
    },
  ]
}

```

and I want to get the average amount of gold per each item in the trading log, like:  
PortionA | 3333.3333  
PortionB | 2000  
PortionC | 1000

I tried:

```auto
GET /test/_search
{
  "size": 0,
  "aggs": {
    "nested_2": {
      "nested": {
        "path": "trade"
      },
      "aggs": {
        "2": {
          "terms": {
            "field": "trade.item_name",
            "size": 10000,
            "order": {
              "_term": "desc"
            }
          },
          "aggs": {
            "1": {
              "avg": {
                "field": "trade.item_count",
                "script": "return doc['trade_gold'].value/doc['trade.item_count'].value;"
              }
            }
          }
        }
      }
    }
  }
}

```

I found the `doc['trade_gold']` is null

and tried:

```auto
GET /test/_search
{
  "size": 0,
  "aggs": {
    "nested_2": {
      "nested": {
        "path": "trade"
      },
      "aggs": {
        "2": {
          "terms": {
            "field": "trade.item_name",
            "size": 10000,
            "order": {
              "_term": "desc"
            }
          },
          "aggs": {
            "nested_1": {
              "reverse_nested": {},
              "aggs": {
                "1": {
                  "avg": {
                    "field": "trade_gold",
                    "script": "return doc['trade_gold'].value/doc['trade.item_count'].value;"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

```

I found `doc['trade.item_count']` is null

How can I do the calculation across the root document and the nested objects ?

If there does not have solution, how the data model changed to achieve this calculation ?  
As this is only the simplified version of my data model, I must keep the array of nested objects in one document, and cannot separate to three documents.

---

<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:23pm UTC](https://discuss.elastic.co/t/calculation-across-the-root-document-and-the-nested-objects/59431/2 "2017-07-05T22:23:33Z")

</div>