Operator.LIKE only works if matching from start of field

Question

I am unable to match on text that is after the first character of a JSON field.  For example, if a field has the value "dishwasher", Operator.LIKE will match "dish" but not "wash".  This is the same problem someone else had years ago in this forum, but no answer was provided.

IMPORTANT:  It **works** if I use a search term that matches the front of the field.  It **breaks** with NotFound if I search within the field.

 

Here is an AS3 code snippet:

App42Log.setDebug(true);
var searchTerm:String = "wash";  // "dish" WORKS!!!  All "alias" fields start with "dishwasher"
var query:Query = QueryBuilder.build("alias", searchTerm, Operator.LIKE);
nosqlSvc.findDocumentsByQuery(MASTER_DB_NAME, ACCOUNT_COLLECTION_NAME, query, ...);

 

Here is my data.  It should match, but the findDocumentsByQuery fails:  Note only the "alias" at the root is relevant

{
  "rank": 0,
  "credsPK": "5bf33c2ae4b0d32c003ba900",
  "alias": "dishwasher JYBE9U",
  "accountConfigured": false,
  "login": "dishwasher JYBE9U",
  "battleHistory": {
    "summaries": [
      {
        "winCode": 1,
        "winTs": 0,
        "opEnv": {
          "rank": 0,
          "alias": "dishwasher DAMUM6",
          "pk": "5bf1cc9de4b01d45f6a4b683"
        },
        "winPK": "5bf33c2ae4b0d32c003ba901"
      },
      {
        "winCode": 1,
        "winTs": 0,
        "opEnv": {
          "rank": 0,
          "alias": "dishwasher DAMUM6",
          "pk": "5bf1cc9de4b01d45f6a4b683"
        },
        "winPK": "5bf33c2ae4b0d32c003ba901"
      }
    ]
  },
  "accountPK": ""
}

 

 

Here is the debug log: (I removed the API and Signature log lines)

[trace] Http url : 1.0/storage/findDocsByQuery/dbName/toyl/collectionName/accounts
[trace] Setting value :jsonQuery : [{"key":"alias","value":"wash","operator":"$lk"}]
[trace] QueryString is ?jsonQuery=%5B%7B%22key%22%3A%22alias%22%2C%22value%22%3A%22wash%22%2C%22operator%22%3A%22%24lk%22%7D%5D&
[trace] Requested URL is https://api.shephertz.com/cloud/1.0/storage/findDocsByQuery/dbName/toyl/collectionName/accounts?jsonQuery=%5B%7B%22key%22%3A%22alias%22%2C%22value%22%3A%22wash%22%2C%22operator%22%3A%22%24lk%22%7D%5D&
[trace]  Setting Header value : SDKName : AS3
[trace]  Setting Header value : dataACL : [{"null":null}]
[trace]  Setting Header value : resource : storage
[trace]  Setting Header value : timeStamp : 2018-11-20T20:54:41.83Z
[trace]  Setting Header value : version : 1.0
[trace] Exception message {"app42Fault":{"httpErrorCode":404,"appErrorCode":2608,"message":"Not Found","details":"No document in the collection 'accounts' exists for given query"}}
[trace] HttpErrorCode 404
[trace] AppErrorCode 2608
 

Answer 1

Hi Kustardking,

We have forwarded this query to our team, we will get back to you as soon as possible.

Regards,

Priyanka Singh

App42 Team

Answer 2

Hello kustardking, Yes you are right while searching for the text bin storage service you have to provide the first letter in case of using like operator. And it is only because of case sensitive search is supported in no SQL storage service. Case insitive search and in-between text search is not supported yet. We have recorded your request in our pipeline and in case we get more request on this, will plan accordingly and keep you updated regarding the next process. Regards, Himanshu Sharma

Comments

Hi Himanshu,

Thanks for clarifying.  For me, this is a serious deficiency related to searching for a friend/opponent.  My workaround is likely to be having to store a name in multiple, deconstructed forms, and search on each term separately.  For example, "dishwasher" will be stored something like

"alias0": "dishwasher", "alias1":"ishwasher", "alias2":"shwasher", "alias3":hwasher", "alias4":"washer", etc., and the search keys will have to have to include the whole set of possibilities.  That is, a giant compound query.

Also, these deconstructed versions of the alias and the runtime search keys will have to be lowercased.

Please advise if you know of text search strategies others are using.