Elasticsearch sorting - not getting expected results

Question

I have data in elastic field name as "Amit 111", "amit 111", "Amit 222".

I am trying to sort it using:

   searchSourceBuilder.query(query).sort("name.keyword", SortOrder.ASC)

It returns result as: "Amit 111", "Amit 222", "amit 111"

But I want results as : "Amit 111", "amit 111", "Amit 222"

Please help.

score 2 · Answer 1 · answered May 26 '20 at 07:15

Another approach is to use the fielddata as on text field you can apply the sort, more details on the linked URL.

Java code for that you need to change the index mapping as shown after java code.

searchSourceBuilder.query(query).sort("name", SortOrder.ASC)

Create index with field data enabled on the `name` field

{
  "mappings": {
    "properties": {
      "name": { 
        "type": "text",
        "fielddata": true
        }
      }
    }
  }

Index example documents

{
  "name" : "amit 111"
}

{
  "name" : "Amit 111"
}

{
  "name" : "Amit 222"
}

Your search query with sort on `name` field

{
    "sort": [
        {
            "name": "asc"
        }
    ]
}

Result

 "hits": [
      {
        "_index": "key",
        "_type": "_doc",
        "_id": "1",
        "_score": null,
        "_source": {
          "name": "amit 111"
        },
        "sort": [
          "111"
        ]
      },
      {
        "_index": "key",
        "_type": "_doc",
        "_id": "2",
        "_score": null,
        "_source": {
          "name": "Amit 111"
        },
        "sort": [
          "111"
        ]
      },
      {
        "_index": "key",
        "_type": "_doc",
        "_id": "3",
        "_score": null,
        "_source": {
          "name": "Amit 222"
        },
        "sort": [
          "222"
        ]
      }
    ]

but fieldData causes an overhead and is expensive as far as I could infer. — amit kumar singh, May 26 '20 at 08:06
@amitkumarsingh thanks for your comment, yeah it's expensive but if you don't have much data its OK, this option is given by Elasticsearch and again it depends on your use-case to use it or not :) — , May 26 '20 at 08:24
@amitkumarsingh also enabling field data. isn't much overhead(wrt to code complexity). Also it would be great if you can accept and upvote answer as it solves the problem, TIA :) — , May 26 '20 at 08:27

score 1 · Answer 2 · answered May 26 '20 at 07:07

keyword fields are stored as it is so sorting on keyword fields is case sensitive.Normalizer with lowercase filter can be used to index keyword fields.

The normalizer property of keyword fields is similar to analyzer except that it guarantees that the analysis chain produces a single token.

Mapping:

{
  "settings": {
    "analysis": {
      "normalizer": {
        "my_normalizer": {
          "type": "custom",
          "filter": [
            "lowercase"
          ]
        }
      }
    }
  },
  "mappings": {
    "properties": {
      "name": {
        "type": "text",
        "fields": {
          "keyword": {
            "type": "keyword",
            "normalizer": "my_normalizer"
          }
        }
      }
    }
  }
}

Query: Both sort on name.keyword and term query on name.keyword will be case insensitive

{
  "query": {
    "match_all": {}
  },
  "sort": [
    {
      "name.keyword": {
        "order": "asc"
      }
    }
  ]
}

Result:"

"hits" : [
      {
        "_index" : "index84",
        "_type" : "_doc",
        "_id" : "SBvLT3IB8mx5yKbJQ7EC",
        "_score" : null,
        "_source" : {
          "name" : "Amit 111"
        },
        "sort" : [
          "amit 111"
        ]
      },
      {
        "_index" : "index84",
        "_type" : "_doc",
        "_id" : "SRvLT3IB8mx5yKbJULFl",
        "_score" : null,
        "_source" : {
          "name" : "amit 111"
        },
        "sort" : [
          "amit 111"
        ]
      },
      {
        "_index" : "index84",
        "_type" : "_doc",
        "_id" : "ShvLT3IB8mx5yKbJaLFg",
        "_score" : null,
        "_source" : {
          "name" : "Amit 222"
        },
        "sort" : [
          "amit 222"
        ]
      }
    ]

Elasticsearch sorting - not getting expected results

2 Answers2

Create index with field data enabled on the name field

Index example documents

Your search query with sort on name field

Result

Create index with field data enabled on the `name` field

Your search query with sort on `name` field