Aggregations on nested documents with painless scripting

Question

USING ELASTIC SEARCH 6.2 So I have a deeply nested document structure which has all the proper mapping (nested, text, keyword, etc). A sample document is as follows:

{
    "type": "Certain Type",
    "lineItems": [
        {
            "lineValue": 10,
            "events": [
                {
                    "name": "CREATED",
                    "timeStamp": "TIME VALUE"
                },
                {
                    "name": "ENDED",
                    "timeStamp": "TIME VALUE"
                }
            ]
        }
    ]
}

What I want to do is find out the average time required for all lines to go from CREATED to ENDED.

I created the following query

GET /_search
{
  "size": 0,
  "query": {
    "match": {
      "type": "Certain Type"
    }
  },
  "aggs": {
    "avg time": {
      "nested": {
        "path": "lineItems.events"
      },
      "aggs": {
        "avg time": {
          "avg": {
            "script": {
              "lang": "painless",
              "source": """
          long timeDiff = 0; 
          long fromTime = 0; 
          long toTime = 0; 
          if(doc['lineItems.events.name.keyword'] == "CREATED"){
            fromTime = doc['lineItems.events.timeValue'].value.getMillis();
          }
          else if(doc['lineItems.events.name.keyword'] == "ENDED"){
            toTime = doc['lineItems.events.timeValue'].value.getMillis();
          }
          timeDiff = toTime-fromTime;
          return (timeDiff)
          """
            }
          }
        }
      }
    }
  }
}

The Result was that I got 0 as the aggregation result which is wrong.

Is there any way to achieve this?

score 1 · Answer 1 · edited Mar 26 '19 at 17:48

Use doc[ in nested object script does not work as nested are a new document for elastic search.

Use params._source instead (https://www.elastic.co/guide/en/elasticsearch/reference/current/search-request-script-fields.html). Note access to source would be really slow, if you have a lot of documents or if you need to request this query a lot, consider add this field on main document.

I consider all value exist, add if robustness test if needed, this should work.

long toTime = 0; 
long fromTime = 0; 
timeDiff  = params['_source']['ENDED'] 
fromTime  = params['_source']['CREATED']
return (toTime - fromTime);

Aggregations on nested documents with painless scripting

1 Answers1