Aggregation with script

Elastic Stack Elasticsearch
1

I have some containers like those


"_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.

{
    "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

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

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

3

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

4

I see. You need nested aggregation as this.

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"
                  }
                }
              }
            }
          }
        }
      }
    }
  }
}
5

that works! thanks for the help!!

6

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