Kibana FIlter on String Value

Question

I have large amount of transactions in Kibana. There is 1 field (timetaken) which indicates time taken by the transaction to complete. The issue is time taken is in string form. When I use >15000; it also shows fields less than 15000.

I want to get all transactions greater than 15000 (milliseconds).

Answer 1

PUT test_time/_doc/1
{"timetaken":"15000"}

PUT test_time/_doc/2
{"timetaken":"1000"}

GET test_time/_search
{
  "query": {
    "range": {
      "timetaken": {
        "lte": "15000"
      }
    }
  }
}

---

-- copy the current mapping and add copy_to and new field with type.

GET test_time

-- update the mapping and add new field.

PUT test_time/_mapping
{
  "properties": {
    "timetaken": {
      "type": "text",
      "copy_to": "timetaken_integer",
      "fields": {
        "keyword": {
          "type": "keyword",
          "ignore_above": 256
        }
      }
    },
    "timetaken_integer": {
      "type": "long"
    }
  }
}

-after put the mapping the new data become available on timetaken_integer field.

PUT test_time/_doc/3
{"timetaken":"1000"}

-use the new field timetaken_integer to search your data.

GET test_time/_search
{
  "query": {
    "range": {
      "timetaken_integer": {
        "lt": "15000"
      }
    }
  }
}

-update existing data

POST test_time/_update_by_query

Answer 2

Alternative approach to Musab's solution without changing or update the mapping is to use script:

{
  "query": {
    "bool": {
      "must": {
        "script": {
          "script": {
            "inline": "Integer.parseInt(doc['timetaken.keyword'].value) >= 15000",
            "lang": "painless"
          }
        }
      }
    }
  }
}