Elasticsearch wildcard query on numeric fields without using mapping

Question

I'm looking for a creative solution because I can't use mapping as solution is already in production. I have this query:

            {
              "size": 4,
              "query": {
                "bool": {
                  "filter": [
                    {
                      "range": {
                        "time": {
                          "from": 1597249812405,
                          "to": null,
                        }
                      }
                    },
                    {
                      "query_string": {
                        "query": "*181*",
                        "fields": [
                          "deId^1.0",
                          "deTag^1.0",
                        ],
                        "type": "best_fields",
                        "default_operator": "or",
                        "max_determinized_states": 10000,
                        "enable_position_increments": true,
                        "fuzziness": "AUTO",
                        "fuzzy_prefix_length": 0,
                        "fuzzy_max_expansions": 50,
                        "phrase_slop": 0,
                        "escape": false,
                        "auto_generate_synonyms_phrase_query": true,
                        "fuzzy_transpositions": true,
                        "boost": 1
                      }
                    }
                  ],
                  "adjust_pure_negative": true,
                  "boost": 1
                }
              },
              "sort": [
                {
                  "time": {
                    "order": "asc"
                  }
                }
              ]
            }

"deId" field is an integer in elasticsearch and the query returns nothing (though should), Is there a solution to search for wildcards in numeric fields without using the multi field option which requires mapping?

Joe - Elasticsearch Handbook Joe - Elasticsearch Handbook · Accepted Answer · 2020-08-12T20:11:52

Once you index an integer, ES does not treat the individual digits as position-sensitive tokens. In other words, it's not directly possible to perform wildcards on numeric datatypes.

There are some sub-optimal ways of solving this (think scripting & String.substring) but the easiest would be to convert those integers to strings.

Let's look at an example deId of 123181994:

POST prod/_doc
{
  "deId_str": "123181994"
}

then

GET prod/_search
{
  "query": {
    "bool": {
      "filter": [
        {
          "query_string": {
            "query": "*181*",
            "fields": [
              "deId_str"
            ]
          }
        }
      ]
    }
  }
}

works like a charm.

Since your index/mapping is already in production, look into _update_by_query and stringify all the necessary numbers in a single call. After that, if you don't want to (and/or cannot) pass the strings at index time, use ingest pipelines to do the conversion for you.

Elasticsearch wildcard query on numeric fields without using mapping

1 Answers