So I’m trying to achieve the top_hits value to be aggregated in bucket_script but I got the errors like this

{
    "error": {
        "root_cause": [
            {
                "type": "action_request_validation_exception",
                "reason": "Validation Failed: 1: No aggregation found for path [first_doc.hits.hits._source.close];"
            }
        ],
        "type": "action_request_validation_exception",
        "reason": "Validation Failed: 1: No aggregation found for path [first_doc.hits.hits._source.close];"
    },
    "status": 400
}

It seems the elasticsearch aggregation cannot read the object from top_hits, my goal is to get the top_hits on my bucket_script and appear on my aggregation on this code:

{
    "query": {
        "bool": {
            "must": [
                {
                    // range filter
                    "range": {
                        "date": {
                            "gte": "now-89d",
                            "lte": "now+1d"
                        }
                    }
                }
            ]
        }
    },
    "aggs": {
        "group": {
            "terms": {
                "field": "stock"
            },
            "aggs": {
                "last_doc": {
                    "top_hits": {
                        "size": 1,
                        "sort": [
                            {
                                "date": {
                                    "order": "desc"
                                }
                            }
                        ],
                        "_source": {
                            "includes": ["close", "stock", "date"]
                        }
                    }
                },
                "first_doc": {
                    "top_hits": {
                        "size": 1,
                        "sort": [
                            {
                                "date": {
                                    "order": "asc"
                                }
                            }
                        ],
                        "_source": {
                            "includes": ["close", "stock", "date"]
                        }
                    }
                },
                "ninety_scr":{
                    "bucket_script":{
                        "buckets_path": {
                            "first_doc": "first_doc.hits.hits._source.close",
                            "last_doc": "last_doc.hits.hits._source.close"
                        },
                        "script": "params.first_doc / params.last_doc"
                    }
                }
            }
        }
    }
}

Here’s the example of hits documents, when I take out the ninety_scr bucket_script object :

"hits": [
    {
        "_index": "stocks",
        "_id": "8odITIEBRQt2Zq4UUGu4",
        "_score": 1.0,
        "_source": {
            "date": "2022-06-10 13:23:36",
            "fvei": "8732.75",
            "pbv": "0.0",
            "prev": "81",
            "book": "1050093.39",
            "roe": "6.21",
            "der": "1.85",
            "high": "86",
            "avg": "79.0",
            "fve": "78.0",
            "low": "77",
            "stock": "ADCP",
            "per": "0.0",
            "close": "78",
            "trade_vol": "19784300",
            "group": "IDXPROPERT",
            "paid_up_cap_shares": "2000.0",
            "trade_val": "1569762300",
            "chg": "-3.49",
            "change": "-3.0",
            "peg_ratio": "0.0",
            "eps": "65180.29",
            "trade_freq": "1240",
            "peg_analysis": "negative growth",
            "board": "RG",
            "open": "81"
        }
    }
]

And here’s the result aggregation:

"aggregations": {
    "group": {
        "doc_count_error_upper_bound": 3,
        "sum_other_doc_count": 757,
        "buckets": [
        {
            "key": "ADCP",
            "doc_count": 1,
            "last_doc": {
                "hits": {
                    "total": {
                        "value": 1,
                        "relation": "eq"
                    },
                    "max_score": null,
                    "hits": [
                        {
                            "_index": "stocks",
                            "_id": "8odITIEBRQt2Zq4UUGu4",
                            "_score": null,
                            "_source": {
                                "date": "2022-06-10 13:23:36",
                                "stock": "ADCP",
                                "close": "78"
                            },
                            "sort": [
                                1654867416000
                            ]
                        }
                    ]
                }
            },
            "first_doc": {
                "hits": {
                    "total": {
                        "value": 1,
                        "relation": "eq"
                    },
                    "max_score": null,
                    "hits": [
                        {
                            "_index": "stocks",
                            "_id": "8odITIEBRQt2Zq4UUGu4",
                            "_score": null,
                            "_source": {
                                "date": "2022-06-10 13:23:36",
                                "stock": "ADCP",
                                "close": "78"
                            },
                            "sort": [
                                1654867416000
                            ]
                        }
                    ]
                }
            }
        }
       ]
    }
}