Problem with SQL query when using RLIKE or LIKE function

[simran96]

Hi I'm trying to write a query where i want to search for a particular pattern but i'm getting errors. I have used the \ escape as well but still its of no use.

My query:
curl -XPOST "http://xxxx/_sql?format=csv" -H 'Content-Type: application/json' -d'{ "query" : "SELECT * FROM my_table WHERE type RLIKE \".*ECC.*\"", "fetch_size": 10000 }' > tmp.table

ERROR :
{"error":{"root_cause":[{"type":"parsing_exception","reason":"line 1:94: mismatched input 'RLIKE' expecting {<EOF>, 'AND', 'GROUP', 'HAVING', 'LIMIT', 'OR', 'ORDER', LIMIT_ESC}"}],"type":"parsing_exception","reason":"line 1:94: mismatched input 'RLIKE' expecting {<EOF>, 'AND', 'GROUP', 'HAVING', 'LIMIT', 'OR', 'ORDER', LIMIT_ESC}","caused_by":{"type":"input_mismatch_exception","reason":null}},"status":400}

[Igor_Motov]

It is always a good idea to start with checking documentation and trying examples there.

Please also check this note about using double vs single quotes.

The last piece of this puzzle, is how do we escape apostrophe on command line. For that we need to take a look at bash documentation or find an answer on stack overflow.

So, with these important pieces of information in mind, we can rewrite your query into something that works:

curl -XPOST "http://xxxx/_sql?format=csv" -H 'Content-Type: application/json' -d'{  "query": "SELECT * FROM my_table WHERE type RLIKE '\''.*ECC.*'\'' ",  "fetch_size": 10000}' > tmp.table

Alternatively, you can use kibana console that makes all this escaping much simpler and once you have a working statement you can copy it as a curl command.

[simran96]

{"error":{"root_cause":[{"type":"x_content_parse_exception","reason":"[1:14] [sql/query] failed to parse object"}],"type":"x_content_parse_exception","reason":"[1:14] [sql/query] failed to parse object","caused_by":{"type":"json_parse_exception","reason":"Unexpected character ('t' (code 116)): was expecting double-quote to start field name\n at [Source: (org.elasticsearch.common.bytes.AbstractBytesReference$MarkSupportingStreamInputWrapper); line: 1, column: 134]"}},"status":400}

it gave me above error

[simran96]

i tried double quotes as well but its still not working.

[Igor_Motov]

Did you try to copy and paste exactly what I posted? '\'' contains 3 single apostrophes and no double quotes. Which shell do you run this in?

[simran96]

it worked. Thanks. i was running something wrong. U did a great help!!