# How to do Metric aggregation inside terms aggregations on nested type

**URL:** <https://discuss.elastic.co/t/how-to-do-metric-aggregation-inside-terms-aggregations-on-nested-type/332217>\
**Category:** Elasticsearch\
**Tags:** painless\
**Created:** [May 2, 2023, 8:14am UTC](https://discuss.elastic.co/t/how-to-do-metric-aggregation-inside-terms-aggregations-on-nested-type/332217 "2023-05-02T08:14:27Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![kartikchauhan](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kartikchauhan/32/119490_2.png) [@kartikchauhan](https://discuss.elastic.co/u/kartikchauhan)\
**Post date:** [May 2, 2023, 8:14am UTC](https://discuss.elastic.co/t/how-to-do-metric-aggregation-inside-terms-aggregations-on-nested-type/332217/1 "2023-05-02T08:14:27Z")

</div>

I've documents stored in Elastic Search in this way:

**Doc1:**

```auto
{
  "owner": "owner_1",
  "attributes" : [
    {
      "name": "dump",
      "value": "xyz"
    },
    {
      "name": "weight",
      "value": "150"
    }
  ]
}

```

**Doc2:**

```auto
{
  "owner": "onwer_2",
  "attributes" : [
    {
      "name": "dump",
      "value": "xyz"
    },
    {
      "name": "weight",
      "value": "600"
    }
  ]
}

```

**Doc3:**

```auto
{
  "owner": "owner_3",
  "attributes" : [
    {
      "name": "dump",
      "value": "abc"
    },
    {
      "name": "weight",
      "value": "40"
    }
  ]
}

```

**Note:** _`attributes` is a nested type field._

* * *

**Problem statement:**

1. Retrieve documents that have the `name` _dump_ in their `attributes` field.
2. Perform an aggregation based on the `value` of _dump_ **(abc, xyz)**.
3. Calculate the total _weight_ for all documents that have the same _dump_ value obtained from the previous step.

The result for the documents mentioned above resembles the following:

```auto
{
  ...
  "aggregations": {
    ...
    "buckets": [
      {
        "key": "xyz",
        "doc_count": 2,
        "weight": {
          "value": 800
        }
      },
      {
        "key": "abc",
        "doc_count": 1,
        "weight": {
          "value": 40
        }
      }
    ]    
  }
}

```

* * *

**What steps I've taken so far?**

I've prepared the following query.

```auto
{
  "size": 0,
  "query": {
    "nested": {
      "path": "doc.attributes",
      "query": {
        "bool": {
          "must": [
            {
              "term": {
                "doc.attributes.name.keyword": "dump"
              }
            }
          ]
        }
      }
    }
  },
  "aggs": {
    "attributes": {
      "nested": {
        "path": "doc.attributes"
      },
      "aggs": {
        "NAME_BUCKET": {
          "filter": {
            "terms": {
              "doc.attributes.name.keyword": [
                "dump",
                "weight"
              ]
            }
          },
          "aggs": {
            "VALUE_BUCKET": {
              "terms": {
                "field": "doc.attributes.value.keyword",
                "size": 100
              },
              "aggs": {
                "weight": {
                  "sum": {
                    "script": {
                      "lang": "painless",
                      "source": """
                      if(doc['doc.attributes.name.keyword'].value == "weight") {
                        return Double.parseDouble(doc['doc.attributes.value.keyword'].value);
                      }
                      return 0;
                      """
                    }
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

```

The above query produces output similar to this:

```auto
{
  ...,
  ...,
  "buckets": [
    {
      "key": "abc",
      "doc_count": 1,
      "Payment": {
        "value": 0.0
      }
    },
    {
      "key": "xyz",
      "doc_count": 2,
      "Payment": {
        "value": 0.0
      }
    },
    {
      "key": "150",
      "doc_count": 1,
      "Payment": {
        "value": 150.0
      }
    },
    {
      "key": "40",
      "doc_count": 1,
      "Payment": {
        "value": 40.0
      }
    },
    {
      "key": "650",
      "doc_count": 1,
      "Payment": {
        "value": 650.0
      }
    }
  ]
}

```

Of course, the above query doesn't yield the expected output. The _NAME\_BUCKET_ aggregation aggregates records based on the _dump_ and _weight_ fields so that they can be used later in the Painless script. However, I am currently unable to determine how to aggregate _weight_ based on the value of _dump_."

Your help would be highly appreciated.

---

<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:** [May 30, 2023, 8:14am UTC](https://discuss.elastic.co/t/how-to-do-metric-aggregation-inside-terms-aggregations-on-nested-type/332217/2 "2023-05-30T08:14:37Z")

</div>

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