Term aggregations - cannot order by keyword field

OpenSearch
1

Hi,
I’m using term aggregations, and apparently there is no way to sort the results based on a keyword field. Sorting by numeric or date field does work.
Anyone can suggest how to overcome this problem?
If not, is there any planned enhancement for OpenSearch to support that?

here is the query I use (for example):

POST fileinstances/_search
{
  "size": 0,
  "aggregations": {
    "types": {
      "terms": {
        "field": "fileInfo.fileQualifier.keyword",
        "size": 250,
        "min_doc_count": 1,
        "shard_min_doc_count": 0,
        "show_term_doc_count_error": false,
        "order": [
          {
            "agents_count": "desc"
          }
        ]
      },
      "aggregations": {
        "file_name": {
          "terms": {
            "field": "fileInfo.fileName.keyword"
          }
        },
        "agents_count": {
          "cardinality": {
            "field": "agentId.keyword"
          }
        }
      }
    }
  }
}

If I switch in the order the “agents_count” with “file_name”, I am getting the following error:

{
  "error" : {
    "root_cause" : [
      {
        "type" : "aggregation_execution_exception",
        "reason" : "Invalid aggregation order path [file_name]. Buckets can only be sorted on a sub-aggregator path that is built out of zero or more single-bucket aggregations within the path and a final single-bucket or a metrics aggregation at the path end."
      }
    ],
    "type" : "search_phase_execution_exception",
    "reason" : "all shards failed",
    "phase" : "query",
    "grouped" : true,
    "failed_shards" : [
      {
        "shard" : 0,
        "index" : "fileinstances-000001",
        "node" : "_9kqI0eCQKqjcQYrfcVO9A",
        "reason" : {
          "type" : "aggregation_execution_exception",
          "reason" : "Invalid aggregation order path [file_name]. Buckets can only be sorted on a sub-aggregator path that is built out of zero or more single-bucket aggregations within the path and a final single-bucket or a metrics aggregation at the path end.",
          "caused_by" : {
            "type" : "illegal_argument_exception",
            "reason" : "Buckets can only be sorted on a sub-aggregator path that is built out of zero or more single-bucket aggregations within the path and a final single-bucket or a metrics aggregation at the path end."
          }
        }
      }
    ]
  },
  "status" : 500
}

Please also see the related github issue I opened a long time ago:
https://github.com/opensearch-project/OpenSearch/issues/899

Thanks.

2

Hello, Anyone has answer??

3

I don’t think OpenSearch supports this kind of sort. I’m not really sure how to overcome this, either. Maybe a scripted metric aggregation on the inner aggregation will help return a number?