How to sort on a text field with elastic search

Question

{
  "parent" : "some_id",
  "type" : "support",
  "metadata" : {
    "account_type" : "Regular",
    "subject" : "Test Subject",
    "user_name" : "John Doe",
    "origin" : "Origin",
    "description" : "TEST",
    "media" : [ ],
    "ticket_number" : "XXXX",
    "status" : "completed",
  },
  "create_time" : "2021-02-24T15:08:57.750Z",
  "entity_name" : "comment"
}

This is my demo data. and when I try to sort by metadata.sort for e.g. ->

GET comments-*/_search
{
    "query": {
        "bool": {
            "must": [{
                "match": {
                    "type": "support"
                }
            }]
        }
    },
    "from": 0,
    "size": 50,
    "sort": [{
      "metadata.status": {
         "order": "desc"
       }
    }]
}

it says -> Fielddata is disabled on text fields by default. Set fielddata=true on [metadata.status] in order to load fielddata in memory by uninverting the inverted index. Note that this can however use significant memory. Alternatively use a keyword field instead.

I am not sure how to achieve the same as I am very new to ESS. Any help would be appreciated

Answer 1

Most probably, the issue is that metadata.status is of text type, which is not sortable (see docs). You can sort over a textual field if this is of a keyword type.

Please check the mapping of your index. Most probably, your index has default mapping (see docs), and a keyword sub-field is automatically assigned to every field with a string value.

TL;DR: try to run this query

GET comments-*/_search
{
    "query": {
        "bool": {
            "must": [{
                "match": {
                    "type": "support"
                }
            }]
        }
    },
    "from": 0,
    "size": 50,
    "sort": [{
      "metadata.status.keyword": {
         "order": "desc"
       }
    }]
}

Answer 2

You can only sort by fields of type "keyword" on string fields.

Elasticsearch dynamic mappings will create 2 fields if you dont set the mappings before sending docs.

In this case "status" , and "status.keyword".

So try with "metadata.status.keyword".

TL;DR

It is a good practice for fields you will not be doing full text search (like status flags) to only store the keyword version of the field.

To do that you have to set the mappings before indexing any document.

There is a trick:

1. Ingest Data

POST test_predipa/_doc
{
  "parent" : "some_id",
  "type" : "support",
  "metadata" : {
    "account_type" : "Regular",
    "subject" : "Test Subject",
    "user_name" : "John Doe",
    "origin" : "Origin",
    "description" : "TEST",
    "media" : [ ],
    "ticket_number" : "XXXX",
    "status" : "completed"
  },
  "create_time" : "2021-02-24T15:08:57.750Z",
  "entity_name" : "comment"
}

2. Get the autogenerated mappings

GET test_predipa/_mapping

3. Create a new empty index with the same mappings and modify as you want (on this case remove the text type field from metadata.status and let only the keyword one.

PUT test_predipa_new
{
  "mappings": {
    "properties": {
      "create_time": {
        "type": "date"
      },
      "entity_name": {
        "type": "text",
        "fields": {
          "keyword": {
            "type": "keyword",
            "ignore_above": 256
          }
        }
      },
      "metadata": {
        "properties": {
          "account_type": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "description": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "origin": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "status": {
            "type": "keyword"
          },
          "subject": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "ticket_number": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "user_name": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          }
        }
      },
      "parent": {
        "type": "text",
        "fields": {
          "keyword": {
            "type": "keyword",
            "ignore_above": 256
          }
        }
      },
      "type": {
        "type": "text",
        "fields": {
          "keyword": {
            "type": "keyword",
            "ignore_above": 256
          }
        }
      }
    }
  }
}

4. Move the data from the old index to the new empty one

POST _reindex
{
  "source": {
    "index": "test_predipa"
  },
  "dest": {
    "index": "test_predipa_new"
  }
}

5. Run the sort query

GET test_predipa_new/_search
{
  "query": {
    "bool": {
      "must": [
        {
          "match": {
            "type": "support"
          }
        }
      ]
    }
  },
  "from": 0,
  "size": 50,
  "sort": [
    {
      "metadata.status": {
        "order": "desc"
      }
    }
  ]
}