# Aggregation with script

**URL:** <https://discuss.elastic.co/t/aggregation-with-script/310216>\
**Category:** Elasticsearch\
**Created:** [July 20, 2022, 10:37pm UTC](https://discuss.elastic.co/t/aggregation-with-script/310216 "2022-07-20T22:37:44Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![Jason\_Yu1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jason_yu1/32/108602_2.png) [@Jason\_Yu1](https://discuss.elastic.co/u/Jason_Yu1)\
**Post date:** [July 20, 2022, 10:37pm UTC](https://discuss.elastic.co/t/aggregation-with-script/310216/1 "2022-07-20T22:37:44Z")

</div>

I have some containers like those

```auto

"_source": {
                    "testId": "test001",
                    "containers": [
                        {
                            "name": "containers1",
                            "start": "1658308670434",
                            "end": "1658308670687"
                        },
                        {
                            "name": "containers2",
                            "start": "1658308670688",
                            "end": "1658308670939"
                        },
                        {
                            "name": "containers3",
                            "start": "1658308670940",
                            "end": "1658308671191"
                        },
                        {
                            "name": "containers4",
                            "start": "1658308680940"
                        }
                   ]
               }

```

What I want is to:

1. Filtered out all the containers that don't have names, start or end.
2. Aggregate those with the same testId, and the terms is `containers.name` and the value is `end` - `start`.

my attempt is that.

```auto
{
    "query": {
        "bool": {
            "must": [
                {
                    "match": {
                        "testId": "1234"
                    }
                }
            ]
        }
    },
    "_source": [
        "containers"
    ],
    "aggregations": {
        "containersWithData": {
            "filter": {
                "bool": {
                    "must": [
                        {
                            "exists": {
                                "field": "containers.start"
                            }
                        },
                        {
                            "exists": {
                                "field": "containers.end"
                            }
                        }
                    ]
                }
            },
            "aggregations": {
                "avgContainersDuration": {
                    "terms": {
                        "field": "containers.name"
                    },
                    "aggregations": {
                        "start": {
                            "avg": {
                                "field": "containers.start"
                            }
                        },
                        "end": {
                            "avg": {
                                "field": "containers.end"
                            }
                        },
                        "diff": {
                            "bucket_script": {
                                "buckets_path": {
                                    "start": "start",
                                    "end": "end"
                                },
                                "script": "params.start - params.begin"
                            }
                        }
                    }
                }
            }
        }
    }
}

```

the answer is wrong.

Mapping part

```auto
      "containers": {
        "properties": {
          "name": {
            "type": "keyword"
          },
          "begin": {
            "type": "long"
          },
          "end": {
            "type": "long"
          }
        }
      },

```

---

<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:** [July 21, 2022, 3:44pm UTC](https://discuss.elastic.co/t/aggregation-with-script/310216/2 "2022-07-21T15:44:01Z")

</div>

Could you share the mappings?  
I wonder if containers is really nested field.

---

<div class="post-metadata">

**Author:** ![Jason\_Yu1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jason_yu1/32/108602_2.png) [@Jason\_Yu1](https://discuss.elastic.co/u/Jason_Yu1)\
**Post date:** [July 21, 2022, 4:11pm UTC](https://discuss.elastic.co/t/aggregation-with-script/310216/3 "2022-07-21T16:11:26Z")

</div>

yeah you are right, it is not nested. Updated the mapping in the question

---

<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:** [July 21, 2022, 4:44pm UTC](https://discuss.elastic.co/t/aggregation-with-script/310216/4 "2022-07-21T16:44:45Z")

</div>

I see. You need nested aggregation as this.

```auto
PUT test_nested_agg
{
  "mappings": {
    "properties": {
      "testId": {"type": "keyword"},
      "containers": {
        "type":"nested",
        "properties":{
          "name": {"type": "keyword"},
          "start": {"type": "date"},
          "end": {"type": "date"}
        }
      }
    }
  }
}

POST test_nested_agg/_doc
{
                    "testId": "test001",
                    "containers": [
                        {
                            "name": "containers1",
                            "start": "1658308670434",
                            "end": "1658308670687"
                        },
                        {
                            "name": "containers2",
                            "start": "1658308670688",
                            "end": "1658308670939"
                        },
                        {
                            "name": "containers3",
                            "start": "1658308670940",
                            "end": "1658308671191"
                        },
                        {
                            "name": "containers4",
                            "start": "1658308680940"
                        }
                   ]
               }
               
GET test_nested_agg/_search
{
  "size":1,
  "query":{
    "term":{
      "testId": "test001"
    }
  },
  "aggs":{
    "n":{
      "nested":{
        "path": "containers"
      },
      "aggs":{
        "f":{
          "filter":{
            "bool":{
              "filter":[
                {
                  "exists": {"field": "containers.start"}
                },{
                  "exists": {"field": "containers.end"}
                }
              ]
            }
          },
          "aggs":{
            "t":{
              "terms":{
                "field": "containers.name"
              },
              "aggs":{
                "start":{
                  "avg":{"field": "containers.start"}
                },
                "end":{
                  "avg": {"field":"containers.end"}
                },
                "diff":{
                  "bucket_script": {
                    "buckets_path": {
                      "start":"start",
                      "end":"end"
                    },
                    "script": "params.start - params.end"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}

```

---

<div class="post-metadata">

**Author:** ![Jason\_Yu1](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jason_yu1/32/108602_2.png) [@Jason\_Yu1](https://discuss.elastic.co/u/Jason_Yu1)\
**Post date:** [July 21, 2022, 5:53pm UTC](https://discuss.elastic.co/t/aggregation-with-script/310216/5 "2022-07-21T17:53:56Z")

</div>

that works! thanks for 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:** [August 18, 2022, 5:54pm UTC](https://discuss.elastic.co/t/aggregation-with-script/310216/6 "2022-08-18T17:54:09Z")

</div>

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