access nested variable from sub-aggregation on elasticsearch

Question

I have an index with documents that look like:

{
    "id": 1,
    "timeline": [{
        "amount": {
            "mpe": 30,
            "drawn": 20
        },
        "interval": {
            "gte": "2020-03-01",
            "lte": "2020-04-01"
        }
    }, {
        "amount": {
            "mpe": 40,
            "drawn": 10
        },
        "interval": {
            "gte": "2020-04-01",
            "lte": "2020-06-01"
        }
    }]
}

Then I have the following query that produces a time bucketed sum of the values from the original intervals:

{
    "aggs": {
        "cp-timeline": {
            "nested": {
                "path": "timeline"
            },
            "aggs": {
                "mpes": {
                    "date_histogram": {
                        "field": "timeline.interval",
                        "calendar_interval": "day"
                    },
                    "aggs": {
                        "sum_mpe": {
                            "sum": {
                                "field": "timeline.amount.mpe"
                            }
                        },
                        "sum_drawn": {
                            "sum": {
                                "field": "timeline.amount.drawn"
                            }
                        }
                    }
                }
            }
        }
    }
}

The above works like a charm yielding the correct sum for each day. Now I want to improve it so I can dynamically multiply the values by a given number that may vary between query executions, although for simplicity I will just use a fixed number 2. I've tried the following:

{
    "aggs": {
        "cp-timeline": {
            "nested": {
                "path": "timeline"
            },
            "aggs": {
                "mpes": {
                    "date_histogram": {
                        "field": "timeline.interval",
                        "calendar_interval": "day"
                    },
                    "aggs": {
                        "sum_mpe": {
                            "sum": {
                                "script": "timeline.amount.mpe * 2"
                            }
                        },
                        "sum_drawn": {
                            "sum": {
                                "script": "timeline.amount.drawn * 2"
                            }
                        }
                    }
                }
            }
        }
    }
}

But I get the following error:

{
    "reason": {
        "type": "script_exception",
        "reason": "compile error",
        "script_stack": [
            "timeline.amount.mpe * 2",
            "^---- HERE"
        ],
        "script": "timeline.amount.mpe * 2",
        "lang": "painless",
        "position": {
            "offset": 0,
            "start": 0,
            "end": 23
        },
        "caused_by": {
            "type": "illegal_argument_exception",
            "reason": "Variable [timeline] is not defined."
        }
    }
}

Is there a way to make the nested variable declared above available in the script?

Dharman · Accepted Answer · 2020-08-07 15:14:54Z

1

This link states as how to access the fields via script. Note that you can only use this for fields which are analyzed i.e. text type.

The below should help:

POST <your_index_name>/_search
{
  "size": 0,
  "aggs": {
    "cp-timeline": {
      "nested": {
        "path": "timeline"
      },
      "aggs": {
        "mpes": {
          "date_histogram": {
            "field": "timeline.interval.gte",
            "calendar_interval": "day",
            "min_doc_count": 1                                       <---- Note this
          },
          "aggs": {
            "sum_mpe": {
              "sum": {
                "script": "doc['timeline.amount.mpe'].value * 2"     <---- Note this
              }
            },
            "sum_drawn": {
              "sum": {
                "script": "doc['timeline.amount.drawn'].value * 2"   <---- Note this
              }
            }
          }
        }
      }
    }
  }
}

Also note that I've made use of min_doc_count so that your histogram would only show you the valid dates.

edited Aug 7, 2020 at 15:14

Dharman♦

33.9k27 gold badges103 silver badges157 bronze badges

answered Aug 7, 2020 at 15:08

Kamal Kunjapur

8,9182 gold badges26 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

franDayz Over a year ago

thank you very much for your answer! Just one more thing, how can I inject the multiplier as a parameter? I cannot find a way on the docs :(

Collectives™ on Stack Overflow

access nested variable from sub-aggregation on elasticsearch

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related