# Sorting a string field numerically in elasticsearch

**URL:** <https://discuss.elastic.co/t/sorting-a-string-field-numerically-in-elasticsearch/161781>\
**Category:** Elasticsearch\
**Created:** [December 21, 2018, 4:00am UTC](https://discuss.elastic.co/t/sorting-a-string-field-numerically-in-elasticsearch/161781 "2018-12-21T04:00:24Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Nam](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nam/32/39074_2.png) [@Nam](https://discuss.elastic.co/u/Nam)\
**Post date:** [December 21, 2018, 4:00am UTC](https://discuss.elastic.co/t/sorting-a-string-field-numerically-in-elasticsearch/161781/1 "2018-12-21T04:00:24Z")

</div>

Hi guys. When sorting on a field that's a string, strings that contain  
numbers aren't sorted properly.

For example, with these documents with type: string:  
{ login\_id: "1" }  
{ login\_id: "1A" }  
{ login\_id: "1B" }  
{ login\_id: "12" }  
{ login\_id: "123" }  
{ login\_id: "20" }  
{ login\_id: "22" }  
{ login\_id: "user" }  
{ login\_id: "user1" }

When ES sorts on the "login\_id" field, the documents are returned in this order:  
{ login\_id: "1" }  
{ login\_id: "12" }  
{ login\_id: "123" }  
{ login\_id: "20" }  
{ login\_id: "22" }  
{ login\_id: "1A" }  
{ login\_id: "1B" }  
{ login\_id: "user" }  
{ login\_id: "user1" }

How can we get ES to return the documents in the following order?  
{ login\_id: "1" }  
{ login\_id: "12" }  
{ login\_id: "20" }  
{ login\_id: "22" }  
{ login\_id: "123" }  
{ login\_id: "1A" }  
{ login\_id: "1B" }  
{ login\_id: "user" }  
{ login\_id: "user1" }  
I used:  
sort : {  
\_script: {  
type: "string",  
script: {  
source: "def s = doc['login\_id'].value;  
def n = org.elasticsearch.common.primitives.Integer.parseInt(s);  
if (n != null) { String.format("%010d",n)} else { s }"  
},  
order: "#{sort[:sort]}"  
}  
}  
But It's error: "reason":"Variable [org] is not defined."}}}]}

---

<div class="post-metadata">

**Author:** ![xeraa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/xeraa/32/48181_2.png) [@xeraa](https://discuss.elastic.co/u/xeraa)\
**Post date:** [December 22, 2018, 12:20am UTC](https://discuss.elastic.co/t/sorting-a-string-field-numerically-in-elasticsearch/161781/3 "2018-12-22T00:20:29Z")

</div>

What you are trying to do is IMO best covered by the ICU analysis plugin and specifically its [keyword field](https://www.elastic.co/guide/en/elasticsearch/plugins/master/analysis-icu-collation-keyword-field.html).

I gave it a quick shot and tried the following:

```
DELETE test

PUT test
{
  "mappings": {
    "_doc": {
      "properties": {
        "login_id": {   
          "type": "text",
          "fields": {
            "sort": {  
              "type": "icu_collation_keyword",
              "index": false,
              "numeric": true,
              "case_level": false
            }
          }
        }
      }
    }
  }
}

POST test/_doc
{ "login_id": "1" }

POST test/_doc
{ "login_id": "1A" }

POST test/_doc
{ "login_id": "1B" }

POST test/_doc
{ "login_id": "12" }

POST test/_doc
{ "login_id": "123" }

POST test/_doc
{ "login_id": "20" }

POST test/_doc
{ "login_id": "22" }

POST test/_doc
{ "login_id": "user" }

POST test/_doc
{ "login_id": "user1" }

POST test/_search
{
  "query": {
    "match_all": {}
  },
  "sort": [
    {
      "login_id.sort": {
        "order": "asc"
      }
    }
  ]
}

```

This gives you back 1, 1A, 1B, 12, 20, 22, 123, user, user1. I'm not sure if it's (easily) possible to get 1A after 123; that feels rather counterintuitive.

---

<div class="post-metadata">

**Author:** ![Nam](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/nam/32/39074_2.png) [@Nam](https://discuss.elastic.co/u/Nam)\
**Post date:** [December 24, 2018, 8:29am UTC](https://discuss.elastic.co/t/sorting-a-string-field-numerically-in-elasticsearch/161781/4 "2018-12-24T08:29:49Z")

</div>

It's not working

---

<div class="post-metadata">

**Author:** ![xeraa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/xeraa/32/48181_2.png) [@xeraa](https://discuss.elastic.co/u/xeraa)\
**Post date:** [December 24, 2018, 2:14pm UTC](https://discuss.elastic.co/t/sorting-a-string-field-numerically-in-elasticsearch/161781/5 "2018-12-24T14:14:30Z")

</div>

This is not a helpful comment: What specifically is not working? Sorting is wrong, you have issues installing the ICU plugin (which I didn't explicitly mention, sorry),...

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [January 21, 2019, 2:14pm UTC](https://discuss.elastic.co/t/sorting-a-string-field-numerically-in-elasticsearch/161781/6 "2019-01-21T14:14:33Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.