Sort similar data by property

Question

I have the following data:

[
  {
    DocumentId": "85",
    "figureText": "General Seat Assembly - DBL",
    "descriptionShort": "Seat Assembly - DBL",
    "partNumber": "1012626-001FG05",
    "itemNumeric": "5"
  },
  {
    DocumentId": "85",
    "figureText": "General Seat Assembly - DBL",
    "descriptionShort": "Seat Assembly - DBL",
    "partNumber": "1012626-001FG05",
    "itemNumeric": "45"
  }
]

I use the following query to get data:

{
  "query": {
    "bool": {
      "must": {
        "match": {
          "DocumentId": "85"
        }
      },
      "should": [
        {
          "match": {
            "figureText": {
              "boost": 5,
              "query": "General Seat Assembly - DBL",
              "operator": "or"
            }
          }
        },
        {
          "match": {
            "descriptionShort": {
              "boost": 4,
              "query": "Seat Assembly - DBL",
              "operator": "or"
            }
          }
        },
        {
          "term": {
            "partNumber": {
              "boost": 1,
              "value": "1012626-001FG05"
            }
          }
        }
      ]
    }
  }
}

Currently, it will returns the item with "itemNumeric" = 45 and I would like to get itemNumeric = "5" (the lowest).

Is a tips exists to do that ? I tried with "sort":[{"itemNumeric":"desc"}]

Thx

Try adding "sort": [{ "itemNumeric": "asc" }] or "sort": [{ "itemNumeric": { "order": "asc" }}]. If you want only the lowest you could specify "size": 1, to only get one result back (the lowest). — Alex G, Oct 17 '18 at 10:23
The query returns both the documents and its correct. Sort using "desc" and using size as "1", returns the document with itemNumeric 5. Is that what you are looking for? — Kamal Kunjapur, Oct 17 '18 at 11:12
I have this error when I try something "Fielddata is disabled on text fields by default. Set fielddata=true on [itemNumeric] 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 have a keyword field in my mapping ""itemGroup": {"type": "text", "fields": { "keyword": { "type": "keyword", "ignore_above": 256 } } } — Paul, Oct 17 '18 at 11:34

Kamal Kunjapur · Accepted Answer · 2018-10-17T13:04:21.223

0

Looking at your comment, you can resolve the issue in two ways.

Solution 1: Updating your mapping, so that your query would work as expected now

PUT my_index/_mapping/_doc
{
  "properties": {
    "itemNumeric": { 
      "type":     "text",
      "fielddata": true
    }
  }
}

Solution 2: Check the mapping of your itemNumeric field in case if your mapping has been created dynamically, you field itemNumeric would be multi-field.

"itemNumeric": {
    "type": "text",
     "fields": {
         "keyword": { 
             "type":  "keyword"
         }
     }
 }

In this case you can have your sorting logic applied on itemNumeric.keyword field.

"sort":[{"itemNumeric.keyword":"desc"}]

In elasticsearch, whenever you have text data, it is always recommended to have two fields created for it. One of type text so that you can apply full text queries and other of type keyword so that you can use if to implement sorting or any aggregation operations.

Solution 1 is not recommended as ES official documentation mentions below reason

Fielddata is disabled on text fields by default. Set fielddata=true on [your_field_name] in order to load fielddata in memory by uninverting the inverted index. Note that this can however use significant memory.

I'd suggest to read about multi-field and fielddata so that you will have more clarity on what's happening.

edited Oct 17 '18 at 13:04

answered Oct 17 '18 at 12:58

Kamal Kunjapur

8,547
2
22
32

thanks a lot for the answer, I use itemNumeric.keyword and it works but the `score` is defined to null now, is a solution exists to keep the score value with a sorting ? – Paul Oct 17 '18 at 13:09
You can just add `_score` to the sort logic. "sort": [{ "desc.keyword": "asc" }, "_score"] – Kamal Kunjapur Oct 17 '18 at 13:17
Yes it works, just a little another question (even if I know the answer), in the mapping, itemNumeric is a text value. If I sort by asc, 10 will come before 5 ? I have to update the mapping to integer to avoid that ? – Paul Oct 17 '18 at 13:28
Yes that's correct, sorting on string is lexicographical in nature which works differently than sorting on integer or long. Although there is a way to sort even text fields like this with numeric behaviour but its would require to make use of `painless` scripting which won't be efficient. Better strategy is to update your field to integer or long. https://stackoverflow.com/a/45950665/3838328 – Kamal Kunjapur Oct 17 '18 at 13:31

Sort similar data by property

1 Answers1