MySQL Bugs: #79050: JSON Path does not support literal escape of double quote character

Bug #79050	JSON Path does not support literal escape of double quote character
Submitted:	1 Nov 2015 0:37	Modified:	10 Nov 2015 8:50
Reporter:	Roland Bouman	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: JSON	Severity:	S3 (Non-critical)
Version:	5.7.9	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
JSON functions do not allow literal escape of double quote in property names appearing within path.

How to repeat:
Suppose we have an object:

mysql> SELECT JSON_OBJECT('"', 1)
+---------------------+
| JSON_OBJECT('"', 1) |
+---------------------+
| {"\"": 1}           |
+---------------------+

Now, we should be able to extract the value of the propery using this path:

$."\""

But we get an error:

mysql> SELECT JSON_EXTRACT(JSON_OBJECT('"', 1), '$."\""');
ERROR 3143 (42000): Invalid JSON path expression. The error is around character position 4 in '$."""'.

Alternative quoting scheme '$.""""' fails with the same error.

Interestingly, it can be extracted when building up the path using CONCAT and JSON_QUOTE:

mysql> SELECT JSON_EXTRACT(JSON_OBJECT('"', 1), concat('$.', json_quote('"')));
+------------------------------------------------------------------+
| JSON_EXTRACT(JSON_OBJECT('"', 1), concat('$.', json_quote('"'))) |
+------------------------------------------------------------------+
| 1                                                                |
+------------------------------------------------------------------+
1 row in set (0.00 sec)

But if we look at the result from concat('$.', json_quote('"')):

mysql> select concat('$.', json_quote('"'));
+-------------------------------+
| concat('$.', json_quote('"')) |
+-------------------------------+
| $."\""                        |
+-------------------------------+
1 row in set (0.00 sec)

..it remains unclear why we have to take this approach. In my opninion the literal path:

'$."\""' 

should behave exactly the same as this expression

concat('$.', json_quote('"'))

Suggested fix:
Please support literal escaped quotes in path to JSON functions.
Please also support the alternative quote escaping scheme: ""

Creating the path with JSON search also works:

mysql> SELECT JSON_EXTRACT(JSON_OBJECT('"', '1'), JSON_UNQUOTE(JSON_SEARCH(JSON_OBJECT('"', '1'), 'one', '1')));
+---------------------------------------------------------------------------------------------------+
| JSON_EXTRACT(JSON_OBJECT('"', '1'), JSON_UNQUOTE(JSON_SEARCH(JSON_OBJECT('"', '1'), 'one', '1'))) |
+---------------------------------------------------------------------------------------------------+
| "1"                                                                                               |
+---------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Indicating that the path itself is fine. It's just that for some reason the equivalent string literal is not parsed or accepted - it should.

Hello Roland,

Thank you for the report.

Thanks,
Umesh

Hi Roland,

I don't think this is a bug.

It is caused by the parser's default (non-standard, I think) handling of backslash in string literals. After the string literal '$."\""' has gone through the parser, the backslash is gone:

mysql> SELECT '$."\""';
+-------+
| $.""" |
+-------+
| $.""" |
+-------+
1 row in set (0,00 sec)

The two string literals '$."\""' and '$."""' represent the same string value, and the path $.""" is invalid.

So in order to create a string literal for the path $."\"", one has to write '$."\\""', like this:

mysql> SELECT JSON_EXTRACT(JSON_OBJECT('"', 1), '$."\\""') AS JE;
+------+
| JE   |
+------+
| 1    |
+------+
1 row in set (0,00 sec)

Alternatively, one can disable the special meaning of backslashes within string literals by enabling the NO_BACKSLASH_ESCAPES SQL mode. See:
https://dev.mysql.com/doc/refman/5.7/en/string-literals.html
https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_backslash_escapes

mysql> SET sql_mode='NO_BACKSLASH_ESCAPES';
Query OK, 0 rows affected, 1 warning (0,00 sec)

mysql> SELECT JSON_EXTRACT(JSON_OBJECT('"', 1), '$."\""') AS JE;
+------+
| JE   |
+------+
| 1    |
+------+
1 row in set (0,00 sec)

Knut, I think I understand. Thanks for the clarification!

Perhaps this should be clarified in the docs (JSON path syntax section)?

I've filed bug#79306 for clarifying the documentation. Thanks.