Bug #87360 -> operator doesn't evaluate expression in JSON path
Submitted: 9 Aug 2017 14:35 Modified: 10 Aug 2017 5:42
Reporter: William Chiquito Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: JSON Severity:S4 (Feature request)
Version:5.7.19 OS:Any
Assigned to: CPU Architecture:Any
Tags: -> operator, json, json_extract

[9 Aug 2017 14:35] William Chiquito
Description:
According to the documentation (https://dev.mysql.com/doc/refman/5.7/en/json-search-functions.html#operator_json-column-pa...): "... the -> operator serves as an alias for the JSON_EXTRACT() function ...", the following expressions are equivalent:

-- Expression 1
JSON_EXTRACT(
  column, path
)

-- Expression 2
column -> path

however, the following expressions are not equivalent:

-- Expression 1
JSON_EXTRACT(
  column, CONCAT('pa', 'th')
)

-- Expression 2
column -> CONCAT('pa', 'th')

Equally applies to the ->> operator.

How to repeat:
DROP TABLE IF EXISTS `my_data`;

CREATE TABLE IF NOT EXISTS `my_data` (
  `json` JSON NOT NULL
);

INSERT INTO `my_data`
VALUES ('{"name1": "Name 1"}');

SELECT
  JSON_EXTRACT(
    `json`, '$.name1'
  ) `name`
FROM `my_data`;
+----------+
| name     |
+----------+
| "Name 1" |
+----------+
1 row in set (0.00 sec)

SELECT `json` -> '$.name1' `name`
FROM `my_data`;
+----------+
| name     |
+----------+
| "Name 1" |
+----------+
1 row in set (0.00 sec)

SELECT
  JSON_EXTRACT(
    `json`, CONCAT('$.name', '1')
  ) `name`
FROM `my_data`;
+----------+
| name     |
+----------+
| "Name 1" |
+----------+
1 row in set (0.00 sec)

SELECT `json` -> CONCAT('$.name', '1') `name`
FROM `my_data`;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CONCAT('$.name', '1') `name` FROM `my_data`' at line 1
[10 Aug 2017 5:42] Umesh Shastry
Hello William,

Thank you for the report and test case.

Thanks,
Umesh
[10 Aug 2017 10:55] Knut Anders Hatlen
Reclassifying from bug to feature request, since this limitation is as designed.

https://dev.mysql.com/worklog/task/?id=8607 - High Level Architecture says:

  "<expr>" is a string literal containing path expression that's acceptable by
           JSON_EXTRACT function (see WL#7909). Nothing but string literal is
           accepted here. Both ' (single quote) and " (double quote) could be
           used to surround string.