Hi,
we have the following error: "ERROR: 42883: operator does not exist: jsonb ~~ character varying" when we try to use Like on JsonB on PostgreSQL.
We have filter similar to this:
...
bucket.PredicateExpression.Add(MyFields.Data % String.Format("%{0}%", filter.SearchPhrase));
...
"Details": "An exception was caught during the execution of a retrieval query: ERROR: 42883: operator does not exist: jsonb ~~ character varying. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception.",
"Error": {
"_queryExecuted": "\r\n\tQuery: SELECT COUNT(\"public\".\"Item\".\"Id\") AS \"Id\" FROM \"public\".\"Item\" WHERE ( \"public\".\"Item\".\"SectionName\" = :p1 AND \"public\".\"Item\".\"Data\" LIKE :p2) LIMIT 1\r\n\tParameter: :p1 : String. Length: 255. Precision: 0. Scale: 0. Direction: Input. Value: \"projects\".\r\n\tParameter: :p2 : String. Length: 3. Precision: 0. Scale: 0. Direction: Input. Value: \"%t%\".\r\n",
"_exceptionInfo": {
"ErrorCode": -2147467259,
"Message": "ERROR: 42883: operator does not exist: jsonb ~~ character varying",
"HelpLink": null,
"ErrorObjects": [
...
"ErrorSql": "SELECT COUNT(\"public\".\"Item\".\"Id\") AS \"Id\" FROM \"public\".\"Item\" WHERE ( \"public\".\"Item\".\"SectionName\" = (('projects')::varchar(255)) AND \"public\".\"Item\".\"Data\" LIKE (('%t%')::text)) LIMIT 1"
It seems to me that issue is related to the following line not being cast to "::text"
"public\".\"Item\".\"Data\" LIKE (('%t%')::text)
Do you have any idea why this is not cast to "text" once we use like on this field? We have same functionality in EF and it's working properly.
We are using LLBLGen 4.2, Low-Level API
Thanks