Elasticsearch: sort terms aggregation buckets by non-key column

user_4432432 0
Viewed 1333

Data

I have objects persisted in an ES index. Each of them has a myKey and myName string fields (persisted as keyword fields). There is no guarantee that myName will always be the same for the same myKey. E.g. the following two entries share the same myKey but have different myName values:

{
  "myKey": "123asd",
  "myName": "United States",
  ...
},
{
  "myKey": "123asd",
  "myName": "United States of America",
  ...
},
{
  "myKey": "456fgh",
  "myName": "United Kingdom",
  ...
}

Challenge

I need to select and return all distinct myKey values, find and display the most likely myName (most occurances within the context of myKey) AND sort the resulting buckets by myName.

So far I managed the following:

  1. Select the distinct myKey values by using a terms aggregation.
  2. Select the corresponding first myName value to each myKey by using a top_hits aggregation.
  3. Sorted by myKey using the order clause of the terms aggregation.

This is the code of the aggregation:

"aggs": {
"distinct": {
  "terms": {
    "field": "myKey",
    "order": {
      "_key": "desc"    <----- this sorts the buckets by myKey
    }
  },
  "aggs": {
    "tops": {
      "top_hits": {
        "size": 1,
        "_source": {
          "includes": ["myName"]
        }
      }
    }
  }
}

I read up on the ES documentation explaining how one can introduce a second aggregation returning a single metric. This appears to address numeric field only though. myName is not numeric.

Is there a way to sort the buckets in ES by myName?

Any help greatly appreciated.

Edit on 2. Sept 2020

Based on the asking of user @joe, current and the expected result are as follows.

Current result

As it is apparent, the sorting of the buckets is based on the key: 123asd comes before 456fgh:

"aggregations" : {
  "distinct" : {
    "buckets" : [
      {
        "key" : "123asd",
        "tops" : {
          "hits" : {
            "hits" : [
              {
                "_source" : {
                  "myName" : "United States"
                }
              }
            ]
          }
        }
      },
      {
        "key" : "456fgh",
        "tops" : {
          "hits" : {
            "hits" : [
              {
                "_source" : {
                  "myName" : "United Kingdom"
                }
              }
            ]
          }
        }
      }
    ]
  }
}

Expected result

The task is to sort the buckets based on the extra selected field myName: United Kingdom comes before United States:

"aggregations" : {
  "distinct" : {
    "buckets" : [
      {
        "key" : "456fgh",
        "tops" : {
          "hits" : {
            "hits" : [
              {
                "_source" : {
                  "myName" : "United Kingdom"
                }
              }
            ]
          }
        }
      },
      {
        "key" : "123asd",
        "tops" : {
          "hits" : {
            "hits" : [
              {
                "_source" : {
                  "myName" : "United States"
                }
              }
            ]
          }
        }
      }
    ]
  }
}
elasticsearch
1 Answers

By doing _count:desc, you've only ordered the top agg alphabetically...

Have you tried the following which looks for the most frequest myNames under a given myKey?

{
  "size": 0, 
  "aggs": {
    "by_key": {
      "terms": {
        "field": "myKey",
        "order": {
          "_key": "desc"
        }
      },
      "aggs": {
        "by_name": {
          "terms": {
            "field": "myName",
            "order":{
              "_count": "desc"
            }
          }
        }
      }
    }
  }
}

Or are you looking to sort the parent myKey agg by the result of the child myName agg?

EDIT

Sorting a parent agg by the result of a multi-bucket child aggregation results in the following error:

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.

In other words, what you're trying to achieve is not possible and here's nicely explained why.

Had your child aggregation been numeric (or single-bucket), it would've been possible.

For now your only option appears to be post-processing (or rather post-sorting) the current response in the frontend (or wherever you're using these aggs).

user_81603180
Related