Sort data by field in Elasticsearch

[aniketpatil (Aniket)]

I have written elasticsearch query using java api in which status count is fetched per day.
Code:

SearchResponse responseOutput = client.prepareSearch(ConstantsValue.indexName)
.setTypes(ConstantsValue._Type)
.setFetchSource(new String[]{"STATUS", "DTCREATED"}, null)
.setQuery(
    QueryBuilders.filteredQuery(QueryBuilders.matchAllQuery(),
    FilterBuilders.rangeFilter("DTCREATED").from(fromDate).to(toDate))).
    addAggregation(AggregationBuilders.terms("STATUS_AGG").field("STATUS")
        .subAggregation(AggregationBuilders.dateHistogram("DATE_AGG").field("DTCREATED").interval(DateHistogram.Interval.DAY).format("yyyy-MM-dd"))
        )
       .addSort("DTCREATED", SortOrder.ASC)
       .get();

I am trying to sort data by DTCREATED field which contains both date and time but query does not provide sorted result. I can't find what I am missing in query. Any help ?

[dadoonet (David Pilato)]

May be share some data sample or the resultset?

[aniketpatil (Aniket)]

Mapping is as below:

{
  "transactioninfo" : {
    "mappings" : {
      "doc" : {
        "properties" : {
            "DTCREATED" : {
            "type" : "date",
            "format" : "dateOptionalTime"
          },
            "STATUS" : {
            "type" : "string"
          },
       }
    }
}

Resultset:

 "aggregations" : {
    "group_by_STATUS" : {
      "doc_count_error_upper_bound" : 0,
      "sum_other_doc_count" : 6,
      "buckets" : [ {
        "key" : "success",
        "doc_count" : 11148,
        "group_by_DATE" : {
          "buckets" : [ {
            "key_as_string" : "2016-09-26",
            "key" : 1474848000000,
            "doc_count" : 6,
            "grades_count" : {
              "value" : 6
            }
          }, {
            "key_as_string" : "2016-09-27",
            "key" : 1474934400000,
            "doc_count" : 19,
            "grades_count" : {
              "value" : 19
            }
   }

Data format:

"STATUS":"SUCCESS"
"DTCREATED":"2016-03-02T08:07:05.000Z"

Response object contains sorted data but when rendering graph in nvd3 x axis dates are not rendered in sorted order

[dadoonet (David Pilato)]

You set the order on the query not on the agg itself.

I guess that if you look at hits, they are correctly sorted.

That being said, date histogram already produces ASC sorted result.

https://github.com/elastic/elasticsearch/blob/5.x/core/src/main/java/org/elasticsearch/search/aggregations/bucket/histogram/DateHistogramAggregationBuilder.java#L80-L80

As far as I can see your resultset, it comes in the correct order, no?