Sorting percentiles aggregation with NaN values

Question

I'm using ElasticSearch 2.3.3 and I have the following aggregation:

"aggregations": {
        "mainBreakdown": {
            "terms": {
                "field": "location_i",
                "size": 10,
                "order": [
                    {
                        "comments>medianTime.50": "asc"
                    }
                ]
            },
            "aggregations": {
                "comments": {
                    "filter": {
                        "term": {
                            "type_i": 120
                        }
                    },
                    "aggregations": {
                        "medianTime": {
                            "percentiles": {
                                "field": "time_l",
                                "percents": [
                                    50.0
                                ]
                            }
                        }
                    }
                }
            }
        }
    }

for better understanding I've added to field names a postfix which tells the field mapping:

_i = integer
_l = long (timestamp)

And aggregation response is:

"aggregations": {
    "mainBreakdown": {
      "doc_count_error_upper_bound": 0,
      "sum_other_doc_count": 0,
      "buckets": [
        {
          "key": 100,
          "doc_count": 2,
          "comments": {
            "doc_count": 1,
            "medianTime": {
              "values": {
                "50.0": 20113
              }
            }
          }
        },
        {
          "key": 121,
          "doc_count": 14,
          "comments": {
            "doc_count": 0,
            "medianTime": {
              "values": {
                "50.0": "NaN"
              }
            }
          }
        }
      ]
    }
}

My problem is that the medianTime aggregation, sometimes has value of NaN because the parent aggregation comments has 0 matched documents, and then the result with the NaN will always be last on both "asc" and "desc" order.
I've tried adding "missing": 0 inside percentiles aggregation but it still returns a NaN.

Can you please help me sorting my buckets by medianTime that and when it's "asc" ordering the NaN values will be first and when its "desc" they will be last?

ItayD ItayD · Accepted Answer · 2019-01-07T08:09:42

NaN's are not numbers, so they will always be last.
After a short discussion on elasticsearch github, we decided its the appropriate way to handle NaN's.
https://github.com/elastic/elasticsearch/issues/36402

Sorting percentiles aggregation with NaN values

1 Answers