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

Bill Karwin · Accepted Answer · 2023-10-11 16:42:18Z

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

edited Oct 11, 2023 at 16:42

answered Oct 11, 2023 at 14:56

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

santosh Over a year ago

Actual Json string is something like this JSON_CONTAINS(contract_details, '{"name":"~!@#$%^&*())_+?><,./:";'{}||}{"') I have added backslash to escape " and ' Key = name and value = ~!@#$%^&*())_+?><,./:";'{}||}{

Bill Karwin Over a year ago

I added more explanation in my answer above.

santosh Over a year ago

Is there list of such characters to be escaped? Which needs to be escaped by single back slash and double black slash

Bill Karwin Over a year ago

There is only one case where you need a double backslash: when you want a string literal to contain a backslash character.

Paul Spiegel Over a year ago

I suggest to use something like JSON_OBJECT(), JSON_QUOTE or passing the JSON string as parameter instead of escaping strings by hand.

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related