Wildcard query on complex text/json column

[salihklc (Salih KILIÇ)]

Hello everyone,

I'm trying to search a text in the Request,ResponseJSon column with wildcard method ,but It's look like search on only first 10 chars. All the other fields works like as excepted but I really dont get the problem or even why this query acting like that.

Example data :

{"_index" : "license",
            "_type" : "JSON_ELASTIC",
            "_id" : "tP8ermwB1p5f4ZMD1Pmr",
            "_score" : 1.0,
            "_source" : {
              "ComputerName" : "MyRealMachine2",
              "ApplicationName" : "LICENSE_HOST.exe",
              "TerminalIP" : "123.123.123.12.435345.345",
              "ListenPort" : "7020",
              "AppVersion" : "1.0.0.0",
              "UniqueId" : "000009_20190820111312",
              "SerialNumber" : "191380004610025",
              "TransactionStartDate" : "2019-08-20T11:13:12.131951+03:00",
              "LogStartDateTime" : "2019-08-20T11:13:12.4444405+03:00",
              "LogEndDateTime" : "2019-08-20T11:13:12.5072718+03:00",
              "LogElapsedTime" : 0,
              "URLCallStartDateTime" : "2019-08-20T11:13:12.4600757+03:00",
              "URLCallEndDateTime" : "2019-08-20T11:13:12.5072718+03:00",
              "URLCallElapsedTime" : 0,
              "WriteElasticSuccess" : true,
              "IsInnerException" : "",
              "ExceptionMessage" : "",
              "ResponseCode" : "00",
              "ResponseMessage" : "ISLEM BASARILI",
              "MachineName" : "MyMachineKgoasdo1asd123",
              "ProcessName" : "LICENSE_HOST",
              "LibraryName" : "Worker",
              "FunctionName" : "RunLicense",
              "Url" : "https://*012.3123.123.132.321/asdasd7asd7asd7as7d/asdasd7asd/as",
              "RequestJSon" : """{"buffer":"494C44000010A00180C580010F3139313338303030343631303032358002008003042019082080040311131180050C34332E30312D76313872393080060180800701019009010780080102A10180808101020134810202747081030944454EDD5A42414E4B8104104431303035304730304C4335303133348105104431303035304730304C433530313334810602FFFF810707000000000000008108070000000000000081090700000000000000810A083030393034373535810B0F353333303030303031343732323933810C0101A00700F0011070778AF13805D9F1220FA5DAB3C5A5F8","endPoint":"86.108.241.118","STX":null,"MessageLength":null,"ProtocolID":"ILD","Version":"0000","MessageType":"10","OwnerBankTeliumID":null,"OwnerBankName":null,"TerminalInfoGroupBuff":"80010F3139313338303030343631303032358002008003042019082080040311131180050C34332E30312D76313872393080060180800701019009010780080102A10180808101020134810202747081030944454EDD5A42414E4B8104104431303035304730304C4335303133348105104431303035304730304C433530313334810602FFFF810707000000000000008108070000000000000081090700000000000000810A083030393034373535810B0F353333303030303031343732323933810C0101","TerminalInfoGroup":{"SerialNumber":"191380004610025","TerminalProductCode":"","TerminalDate":"20190820","TerminalTime":"111311","IdleVersion":"43.01-v18r90","BankAppBlockBuff":"8101020134810202747081030944454EDD5A42414E4B8104104431303035304730304C4335303133348105104431303035304730304C433530313334810602FFFF810707000000000000008108070000000000000081090700000000000000810A083030393034373535810B0F353333303030303031343732323933810C0101","TerminalProfile":"?","TerminalLicenseType":"01","TerminalLicenseRequestType":"02","LoadReason":"","BankAppBlockList":[{"BkmId":"0134","TeliumAppID":"7470","BankName":"DEnemeGbank","BankVersiyon":"D10050G00LC50134","IngenicoVersion":"D10050G00LC50134","Status":"FFFF","FirstParameterDate":"00000000000000","LastParameterDate":"00000000000000","LastTransactionDate":"00000000000000","TerminalNumber":"00904755","MerchantNumber":"533000001472293","LicenseStatus":"01","LicenseEndDate":""}]},"OTPInfoGroupBuff":null,"OTPInfoGroup":{"TechnicianPhoneNumber":null},"ECRInfoGroupBuff":"","ECRInfoGroup":{"OKCFirm":null,"OKCModel":null,"OKCVersion":null,"OKCRegisterNumber":null,"DllVersion":null,"LicenseFirmName":null,"LicenseHASH":null,"AsciiOTP":null,"LoadReason":null,"OKCSerialNo":null},"TetraLicenseInfoGroupBuff":"","TetraLicenseInfoGroup":{"TetraLicenseOTPBuff":null,"ReasonForAppLicenseDownload":null,"TetraLicenseOTP":null},"PackageSign":"70778AF13805D9F1220FA5DAB3C5A5F8","ETX":"03","CRC":"7C65","ResultCode":"00","ResultMessage":"ISLEM BASARILI"}""",
              "LicenseRequestType" : 2,
              "LicenseRequestTypeDesc" : "Uygulama Lisans İsteği",
              "ImpProResponseCode" : "",
              "ImpProResponseMessage" : "",
              "AppResponseCode" : "00",
              "AppResponseMessage" : "ISLEM BASARILI"
            }
          }

and first query (the working one because query value occurs in first 10 chars (dont know why this is works but whatever ):

GET license/_search
{
  "query": {
    "wildcard": {
      "RequestJSon":"*buff*"
    }
  }
}

And the second one (the not working query ):

   GET license/_search
    {
      "query": {
        "wildcard": {
          "RequestJSon":"*C5A5F8*"
        }
      }
    }

Normal wildcard working on other fields:

 GET license/_search
        {
          "query": {
            "wildcard": {
              "UniqueId":"*1113*"
            }
          }
        }

Thanks for any ideas

[dadoonet (David Pilato)]

It depends on the analyzer (mapping) you used for this field.

Have a look at the analyze API to understand how your string has been actually indexed. Define a keyword analyzer on this field.
Otherwise use may be c5a5f8 but may be the text has been indexed as c, 5, a, ..

[Christian_Dahlqvist]

Be aware that even if you change the analyzer to keyword, which basically treats the whole content as a single string/token, there is a limit to how long a token can be (32k characters). This way of searching is also the most inefficient way of searching in Elasticsearch and will scale badly.

[dadoonet (David Pilato)]

Thanks for adding this information @Christian_Dahlqvist. I totally forgot to mention: "Do no use the wildcard query!"

[salihklc (Salih KILIÇ)]

Thanks for fast response and advice,

I knew that was a bad practice to use wildcard but I'm curious about reason, now I will index that fields as json nested field, will it be better for mapping and performans

Thank you so much