Like operator not working when trying to fetch stringify array of objects or object

[rasyhad]

Have tried to search for similar issue, but could not find any.

Basically, I am trying to use like operator in loopback4 filter for columns that store stringified array of objects or just an object.

Because array of objects and object are stored as a string, you would normally use the like operator to find value in the string.

I am not sure if its a bug, can someone please help me on this?

Also, I am not sure if this happens on other SQL database as well.

Steps to reproduce

Here are the steps to reproduce.

1. Create a Loopback 4 Model as following or any but have one field with property array of objects.

@model({settings: {'strict': true}})
export class LocationLog extends Entity {

    @property({
        type: 'string',
        id: true,
        defaultFn: 'guid'
    })
    id?: string


    @property.array(Object, {
        mssql: {
            dataType: 'nvarchar',
            dataLength: 'max'
        }

    })
    sentTo?: Object[]

    constructor(data?: Partial<Approval>) {
        super(data)
    }
}

2. Fetch the data using Loopback 4 filter like so.

const filter = {
      
    where: {
         sentTo: {  like: '&anystringyouwanttofindusinglikeoperator&'  }
   }
}

// use the find method to fetch data
this.find(filter)

Basically, only two steps, of course you definitely need the controller and repository created as well, I won't be covering that.
Use loopback-cli to quickly generate the repository and controller for the model.

Current Behavior

When you try to fetch the data through .find() with the filter above, nothing will be returned even though there are data in the database.

I tried to track the problem and I found that in the file loopback-connector-mssql/lib/mssql.js, line 509 where the codes try to build the expression related to like operator.

If you console.log the operatorValue, you will find the the value has double quotations. Like so.

MsSQL.prototype.buildExpression = function(columnName, operator, operatorValue,
  propertyDefinition) {
  
  // console.log the parameters
 console.log(columnName, 'columnName')
console.log(operator, 'operator')
console.log(operatorValue, 'operatorValue')
console.log(propertyDefinition, 'propertyDefinition')

  switch (operator) {
    case 'like':
      return new ParameterizedSQL(columnName + " LIKE ? ESCAPE '\\'",
        [operatorValue]);
    case 'nlike':
      return new ParameterizedSQL(columnName + " NOT LIKE ? ESCAPE '\\'",
        [operatorValue]);
    case 'regexp':
      g.warn('{{Microsoft SQL Server}} does not support the regular ' +
          'expression operator');
    default:
      // invoke the base implementation of `buildExpression`
      return this.invokeSuper('buildExpression', columnName, operator,
        operatorValue, propertyDefinition);
  }
};

//result of console.log

[sentTo] columnName
like operator
"&anystringyouwanttofindusinglikeoperator&" operatorValue
{ ... } propertyDefinition

The operatorValue has double quotation which then will just be treated as a string in the like statement.

Because I am using MSSQL database, I understand that an array of objects or object will be stringified before being stored in the database.

Expected Behavior

The operatorValue should not have a double quotation.
It should be something like this.

[sentTo] columnName
like operator
&anystringyouwanttofindusinglikeoperator& operatorValue
{ ... } propertyDefinition

Additional information

Please note this will only happen if you have a model with field that is an array of objects or if that field is an object itself.
If you have an array of string, it works just fine.

I guess its probably because of how the code treats object which will always get stringified.

To fix this problem for my application, I modified the code like so,

MsSQL.prototype.buildExpression = function(columnName, operator, operatorValue,
  propertyDefinition) {

  // check for propertyDefinition first, only checks for object
  if(propertyDefinition.type.name === 'Object' || typeof(propertyDefinition.type) === 'object'){
    
    // only if operator is `like`
    if(operator === 'like'){

      // check if double quotation is present as the first and last char
      if(operatorValue[0] === '"' && operatorValue[operatorValue.length - 1] === '"'){
        // remove the double quotations
        operatorValue = operatorValue.slice(1, -1)
      }
    }
  }

  switch (operator) {
    case 'like':
      return new ParameterizedSQL(columnName + " LIKE ? ESCAPE '\\'",
        [operatorValue]);
    case 'nlike':
      return new ParameterizedSQL(columnName + " NOT LIKE ? ESCAPE '\\'",
        [operatorValue]);
    case 'regexp':
      g.warn('{{Microsoft SQL Server}} does not support the regular ' +
          'expression operator');
    default:
      // invoke the base implementation of `buildExpression`
      return this.invokeSuper('buildExpression', columnName, operator,
        operatorValue, propertyDefinition);
  }
};

The above code checks if the column is an object and remove the double quotations if present

node -e 'console.log(process.platform, process.arch, process.versions.node)';

win32 x64 16.14.2