Bug #79049 JSON: path arguments do not allow square brace notation for property names
Submitted: 31 Oct 2015 23:54 Modified: 1 Nov 2015 11:34
Reporter: Roland Bouman Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: JSON Severity:S4 (Feature request)
Version:5.7.9 OS:Any
Assigned to: CPU Architecture:Any

[31 Oct 2015 23:54] Roland Bouman
Description:
The JSON functions that take a path argument, like JSON_EXTRACT, do not accept square brace notation for properties.

Suppose we have an object: 

{
  "bla": 1
}

then the path

$.["bla"] 

should be equivalent to the path:

$.bla (or $."bla")

At least, this is what works in any javascript engine I know.

How to repeat:
mysql> SELECT JSON_EXTRACT(JSON_OBJECT('bla', 1), '$.["bla"]');
ERROR 3143 (42000): Invalid JSON path expression. The error is around character position 2 in '$.["bla"]'.

Suggested fix:
Please support square brace property access operator in path argument to JSON functions.
[1 Nov 2015 11:34] Umesh Shastry
Hello Roland,

Thank you for the report.

// 5.7.9

[umshastr@hod03]/export/umesh/server/binaries/mysql-5.7.9: bin/mysql -uroot -S /tmp/mysql_ushastry.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.9 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT JSON_EXTRACT(JSON_OBJECT('bla', 1), '$.["bla"]');
ERROR 3143 (42000): Invalid JSON path expression. The error is around character position 2 in '$.["bla"]'.
mysql>
mysql> SELECT JSON_EXTRACT(JSON_OBJECT('bla', 1), '$."bla"');
+------------------------------------------------+
| JSON_EXTRACT(JSON_OBJECT('bla', 1), '$."bla"') |
+------------------------------------------------+
| 1                                              |
+------------------------------------------------+
1 row in set (0.00 sec)

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

Thanks,
Umesh
[5 Nov 2015 9:01] Knut Anders Hatlen
Changing the report from bug to feature request, since it's a request to add alternative syntax for existing functionality.