Description:
https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html
This SQL described in the JSON_VALUE() function description field cannot be executed:
SELECT name, price FROM t1
WHERE JSON_VALUE(j, '$.id' RETURNING UNSIGNED) = 123;
JSON is not specified in the SELECT statement. This SQL is executable:
SELECT j->"$.name" as name, j->"$.price" as price FROM t1
WHERE JSON_VALUE(j, '$.id' RETURNING UNSIGNED) = 123;
Write the execution log:
mysql> CREATE TABLE t1(
-> j JSON,
-> INDEX i1 ( (JSON_VALUE(j, '$.id' RETURNING UNSIGNED)) )
-> );
Query OK, 0 rows affected (0.03 sec)
mysql> INSERT INTO t1 VALUES ROW('{"id": "123", "name": "shoes", "price": "49.95"}');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT name, price FROM t1
-> WHERE JSON_VALUE(j, '$.id' RETURNING UNSIGNED) = 123;
ERROR 1054 (42S22): Unknown column 'name' in 'field list'
mysql> SELECT j->"$.name" as name, j->"$.price" as price FROM t1
-> WHERE JSON_VALUE(j, '$.id' RETURNING UNSIGNED) = 123;
+---------+---------+
| name | price |
+---------+---------+
| "shoes" | "49.95" |
+---------+---------+
1 row in set (0.00 sec)
mysql>
If you have a better SELECT statement, make it better SQL.
How to repeat:
See description
