CODEWITHSUNDEEP
javaelasticsearch
André Luiz
André Luiz

Reputation: 7332

Elasticsearch: sort by a numberic field

I'm coding a console app to Insert data from JSOIN files into Elasticsearch 7.5.1. The _id field should be an int and act like auto-increment. To make that happen, before inserting I get the last ID inserted and increment 1.

My problem is that I'm having trouble getting the last ID, because the ordering is happening on a string. This is what I mean: if you have 10 items and the last ID is 10, when querying and sorting it will return 9.

This is my query when using Postman:

GET my_index/_search
{
  "size": 1, 
  "query": {
    "match_all": {}
  },
 "sort": [{
      "_id": {
        "order": "desc"
      }
    }
  ]
}

And my Java function using their client:

private static String getLastElasticSearchId(String index)
{
    RestHighLevelClient client = getElasticSearchClient();

    SearchRequest searchRequest = new SearchRequest(index);
    SearchSourceBuilder b = new SearchSourceBuilder();
    b.query(QueryBuilders.matchAllQuery());
    b.sort(new FieldSortBuilder("_id").order(SortOrder.DESC));
    b.from(0);
    b.size(1);
    searchRequest.source(b);

    try {

        SearchResponse searchResponse = client.search(searchRequest, RequestOptions.DEFAULT);
        SearchHits hits = searchResponse.getHits();

        if(hits.getTotalHits().value > 0){
            SearchHit[] searchHits = hits.getHits();
            for (SearchHit hit : searchHits) {
                return hit.getId();
            }
        }
        else {
            return "0";
        }

    } catch (IOException e) {
        e.printStackTrace();
    } finally {
        try {
            client.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    return null;
}

Both return 9 as the last ID, even having another item in the index with 10 as ID.

I cannot apply string padding in the IDs as answered here.

How can I achieve what I need?

Upvotes: 0

Views: 1342

Answers (1)

Assael Azran
Assael Azran

Reputation: 2993

Try this:

GET my_index/_search
{
  "size": 1, 
  "query": {
    "match_all": {}
  },
  "sort" : {
        "_script" : {
            "type" : "number",
            "script" : {
                "lang": "painless",
                "source": "Integer.parseInt(doc['_id'].value)"

            },
            "order" : "desc"
        }
    }
}

Hope this helps

Upvotes: 2

Related Questions