ElasticSearch: how to perform search as MSSQL "LIKE word% with tokenized string?

Question

Currently we are performing full text search within MSSQL with query:

select * from contract where number like 'word%'

the problem is that contract number may be like

АА-1641471

TST-100069

П-5112-90-00230

001-1000017

1617/292/000001

and ES split all this into tokens.

How to configure ES not to split all this contract numbers into tokens and perform same search like SQL query above ?

the closest solution i've found is to perform query like this:

{
  "size": 10,
  "query": {
    "regexp": {
      "contractNumber": {
        "value": ".*п-11.*"
      }
    }
  }
}

this solution work same as MSSQL LIKE 'word%' with value like 1111,2568 etc, but fails with п-11

Answer 1

One option could be to use the wildcard query which can perform any type of wildcard combination i.e %val%, %val or val%

{
    "query": {
        "wildcard" : { "contractNumber" : "*11" }
    }
}

NOTE: It's not recommended to start with a wildcard in the search. Could be extremely slow

To make this work with string values to prevent them from being tokenized, you need to update your index and tell the analyser to stay away. One way of doing that is to define the property as type keyword instead of text

PUT /_template/template_1
{
    "index_patterns" : ["your_index*"],
    "order" : 0,
    "settings" : {
        "number_of_shards" : 1
    },
    "mappings" : {
        "your_document_type" : {
            "properties" : {
                "contractNumber" : {
                    "type" : "keyword"
                }
        }
    }
}

NOTE: replace your_index with your index name and your_document_type with the document type.

When the mapping is added, delete the current index and recreate it, then it will use the template for properties and your contractNumber will be indexed as a keyword