CODEWITHSUNDEEP
elasticsearch
J.Done
J.Done

Reputation: 3053

Elasticsearch aggregation sort by _key

I'm using elasticsearch 6.2. aggregation's _key field is string BUT since theses data contain number, I would like to treat as NUMBER. So I can get result like [1, 3, 7, 11, 13] order , not [1, 11, 13, 3, 7]

"agg_oid": {
  "terms": {
    "field": "oid",
    "size": 10,
    "order": {
      "_key": "desc" // how should I change here?
      }
    }
  },

Upvotes: 0

Views: 436

Answers (1)

Val
Val

Reputation: 217554

Since your oid field can contain alphanumeric data, I suggest that you create a sub-field that will contain only numeric data and then you can use it in your aggregation.

Your current mapping must look like this:

{
  "oid": {
    "type": "keyword"
  }
}

Change it to

{
  "oid": {
    "type": "keyword",
    "fields": {
      "numeric": {
        "type": "integer",
        "ignore_malformed": true
      }
    }
  }
}

And then you'll be able to aggregate on the numeric sub-field and get the result you expect, like this:

POST test/_search
{
  "size": 0,
  "aggs": {
    "agg_oid": {
      "terms": {
        "field": "oid.numeric",
        "size": 10,
        "order": {
          "_key": "desc"
        }
      }
    }
  }
}

UPDATE:

If you don't want or can't change your mapping, what you can do is the following. First enable fielddata on the oid field (beware that this can have a big impact on performance and your heap)

PUT your_index/_mapping/your_type
{
  "properties": {
    "oid": {
      "type":"text",
      "fielddata": true
    }
  }
}

Then you can run the following query and it will sort your keys as you expect:

POST your_index/_search
{
  "size": 0,
  "aggs": {
    "agg_oid": {
      "terms": {
        "field":"oid",
        "size": 10,
        "order": {
          "oidNum": "desc"
        }
      },
      "aggs": {
        "oidNum": {
          "avg": {
            "script": "try {return Integer.parseInt(params._source.oid)} catch(NumberFormatException nfe){return 0;}"
          }
        }
      }
    }
  }
}

Upvotes: 1

Related Questions