Sql query on a field with special characters

OpenSearch
1

Hello, I am trying to query table in Query Workbench with this statement :

select log, @timestamp, msg from logs-json-2023.05.30 where log LIKE "%14934330815f47c7b91e4a43418d44ca/|23%" ESCAPE "/"

which contains a special character ‘|’. But I am getting an error : Bad Request, this query is not runnable.
The string I am quering is '14934330815f47c7b91e4a43418d44ca|23'

Any idea on how to build a proper query ? Also query with email address doesnt work due to character ‘@’.

Thank you for any information, Radim

2

Hey @rado877

Have you tried

SELECT log, @timestamp, msg FROM logs-json-2023.05.30 where log='%14934330815f47c7b91e4a43418d44ca/|23%';
3

Hi @rado877

Could you run in Devtools the command below and share the output?

GET logs-json-2023.05.30/_mapping

4

Hi, yes, I tried, but it didnt help.

5

it is pretty long but I am searching based on these two fields :slight_smile:

        },
        "level" : {
          "type" : "long"
        },
        "log" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "msg" : {
          "type" : "text",
          "fields" : {
            "keyword" : {
              "type" : "keyword",
              "ignore_above" : 256
            }
          }
        },
        "pid" : {
          "type" : "long"
        },
6

@rado877 Please try to run the query below:
select log, @timestamp, msg from logs-json-2023.05.30 where log.keyword LIKE '%14934330815f47c7b91e4a43418d44ca|23%';

7

Keyword type is better for exact string searches.

8

unfortunately ‘log.keyword’ didn’t help. I
Even with log.keyword LIKE ‘%7f372553c6f448a196ef57991bce5834%’ (no character ‘|’ was used) the result is 0

with log LIKE ‘%7f372553c6f448a196ef57991bce5834%’, I got the result > 0

9

Which versions of OpenSearch and OpenSearch Dashboards do you use?

Could you run in DevTools the commands below?

POST _plugins/_sql/_explain
{
  "query": "select log, @timestamp, msg from logs-json-2023.05.30 where log LIKE '%14934330815f47c7b91e4a43418d44ca|23%';"
}

GET logs-json-2023.05.30/_search
{
"size": 1,
"fields": [
    "log",
    "log.keyword",
    "@timestamp",
    "msg",
    "msg.keyword"
  ]
}
10

We use OpenSearch verion v 1.3.8