Replacement of RLIKE operator in Opensearch

OpenSearch
, ,
1

Versions (relevant - OpenSearch/Dashboard/Server OS/Browser):

OpenSearch 2.4.1

Describe the issue:
We are migrating from ElasticSearch-7.X to Opensearch-2.4.1 and we were using RLIKE operator in SQL query for RegularExpression matching.
But looks now RLIKE operator has been dropped from OpenSearch and OpenSearch Application is throwing Error while running SQL query with RLIKE operator.

Do we have RLIKE operator support in SQL query, or we can use any other replacement operator to be used?

URL: http://localhost:9200/_plugins/_sql
Method: POST
Payload:
{

"query": "SELECT identifier FROM `2` WHERE companyName = 'test1' AND containerName = 'car_catalog' AND ((`attributes.car_specs.item_name`  RLIKE '[a-z]')) LIMIT 10000 "

}

Response from OpenSearch API:

{
“error”: {
“reason”: “Invalid SQL query”,
“details”: “Unsupported operation: RLIKE”,
“type”: “SqlParseException”
},
“status”: 400
}
Configuration:

Relevant Logs or Screenshots:

2

@anirudha - do you know if RLIKE was dropped? Can you or someone on the team assist @bimlesh_singh on this? thanks

3

Hi @anirudha /@kris , Any information on this issue. Thanks in advance!

4

@bimlesh_singh OpenSearch SQL doesn’t support RLIKE. Were you using Elasticsearch SQL plugin? I think REGEXP operator maybe the equivalent one: sql/expressions.rst at main · opensearch-project/sql · GitHub

1 Like
5

Hi @daichen , We had tried with REGEX operator as below. but opensearch query is not working.
the attributes.cat_specs.item_description is of string type
Could you suggest any change.

Request payload:
{ “query”: “SELECT count(*) FROM `66` WHERE companyName = ‘devsanity’ AND containerName = ‘catalog’ AND ((`attributes.cat_specs.item_description` REGEXP ‘[a-z]’))”
}
Response body:
{
“error”: {
“reason”: “Invalid SQL query”,
“details”: “and function expected {[BOOLEAN,BOOLEAN]}, but get [BOOLEAN,INTEGER]”,
“type”: “ExpressionEvaluationException”
},
“status”: 400
}

6

Hi , Any suggestion on this issue. Thanks in advance.

7

@daichen , Any suggestion on this issue. Please let me know if any test data needed. Thanks in advance!

8

Hi @daichen ,Do we have any update on RLIKE operator support