How to execute search and aggregation query on two indexes in Elasticsearch?

Question

elasticsearch

Created two indexes with dates.

Mapping the first index:

PUT /index_one
{
    "mappings": {
        "properties": {
            "date_start": {
                "type": "date",
                "format": "yyyy-MM-dd HH:mm:ss.SSSZZ||yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis"
            }
        }
    }
}

Mapping of the second index:

PUT /index_two
{
    "mappings": {
        "properties": {
            "date_end": {
                "type": "date",
                "format": "yyyy-MM-dd HH:mm:ss.SSSZZ||yyyy-MM-dd HH:mm:ss||yyyy-MM-dd||epoch_millis"
            }
        }
    }
}

It is necessary to find dates in a certain interval and perform an aggregation of the average from the difference of dates.

Tried to make a query like this:

GET /index_one,index_two/_search?scroll=1m&q=[2021-01-01+TO+2021-12-31]&filter_path=aggregations,hits.total.value,hits.hits
{
    "aggs": {
        "filtered_dates": {
            "filter": {
                "bool": {
                    "must": [
                        {
                            "exists": {
                                "field": "date_start"
                            }
                        },
                        {
                            "exists": {
                                "field": "date_end"
                            }
                        }
                    ]
                }
            },
            "aggs": {
                "avg_date": {
                    "avg": {
                        "script": {
                            "lang": "painless",
                            "source": "doc['date_end'].value.toInstant().toEpochMilli() - doc['date_begin'].value.toInstant().toEpochMilli()"
                        }
                    }
                }
            }
        }
    }
}

I receive the following response to the request:

{
    "hits": {
        "total": {
            "value": 16508
        },
        "hits": [
            {
                "_index": "index_one",
                "_type": "_doc",
                "_id": "93a34c5b-101b-45ea-9965-96a2e0446a28",
                "_score": 1.0,
                "_source": {
                    "date_begin": "2021-02-26 07:26:29.732+0300"
                }
            }
        ]
    },
    "aggregations": {
        "filtered_dates": {
            "meta": {},
            "doc_count": 0,
            "avg_date": {
                "value": null
            }
        }
    }
}

Tell me, please, is it possible to make a query with search and aggregation on two indexes in Elasticsearch? If possible, how?

Answer 1

It seems to me that aggregation should work here. Perhaps you have a problem calculating 'avg'? Check the same with one index - in my opinion, there will be an error too.