# ElasticSearch aggregate on a scripted nested field

**URL:** <https://discuss.elastic.co/t/elasticsearch-aggregate-on-a-scripted-nested-field/298841>\
**Category:** Elasticsearch\
**Tags:** runtime-fields\
**Created:** [March 4, 2022, 11:23am UTC](https://discuss.elastic.co/t/elasticsearch-aggregate-on-a-scripted-nested-field/298841 "2022-03-04T11:23:15Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![doogyatnesta](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/doogyatnesta/32/98588_2.png) [@doogyatnesta](https://discuss.elastic.co/u/doogyatnesta)\
**Post date:** [March 4, 2022, 11:23am UTC](https://discuss.elastic.co/t/elasticsearch-aggregate-on-a-scripted-nested-field/298841/1 "2022-03-04T11:23:15Z")

</div>

I have the following mapping in my Elasticsearch index (simplified as the other fields are irrelevant:

```json
{
  "test": {
    "mappings": {
      "properties": {
        "name": {
          "type": "keyword"
        },
        "entities": {
          "type": "nested",
          "properties": {
            "text_property": {
              "type": "text"
            },
            "float_property": {
              "type": "float"
            }
          }
        }
      }
    }
  }
}

```

The data looks like this (again simplified):

```json
[
  {
    "name": "a",
    "entities": [
      {
        "text_property": "foo",
        "float_property": 0.2
      },
      {
        "text_property": "bar",
        "float_property": 0.4
      },
      {
        "text_property": "baz",
        "float_property": 0.6
      }
    ]
  },
  {
    "name": "b",
    "entities": [
      {
        "text_property": "foo",
        "float_property": 0.9
      }
    ]
  },
  {
    "name": "c",
    "entities": [
      {
        "text_property": "foo",
        "float_property": 0.2
      },
      {
        "text_property": "bar",
        "float_property": 0.9
      }
    ]
  }
]

```

I'm trying perform a bucket aggregation on the maximum value of `float_property` for each document. So for the example above, the following would be the desired response:

```json
...
{
  "buckets": [
    {
      "key": "0.9",
      "doc_count": 2
    },
    {
      "key": "0.6",
      "doc_count": 1
    }
  ]
}

```

as doc `a`'s highest nested value for `float_property` is 0.6, `b`'s is 0.9 and `c`'s is 0.9.

I've tried using a mixture of `nested` and `aggs`, along with `runtime_mappings`, but I'm not sure in which order to use these.

---

<div class="post-metadata">

**Author:** ![doogyatnesta](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/doogyatnesta/32/98588_2.png) [@doogyatnesta](https://discuss.elastic.co/u/doogyatnesta)\
**Post date:** [March 4, 2022, 5:16pm UTC](https://discuss.elastic.co/t/elasticsearch-aggregate-on-a-scripted-nested-field/298841/2 "2022-03-04T17:16:49Z")

</div>

I've managed to figure this out in the end.

The two things I hadn't realised were:

1. You can provide a `script` instead of a `field` key to bucket aggregations.
2. Instead of using `nested` queries, you can access nested values directly using `params._source`.

The combination of these two things allowed me to write the correct query:

```json
{
  "size": 0,
  "aggs": {
    "max.float_property": {
      "terms": {
        "script": "double max = 0; for (item in params._source.entities) { if (item.float_property > max) { max = item.float_property; }} return max;"
      }
    }
  }
}

```

Response:

```json
{
  ...
  "aggregations": {
    "max.float_property": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": "0.9",
          "doc_count": 2
        },
        {
          "key": "0.6",
          "doc_count": 1
        }
      ]
    }
  }
}

```

I'm confused though, because I thought the correct way to access `nested` fields was by using the `nested` query type. Unfortunately there's very little documentation for this, so I'm still unsure if this is the intended/correct way to aggregate on scripted nested fields.

---

<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 6, 2022, 10:32am UTC](https://discuss.elastic.co/t/elasticsearch-aggregate-on-a-scripted-nested-field/298841/3 "2022-03-06T10:32:30Z")

</div>

In my opinion, as nested fields are [separated Lucene documents](https://www.elastic.co/guide/en/elasticsearch/reference/current/nested.html#_limits_on_nested_mappings_and_objects), they are not stored as doc values. So reconstructing from source field is the only way to accessing the nested fields in painless script.

Of cource you can make the script reusable by runtime mappings of the index or do the calculation during indexing using ingest pipeline.

---

<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:** [April 3, 2022, 10:32am UTC](https://discuss.elastic.co/t/elasticsearch-aggregate-on-a-scripted-nested-field/298841/4 "2022-04-03T10:32:45Z")

</div>

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