Bug #81896 Can't use JSON when json name has dash inside

• Submitted: 17 Jun 2016 4:26
• Modified: 20 Jun 2016 14:32
• Reporter: Vadim Tkachenko
• Status: Not a Bug
• Category: MySQL Server: JSON
• Severity: S3 (Non-critical)
• Version: 5.7.13
• OS: Any
• CPU Architecture: Any

[17 Jun 2016 4:26] Vadim Tkachenko

Description:
When I use JSON type with fieldname containing dash - I can't extract value from this field.
I am getting error:
ERROR 3143 (42000): Invalid JSON path expression. The error is around character position 12.

if dash is invalid character for fieldname, then MySQL should not allow me to insert it, but MySQL allows it.

How to repeat:
create table tt (j1 json);
insert into tt values ('{"cache-size":10}'); <-- insert went fine
insert into tt values ('{"cachesize":10}'); 

select JSON_EXTRACT(j1,"$.cachesize") from tt; 
+--------------------------------+
| JSON_EXTRACT(j1,"$.cachesize") |
+--------------------------------+
| NULL                           |
| 10                             |
+--------------------------------+
2 rows in set (0.00 sec)
 
it works fine, but
select JSON_EXTRACT(j1,"$.cache-size") from tt;
ERROR 3143 (42000): Invalid JSON path expression. The error is around character position 12.

Suggested fix:
Either allow to use dash in JSON_EXTRACT or fail to insert invalid value

[17 Jun 2016 7:00] MySQL Verification Team

Hello  Vadim,

Thank you for the report and feedback!

Thanks,
Umesh

[20 Jun 2016 14:32] Knut Anders Hatlen

Hi Vadim,

I think what you are seeing matches what is documented here: http://dev.mysql.com/doc/refman/5.7/en/json-path-syntax.html

In particular this part: "Names of keys must be double-quoted strings or valid ECMAScript identifiers"

Hyphens are not allowed in ECMAScript identifiers, so the name needs to be quoted in the path expression. This seems to work:

mysql> select JSON_EXTRACT(j1, '$."cache-size"') from tt;
+------------------------------------+
| JSON_EXTRACT(j1, '$."cache-size"') |
+------------------------------------+
| 10                                 |
| NULL                               |
+------------------------------------+
2 rows in set (0,00 sec)