MySQL Bugs: #87360: -> operator doesn't evaluate expression in JSON path

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

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

Hello William,

Thank you for the report and test case.

Thanks,
Umesh

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.

In the MYSQL documentation it states

JSON_VALUE(json_doc, path)

It does not state that "path" can only be a string.  The same syntax is also used for JSON_SET, JSON_EXTRACT, all of which do not require only a string but can also accept variables and functions as the path.

Is this as intended as the documentation does not support that.

In JSON_VALUE it defines path as and does not state it is only a static string:
path is a JSON path pointing to a location in the document.

I've filed bug#103726 to have the documentation updated to mention the current limitations.