Bug #79643 Empty strings as keys probably not handled correctly by json_extract()
Submitted: 14 Dec 2015 22:48 Modified: 20 Jan 2016 12:30
Reporter: Pawel Mroszczyk Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: JSON Severity:S3 (Non-critical)
Version: OS:Any
Assigned to: CPU Architecture:Any

[14 Dec 2015 22:48] Pawel Mroszczyk
Description:
I think there may be a bug with path expressions used in json_extract().
I can create a JSON object with an empty key (and try to extract something else) - no error:

mysql> select json_extract( '{"":42}', '$.bla');
+-----------------------------------+
| json_extract( '{"":42}', '$.bla') |
+-----------------------------------+
| NULL                              |
+-----------------------------------+
1 row in set (0.00 sec)

But how would I go about extracting value from that empty key?

mysql> select json_extract( '{"":42}', '$.');
ERROR 3143 (42000): Invalid JSON path expression. The error is around character position 2 in '$.'.

mysql> select json_extract( '{"":42}', '$.""');
ERROR 3143 (42000): Invalid JSON path expression. The error is around character position 4 in '$.""'.

I'm not an expert on JSON, but my best guess is that empty strings are valid keys.

How to repeat:
Shown in the description

Suggested fix:
Again, I'm not an expert on the matter.  I'm implementing json_extract()-like functionality in mysqls and I ran into the same dilemma.  Right now I'm implementing it so that both of these expressions will extract the empty key:

$.
$.""

This opens discussion regarding further "weird but valid" JSON paths.  If above is valid, then so should these be:

$.....foo.....bar
$....""
$.foo."".bar

Implementation-wise, so far supporting the empty string seems simple, while checking and guarding against it being empty results in many corner-case if statements.  So my personal preference is to support empty keys.
[21 Dec 2015 11:12] Knut Anders Hatlen
Hi Pawel,

I believe the unquoted variants ('$.', '$.....', etc) should cause an error to be raised. According to https://dev.mysql.com/doc/refman/5.7/en/json-path-syntax.html, unquoted key names in JSON paths must be ECMAScript identifiers. An empty string cannot be an identifier, if I read the ECMAScript specification correctly.

I don't see any reason to reject the quoted variants, though. Since JSON objects can be created with empty key names, it should be possible to construct paths with empty key names.
[20 Jan 2016 12:30] Jon Stephens
Documented fix in the MySQL 5.8.0 changelog, as follows:

    The empty string is now accepted as a key when used as such with
    JSON functions. In this case, it must be quoted.

Closed.