Why am I getting NaN from an elasticsearch aggregate query?

Question

In the query below, occasionally I receive a "NaN" response (see the response below the query).

I'm assuming that, occasionally, some invalid data gets in to the "amount" field (the one being aggregated). If that is a valid assumption, how can I find those documents with the invalid "amount" fields so I can troubleshoot them?

If that's not a valid assumption, how do I troubleshoot the occasional "NaN" value being returned?

REQUEST:

POST /_msearch
{
  "search_type": "query_then_fetch",
  "ignore_unavailable": true,
  "index": [
    "view-2017-10-22",
    "view-2017-10-23"
  ]
}
{
  "size": 0,
  "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "handling-time": {
              "gte": "1508706273585",
              "lte": "1508792673586",
              "format": "epoch_millis"
            }
          }
        },
        {
          "query_string": {
            "analyze_wildcard": true,
            "query": "+page:\"checkout order confirmation\" +pageType:\"d\""
          }
        }
      ]
    }
  },
  "aggs": {
    "2": {
      "date_histogram": {
        "interval": "1h",
        "field": "time",
        "min_doc_count": 0,
        "extended_bounds": {
          "min": "1508706273585",
          "max": "1508792673586"
        },
        "format": "epoch_millis"
      },
      "aggs": {
        "1": {
          "sum": {
            "field": "amount"
          }
        }
      }
    }
  }
}

RESPONSE:

{
  "responses": [
    {
      "took": 12,
      "timed_out": false,
      "_shards": {
        "total": 10,
        "successful": 10,
        "failed": 0
      },
      "hits": {
        "total": 44587,
        "max_score": 0,
        "hits": []
      },
      "aggregations": {
        "2": {
          "buckets": [
            {
              "1": {
                "value": "NaN"
              },
              "key_as_string": "1508706000000",
              "key": 1508706000000,
              "doc_count": 2915
            },
            {
              "1": {
                "value": 300203.74
              },
              "key_as_string": "1508709600000",
              "key": 1508709600000,
              "doc_count": 2851
            },
            {
              "1": {
                "value": 348139.5600000001
              },
              "key_as_string": "1508713200000",
              "key": 1508713200000,
              "doc_count": 3197
            },
            {
              "1": {
                "value": "NaN"
              },
              "key_as_string": "1508716800000",
              "key": 1508716800000,
              "doc_count": 3449
            },
            {
              "1": {
                "value": "NaN"
              },
              "key_as_string": "1508720400000",
              "key": 1508720400000,
              "doc_count": 3482
            },
            {
              "1": {
                "value": 364449.60999999987
              },
              "key_as_string": "1508724000000",
              "key": 1508724000000,
              "doc_count": 3103
            },
            {
              "1": {
                "value": 334914.68
              },
              "key_as_string": "1508727600000",
              "key": 1508727600000,
              "doc_count": 2722
            },
            {
              "1": {
                "value": 315368.09000000014
              },
              "key_as_string": "1508731200000",
              "key": 1508731200000,
              "doc_count": 2161
            },
            {
              "1": {
                "value": 102244.34
              },
              "key_as_string": "1508734800000",
              "key": 1508734800000,
              "doc_count": 742
            },
            {
              "1": {
                "value": 37178.63
              },
              "key_as_string": "1508738400000",
              "key": 1508738400000,
              "doc_count": 333
            },
            {
              "1": {
                "value": 25345.68
              },
              "key_as_string": "1508742000000",
              "key": 1508742000000,
              "doc_count": 233
            },
            {
              "1": {
                "value": 85454.47000000002
              },
              "key_as_string": "1508745600000",
              "key": 1508745600000,
              "doc_count": 477
            },
            {
              "1": {
                "value": 24102.719999999994
              },
              "key_as_string": "1508749200000",
              "key": 1508749200000,
              "doc_count": 195
            },
            {
              "1": {
                "value": 23352.309999999994
              },
              "key_as_string": "1508752800000",
              "key": 1508752800000,
              "doc_count": 294
            },
            {
              "1": {
                "value": 44353.409999999996
              },
              "key_as_string": "1508756400000",
              "key": 1508756400000,
              "doc_count": 450
            },
            {
              "1": {
                "value": 80129.89999999998
              },
              "key_as_string": "1508760000000",
              "key": 1508760000000,
              "doc_count": 867
            },
            {
              "1": {
                "value": 122797.11
              },
              "key_as_string": "1508763600000",
              "key": 1508763600000,
              "doc_count": 1330
            },
            {
              "1": {
                "value": 157442.29000000004
              },
              "key_as_string": "1508767200000",
              "key": 1508767200000,
              "doc_count": 1872
            },
            {
              "1": {
                "value": 198831.71
              },
              "key_as_string": "1508770800000",
              "key": 1508770800000,
              "doc_count": 2251
            },
            {
              "1": {
                "value": 218384.08000000002
              },
              "key_as_string": "1508774400000",
              "key": 1508774400000,
              "doc_count": 2305
            },
            {
              "1": {
                "value": 229829.22000000006
              },
              "key_as_string": "1508778000000",
              "key": 1508778000000,
              "doc_count": 2381
            },
            {
              "1": {
                "value": 217157.56000000006
              },
              "key_as_string": "1508781600000",
              "key": 1508781600000,
              "doc_count": 2433
            },
            {
              "1": {
                "value": 208877.13
              },
              "key_as_string": "1508785200000",
              "key": 1508785200000,
              "doc_count": 2223
            },
            {
              "1": {
                "value": "NaN"
              },
              "key_as_string": "1508788800000",
              "key": 1508788800000,
              "doc_count": 2166
            },
            {
              "1": {
                "value": 18268.14
              },
              "key_as_string": "1508792400000",
              "key": 1508792400000,
              "doc_count": 155
            }
          ]
        }
      },
      "status": 200
    }
  ]
}

Could you show your mapping? GET /_msearch/_mapping – Miek Oct 23 '17 at 21:54 — Miek, Oct 23 '17 at 21:54

score 1 · Accepted Answer · answered Oct 23 '17 at 22:19

1

You can do a search for <fieldName>:NaN (on numeric fields) to find numbers that are set to NaN.

Obviously, once you find those, you can either fix the root cause of the field being set to NaN, or you can exclude those records from the aggregation by adding a -<fieldName>:NaN to the query.

(It turns out that the input was feeding in some garbage characters once in every few million documents.)

answered Oct 23 '17 at 22:19

Jared

25,520
24
79
114

To exclude a field, you can try this. https://stackoverflow.com/questions/25353564/elasticsearch-exclude-results-with-value – angelokh Nov 25 '21 at 05:50

Why am I getting NaN from an elasticsearch aggregate query?

1 Answers1