MySQL syntax error for JSON_CONTAINS when querying for column having array of json objects

Question

What is wrong with the below query.

SELECT *
  FROM contract
  WHERE application_id="abcdef"
    AND JSON_CONTAINS(contract_details, '{"name":"~!@#$$%%^&*())_+?><,./:\";\'{}||}{"}');

I get error as below.

SELECT * FROM contract where application_id="abcdef" and JSON_CONTAINS(contract_details, '{"name":"~!@#$$%%^&*())_+?><,./:\";\'{}||}{\"}')
 LIMIT 0, 10000 Error Code: 3141. Invalid JSON text in argument 2 to function json_contains: "Missing a comma or '}' after an object member." at position 34.   0.234 sec

I have column having array of Json objects and in that i want select record based on name and its value

Answer 1

The JSON is indeed not valid.

mysql> select json_valid('{"name":"~!@#$$%%^&*())_+?><,./:\";\'{}||}{\"}') valid;
+-------+
| valid |
+-------+
|     0 |
+-------+

If you need a literal backslash in an SQL string, you need to use a double backslash.

mysql> select json_valid('{"name":"~!@#$$%%^&*())_+?><,./:\\";\'{}||}{\"}') valid;
                                                          ^
+-------+
| valid |
+-------+
|     1 |
+-------+

You need to keep in mind there are two levels of evaluation. One is MySQL string literals, and the next is JSON format. The backslash serves as an escape metacharacter at both levels.

If you need a backslash to escape a character in the JSON format, you need a literal backslash character to appear in your document. But MySQL removes a backslash, as it uses it for escaping characters.

For example, since the string delimiter is ', you need to escape a literal quote character in your string so MySQL understands that it is not the string delimiter.

'abc\'xyz'
    ^^ escaped quote

The resulting string does not contain a backslash. That has been removed as MySQL syntax treats it as an escape.

But you also need JSON to have escaped characters, because it uses " as a delimiter for JSON values within its document syntax.

{"key": "abc\"xyz"} 
            ^^ escaped double-quote

When you type a JSON document as a MySQL string, the string literal evaluation applies to it. The backslash character itself needs to be backslashed, so it is still a single literal backslash character for the benefit of the JSON format.

'{"key": "abc\\"xyz"}'
             ^^ escaped backslash, so a single backslash survives
                to escape the double-quote for JSON format