Elastic Search Sorting Results Based on key

Question

I am trying to get data from elastic search. But not able to sort the results.

Here is search query:

{
    "size": 20,
    "from": 0,
    "sort": {
        "email": {
            "order": "desc"
        }
    },
    "query": {
        "bool": {
            "must": [
                "",
                {
                    "range": {
                        "created_at": {
                            "gte": "2017-01-01",
                            "lte": "now"
                        }
                    }
                }
            ]
        }
    }
}

Response:

{
    "error": {
        "root_cause": [
            {
                "type": "illegal_argument_exception",
                "reason": "Fielddata is disabled on text fields by default. Set fielddata=true on [email] 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."
            }
        ],
        "type": "search_phase_execution_exception",
        "reason": "all shards failed",
        "phase": "query",
        "grouped": true,
        "failed_shards": [
            {
                "shard": 0,
                "index": "search",
                "node": "XLaHCsHWR9WHIFXgT5o7nw",
                "reason": {
                    "type": "illegal_argument_exception",
                    "reason": "Fielddata is disabled on text fields by default. Set fielddata=true on [email] 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."
                }
            }
        ],
        "caused_by": {
            "type": "illegal_argument_exception",
            "reason": "Fielddata is disabled on text fields by default. Set fielddata=true on [email] 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.",
            "caused_by": {
                "type": "illegal_argument_exception",
                "reason": "Fielddata is disabled on text fields by default. Set fielddata=true on [email] 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."
            }
        }
    },
    "status": 400
}

Above search query working fine when I remove the sort property and return the original results.

"sort": {
        "email": {
            "order": "desc"
        }
    },

Similar output look like an array of objects.

        "hits": [
            {
                "_index": "search",
                "_type": "search",
                "_id": "218321",
                "_score": 1.0,
                "_source": {
                    "orderId": 211,
                    "commission_waived": 0,
                    "shippingTreeId": null,
                    "email": "[email protected]",
                    "userId": 787,
                    "firstName": "manoj",
                    "currency": "USD",
                    "item_quantity": 5,
                    "lastName": "manoj",
                    "fullName": "manoj manoj",
                    "affiliatedBy": 10452,
                    "affiliatedByName": "manoj manoj",
                    "office_specialist": null,
                    "grandTotal": "101.03",
                    "conversionType": "ct0",
                    "created_at": "2023-04-28T04:14:12.000Z",
                    "transactionId": "cf_seffdghghkjgd54564",
                    "orderStatus": "Processing",
                    "status": "0",
                    "carrier": null,
                    "tracking_number": null,
                    "paymentStatus": "Paid",
                    "shipment_tree_id": null,
                    "warehouse_name": null,
                    "warehouse_id": null,
                    "updated_at": "2023-04-28T04:14:14.000Z",
                    "shipment_url": null,
                    "couponCode": "testing23",
                    "id": "2181",
                    "paymentStatusId": 0
                }
            }
]

Anyone can correct me where I am missing in elastic search query.

Answer 1

Tldr;

This is a mapping issue. Your field email must be using the text type.

Fielddata is disabled on text fields by default.

What are field data ?

text fields are searchable by default, but by default are not available for aggregations, sorting, or scripting. If you try to sort, aggregate, or access values from a text field using a script, you’ll see an exception indicating that field data is disabled by default on text fields.

Solution

Use email.keyword

If you have not set the mapping manually, chances are that you have:

• email as a text field
• email.keyword as a keyword subfield

In that case you would just need to:

{
    "size": 20,
    "from": 0,
    "sort": {
        "email.keyword": {
            "order": "desc"
        }
    },
    "query": {
        "bool": {
            "must": [
                "",
                {
                    "range": {
                        "created_at": {
                            "gte": "2017-01-01",
                            "lte": "now"
                        }
                    }
                }
            ]
        }
    }
}

Change the mapping + re-index

PUT search_2
{
  "mappings": {
    "properties": {
      "email":{
        "type": "keyword"
      }
      .
      .
      .
    }
  }
}

Then you can re-index the data in the first index search in search_2

POST _reindex
{
  "source": {
    "index": "search"
  },
  "dest": {
    "index": "search_2"
  }
}

Use fielddata=true

Using text field to perform aggregation / sorting is not the most effective way to go.

Loading field data in memory can consume significant memory.

PUT /search/
{
  "mappings": {
    "properties": {
      "email":{
        "type": "text",
        "fielddata": true
      }
    }
  }
}

Answer 2

May be this error message show email field is text field, means you need to enable fielddata option. You can update the mapping for the index, such as:

"email": {
      "type": "text",
      "fielddata": true
    }

and the second way is to create field and set keyword:

{
  "properties": {
    "email": {
      "type": "text",
      "fields": {
        "keyword": {
          "type": "keyword"
        }
      }
    }
  }
}

After mapping you write a query like:

{
    "size": 20,
    "from": 0,
    "sort": {
        "email.keyword": {
            "order": "desc"
        }
    },
    "query": {
        "bool": {
            "must": [
                "",
                {
                    "range": {
                        "created_at": {
                            "gte": "2017-01-01",
                            "lte": "now"
                        }
                    }
                }
            ]
        }
    }
}