Elasticsearch _sql string containing Apostrophe

Elastic Stack Elasticsearch
1

Hi,
I have a query:

GET /test_sql/_search

{
"query": {
"bool": {
"must": [
{
"match": {
"tenant_name.keyword": "KNOXVILLE'S CORPORATION"
}
}
]
}
}
}

The above query works fine.
Now, I try the same thing with _sql:
POST /_sql/translate
{
"query": "SELECT * FROM test_sql WHERE tenant_name.keyword = 'KNOXVILLE'S CORPORATION'"
}
This doesn't work gives out error:
"reason": "Unrecognized character escape ''' (code 39)\n at [Source: org.elasticsearch.transport.netty4.ByteBufStreamInput@6017995f; line: 2, column: 76]"

I tried various combinations of , " around that string value doesn't work (got a parsing exception).

The below query works:

POST _sql?format=txt

{
"query": "SELECT * FROM test_sql",
"filter": {
"match_phrase": {
"tenant_name": "KNOXVILLE'S CORPORATION"
}
}
}

What are my options to not use filter and include something in the query itself. I am asking this since we have around 80 queries and we wont be able to change all the queries with the added filter.

2

You can escape with 2 single quotes:

{
"query": "SELECT * FROM test_sql WHERE tenant_name.keyword = 'KNOXVILLE''S CORPORATION'"
}
3

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.