Sorting a terms aggregation on a child top_metrics aggregation fails with a keyword field

[ddolcimascolo]

Elasticsearch version (bin/elasticsearch --version): 7.13.3

Plugins installed: []

JVM version (java -version): Bundled JDK

OS version (uname -a if on a Unix-like system): Linux 5.11.0-34-generic #36~20.04.1-Ubuntu SMP Fri Aug 27 08:06:32 UTC 2021 x86_64 x86_64 x86_64 GNU/Linux

Description of the problem including expected versus actual behavior:

Sorting a terms aggregation on a child top_metrics aggregation does not work with a keyword field. Sorting does not happen and the terms aggregation is sorted by _key as if there was no order defined. Sorting on a number metric works just fine.

Steps to reproduce:

Kibana script to reproduce

PUT /sorting_on_top_metrics_keyword
{
  "mappings": {
    "properties": {
      "@timestamp": {
        "type": "date"
      },
      "uuid": {
        "type": "keyword"
      },
      "name": {
        "type": "keyword"
      },
      "metric": {
        "type": "long"
      }
    }
  }
}

PUT /sorting_on_top_metrics_keyword/_bulk
{ "index": {} }
{ "@timestamp": "2021-10-01", "metric": 1, "name": "first", "uuid": "0002" }
{ "index": {} }
{ "@timestamp": "2021-10-02", "metric": 2, "name": "first", "uuid": "0002" }
{ "index": {} }
{ "@timestamp": "2021-10-01", "metric": 1, "name": "second", "uuid": "0001" }
{ "index": {} }
{ "@timestamp": "2021-10-02", "metric": 3, "name": "second", "uuid": "0001" }
{ "index": {} }
{ "@timestamp": "2021-10-01", "metric": 1, "name": "third", "uuid": "0003" }
{ "index": {} }
{ "@timestamp": "2021-10-02", "metric": 1, "name": "third", "uuid": "0003" }

POST /sorting_on_top_metrics_keyword/_search
{
  "size": 0,
  "_source": false, 
  "aggs": {
    "total": {
      "cardinality": {
        "field": "uuid"
      }
    },
    "hits": {
      "terms": {
        "field": "uuid",
        "order": {
          "hit.name": "desc"
        }
      },
      "aggs": {
        "hit": {
          "top_metrics": {
            "sort": { "@timestamp": "desc" },
            "metrics": [
              { "field": "metric" },
              { "field": "name" }
            ]
          }
        }
      }
    }
  }
}

Produces

{
  "took" : 0,
  "timed_out" : false,
  "_shards" : {
    "total" : 1,
    "successful" : 1,
    "skipped" : 0,
    "failed" : 0
  },
  "hits" : {
    "total" : {
      "value" : 6,
      "relation" : "eq"
    },
    "max_score" : null,
    "hits" : [ ]
  },
  "aggregations" : {
    "hits" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 0,
      "buckets" : [
        {
          "key" : "0001",
          "doc_count" : 2,
          "hit" : {
            "top" : [
              {
                "sort" : [
                  "2021-10-02T00:00:00.000Z"
                ],
                "metrics" : {
                  "metric" : 3,
                  "name" : "second"
                }
              }
            ]
          }
        },
        {
          "key" : "0002",
          "doc_count" : 2,
          "hit" : {
            "top" : [
              {
                "sort" : [
                  "2021-10-02T00:00:00.000Z"
                ],
                "metrics" : {
                  "metric" : 2,
                  "name" : "first"
                }
              }
            ]
          }
        },
        {
          "key" : "0003",
          "doc_count" : 2,
          "hit" : {
            "top" : [
              {
                "sort" : [
                  "2021-10-02T00:00:00.000Z"
                ],
                "metrics" : {
                  "metric" : 1,
                  "name" : "third"
                }
              }
            ]
          }
        }
      ]
    },
    "total" : {
      "value" : 3
    }
  }
}

But I was expecting my buckets to be ordered [third, second, first]

Provide logs (if relevant): N/A

[elasticmachine]

Pinging @elastic/es-analytics-geo (Team:Analytics)

[not-napoleon]

Yup, this looks like a bug. It's definitely intended to work, and we reproduced it following your steps. I'm wrapping up some other work, but I'll take a look at this next and hopefully get a fix for you soon. Thanks for reporting it.

[ddolcimascolo]

Thx @not-napoleon

We're starting to run some extensive testing trying to replace top_hits by top_metrics. I will report any issues I found as usual.

Keep up the great work!

David

[ddolcimascolo]

Hey guys,

Any news on this one?
We're still unsure about using top_metrics in our app, but from my understanding this issue was something you wanted to fix anyway.

Cheers,
David