Suppose I have a document in my database with this literal String value for a field (nothing here is an escape character; this is the final string that should appear in the UI of an app):
\\\blah%%BLAH\\
I anticipated this query would work:
SELECT *, Meta().id FROM SomeCollection WHERE (LOWER(`title`) LIKE "\\\\\\blah\%\%blah\\\\")
But I get syntax error: invalid quoted string - invalid escape sequence - line 1, column 63, which is the leading ".
I figured the query engine might be getting tripped up by the closing \", so I switched to single quotes. But that gives: syntax error: invalid quoted string - invalid escape sequence - line 1, column 71, which is just after the first “b” in “blah”.
All of these tests are done in Capella’s web UI query interface. So what’s the correct way to escape in a situation like this?
NB: I’m not concerned with performance or alternate approaches for this exact query. This is a test case specifically designed to exercise escapes.
1 Like
Update
I found an old thread and made some progress. It turns out that the % wildcards must be double-escaped. So I’ve now got this working:
SELECT *, Meta().id FROM SomeCollection WHERE (LOWER(`title`) LIKE "\\\\\\blah\\%\\%blah")
But if the string I want to match ends with a backslash, I have a new error:
//Error evaluating filter - cause: Trailing escape character (\\) in pattern
SELECT *, Meta().id FROM SomeCollection WHERE (LOWER(`title`) LIKE "\\\\\\blah\\%\\%blah\\")
I haven’t been able to resolve this yet. Single/Double quotes don’t matter.
I Need a New Backslash Key
So the backslashes need to be double-escaped as well. Which means to match a single backslash, you must use four of them in the query. Here’s what ended up working
Needle: \\\blah%%BLAH\\
Query: SELECT *, Meta().id FROM Foo WHERE (LOWER(title) LIKE "\\\\\\\\\\\\blah\\%\\%blah\\\\\\\\")
A Smaller Example
Suppose we want to find this needle: blah\blah. In the JSON document, that has be represented with the backslash escaped: blah\\blah.
It looks like the query engine is targeting the JSON value rather than what the string actually is when the JSON value is parsed and the escapes are removed.
Because this query matches nothing:
SELECT *, Meta().id FROM Foo WHERE (LOWER(`title`) LIKE "blah\\blah")
But this query matches the blah\blah needle:
SELECT *, Meta().id FROM Foo WHERE (LOWER(`title`) LIKE "blah\\\\blah")
A suggestion
It would be helpful for the docs to clarify this. You’re querying against the already-escaped string that’s in JSON, not the “original” or “raw” string that you stored. This matters because applications using the SDKs will have the string blah\blah and if you think you just have to escape that backslash once, your query isn’t looking for what you think it’s looking for.
1 Like