JSON_VALUE and path as variable in MySQL 8.x not working-CodePudding

I use Mysql 8.0 and trying to use JSON_VALUE to work with arrays in stored procedures

CREATE PROCEDURE `tJS` ()
BEGIN
   DECLARE _counter INT DEFAULT 0;
   SET @arr = '["123","3","15"]';

   WHILE _counter < JSON_LENGTH(@arr) DO
     --here I want extract element with index _counter from @arr 
     SET @ix = CONCAT('$[', _counter, ']') ;
     SET @v = JSON_VALUE(@arr, @ix);
     -- some stuff with @v must be here
   END WHILE;
END

I get error at line SET @v = JSON_VALUE(@arr, @ix):

"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 '@ix);"

As I found out, in MySQL 8 JSON_VALUE works only if path(second parameter) a fixed string, not variable.

So, this command works:

SET @v = JSON_VALUE(@arr, '$[0]')

And this command throws sames yntax error:

SET @v = JSON_VALUE(@arr, CONCAT('$[', _counter, ']'));

Is there any way in MySQL 8.0 to set second parameter in JSON_VALUE not fixed string, but variable?

CodePudding user response：

https://dev.mysql.com/doc/refman/8.0/en/json-search-functions.html#function_json-value says:

path is a JSON path pointing to a location in the document. This must be a string literal value.

So the answer is no, you can't use a variable in place of the path.

You can use PREPARE and EXECUTE to run a dynamic SQL query, after concatenating your path expression into the SQL query string before it is prepared.

But my opinion is that your approach is inadvisable for several reasons:

Using an expression or variable for the JSON path when the manual says it doesn't support this (you should have checked the doc for JSON_VALUE() before posting to Stack Overflow).
Using a JSON document when you really want to use SQL expressions to process discrete elements within that document as if they are relational data (JSON is not relational).
Using a MySQL stored procedure for a task that would be easy in literally any other programming language.