# Filter by comparing two number fields fails with number\_format\_exception

**URL:** <https://discuss.elastic.co/t/filter-by-comparing-two-number-fields-fails-with-number-format-exception/222250>\
**Category:** Kibana\
**Created:** [March 5, 2020, 9:48am UTC](https://discuss.elastic.co/t/filter-by-comparing-two-number-fields-fails-with-number-format-exception/222250 "2020-03-05T09:48:11Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![ismarslomic](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ismarslomic/32/63870_2.png) [@ismarslomic](https://discuss.elastic.co/u/ismarslomic)\
**Post date:** [March 5, 2020, 9:48am UTC](https://discuss.elastic.co/t/filter-by-comparing-two-number-fields-fails-with-number-format-exception/222250/1 "2020-03-05T09:48:11Z")

</div>

I would like to filter documents in Kibana Visualize where `msg.count_passenger_out > msg.count_passenger_in`. Both fields are _number_ fields and are indexed.

Im running Kibana + Elasticsearch v7.6.0 at Elastic Cloud.

This query work perfectly `msg.count_passenger_out > 10`, however I get an ugly, unformatted exception In Kibana when I try executing `msg.count_passenger_out > msg.count_passenger_in` in Kibana:

```
{
  "error": {
    "root_cause": [
      {
        "type": "query_shard_exception",
        "reason": "failed to create query: {\n \"bool\" : {\n \"filter\" : [\n {\n \"bool\" : {\n \"should\" : [\n {\n \"range\" : {\n \"msg.count_passenger_out\" : {\n \"from\" : \"msg.count_passenger_in\",\n \"to\" : null,\n \"include_lower\" : false,\n \"include_upper\" : true,\n \"boost\" : 1.0\n }\n }\n }\n],\n \"adjust_pure_negative\" : true,\n \"minimum_should_match\" : \"1\",\n \"boost\" : 1.0\n }\n },\n {\n \"bool\" : {\n \"should\" : [\n {\n \"range\" : {\n \"msg.count_passenger_out\" : {\n \"from\" : \"msg.count_passenger_in\",\n \"to\" : null,\n \"include_lower\" : false,\n \"include_upper\" : true,\n \"boost\" : 1.0\n }\n }\n }\n],\n \"adjust_pure_negative\" : true,\n \"minimum_should_match\" : \"1\",\n \"boost\" : 1.0\n }\n },\n {\n \"range\" : {\n \"msg.@timestamp\" : {\n \"from\" : null,\n \"to\" : null,\n \"include_lower\" : true,\n \"include_upper\" : true,\n \"boost\" : 1.0\n }\n }\n }\n ],\n \"adjust_pure_negative\" : true,\n \"boost\" : 1.0\n }\n}",
        "index_uuid": "YFS2X_8JQMOVzygXTQPjMQ",
        "index": "logs-2020.03.05"
      }
    ],
    "type": "search_phase_execution_exception",
    "reason": "all shards failed",
    "phase": "query",
    "grouped": true,
    "failed_shards": [
      {
        "shard": 0,
        "index": "logs-2020.03.05",
        "node": "gTASgYfOSg2I4NRpAe3fiw",
        "reason": {
          "type": "query_shard_exception",
          "reason": "failed to create query: {\n \"bool\" : {\n \"filter\" : [\n {\n \"bool\" : {\n \"should\" : [\n {\n \"range\" : {\n \"msg.count_passenger_out\" : {\n \"from\" : \"msg.count_passenger_in\",\n \"to\" : null,\n \"include_lower\" : false,\n \"include_upper\" : true,\n \"boost\" : 1.0\n }\n }\n }\n],\n \"adjust_pure_negative\" : true,\n \"minimum_should_match\" : \"1\",\n \"boost\" : 1.0\n }\n },\n {\n \"bool\" : {\n \"should\" : [\n {\n \"range\" : {\n \"msg.count_passenger_out\" : {\n \"from\" : \"msg.count_passenger_in\",\n \"to\" : null,\n \"include_lower\" : false,\n \"include_upper\" : true,\n \"boost\" : 1.0\n }\n }\n }\n],\n \"adjust_pure_negative\" : true,\n \"minimum_should_match\" : \"1\",\n \"boost\" : 1.0\n }\n },\n {\n \"range\" : {\n \"msg.@timestamp\" : {\n \"from\" : null,\n \"to\" : null,\n \"include_lower\" : true,\n \"include_upper\" : true,\n \"boost\" : 1.0\n }\n }\n }\n ],\n \"adjust_pure_negative\" : true,\n \"boost\" : 1.0\n }\n}",
          "index_uuid": "YFS2X_8JQMOVzygXTQPjMQ",
          "index": "logs-2020.03.05",
          "caused_by": {
            "type": "number_format_exception",
            "reason": "For input string: \"msg.count_passenger_in\""
          }
        }
      }
    ]
  },
  "status": 400
}
```

---

<div class="post-metadata">

**Author:** ![flash1293](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/flash1293/32/41227_2.png) [@flash1293](https://discuss.elastic.co/u/flash1293)\
**Post date:** [March 5, 2020, 10:35am UTC](https://discuss.elastic.co/t/filter-by-comparing-two-number-fields-fails-with-number-format-exception/222250/2 "2020-03-05T10:35:58Z")

</div>

Filters in Kibana are not able to do that out of the box, they can only filter by constant values.

To work around this issue, you can create a [scripted field](https://www.elastic.co/guide/en/kibana/current/scripted-fields.html) in your index pattern doing the comparison and being either `true` or `false` and then filter on that field in your discover view.

The downside of this approach is that performance might be impacted because Elasticsearch has to look up each single document to check whether the condition matches and can't rely on pre-built inverted indices. To get maximum performance, it makes sense to do the comparison before ingesting the data to elasticsearch and saving it in a separate field.

---

<div class="post-metadata">

**Author:** ![ismarslomic](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ismarslomic/32/63870_2.png) [@ismarslomic](https://discuss.elastic.co/u/ismarslomic)\
**Post date:** [March 6, 2020, 9:59am UTC](https://discuss.elastic.co/t/filter-by-comparing-two-number-fields-fails-with-number-format-exception/222250/3 "2020-03-06T09:59:47Z")

</div>

Thanks for quick reply, @flash1293! Do you know if this limitation is by design (and the reason for it) or is it a missing feature? I have previously experience with Splunk and this was possible for many years.

---

<div class="post-metadata">

**Author:** ![flash1293](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/flash1293/32/41227_2.png) [@flash1293](https://discuss.elastic.co/u/flash1293)\
**Post date:** [March 6, 2020, 10:53am UTC](https://discuss.elastic.co/t/filter-by-comparing-two-number-fields-fails-with-number-format-exception/222250/4 "2020-03-06T10:53:04Z")

</div>

You will get more context in the Elasticsearch forum if you are interested in details but AFAIK that’s by design. Queries are meant to be fast, even on vast amounts of data. If the thing you are searching for is a static value, Elasticsearch can pre-compute an inverted index for that value and searching will be super fast because Elasticsearch just has to check in a single place to see which documents match.

If you are specifying this comparison of two fields in the same document on the fly, Elasticsearch has to go through every single document and compare the values to figure out whether they match the condition - much slower on large amounts of data. It’s possible (by using scripted fields) but discouraged.

---

<div class="post-metadata">

**Author:** ![ismarslomic](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/ismarslomic/32/63870_2.png) [@ismarslomic](https://discuss.elastic.co/u/ismarslomic)\
**Post date:** [March 6, 2020, 10:58am UTC](https://discuss.elastic.co/t/filter-by-comparing-two-number-fields-fails-with-number-format-exception/222250/5 "2020-03-06T10:58:53Z")

</div>

Thanks, that explanation was quite useful!

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [April 3, 2020, 10:58am UTC](https://discuss.elastic.co/t/filter-by-comparing-two-number-fields-fails-with-number-format-exception/222250/6 "2020-04-03T10:58:58Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.