Elasticsearch - perform multi-field max aggregation

Question

I have an index that contains events from different sources, therefore, I have two different names for the same field, "accounting_date" and "dt_comptable", and I need to query and get the max of the two field's values, The following query worked for me for one single field:

GET indexXX/_search
{
  "size": 0,
  "aggs": {
    "latest_accounting_date": {
      "max": {
        "field": "dt_comptable"
      }
    }
  }
}  

I need to include the other field "accounting_date".

Answer 1

Enter scripting... Instead of specifying a field you can specify a script that will retrieve either field, whichever is present, but the max aggregation will be able to work on both of them:

GET indexXX/_search
{
  "size": 0,
  "aggs": {
    "latest_accounting_date": {
      "max": {
        "script": {
          "source": "doc['dt_comptable'].size() > 0 ? doc['dt_comptable'].value : doc['accounting_date'].value"
        }
      }
    }
  }
}