Tested in mysql Ver 8.0.26

SET @my_json = '{"key1":"val1","key2":"val2"}';
SET @my_key = 'key1';

Everything's okay with the following attempt:

SELECT JSON_VALUE(@my_json,'$.key1');

Though with the following one I get an error:

SELECT JSON_VALUE(@my_json,CONCAT('$.',@my_key));

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('$.',@my_key))' at line 1

Also if I try these other two attempts:

SELECT JSON_VALUE(@my_json,CONCAT('$.','key1'));

SELECT JSON_VALUE(@my_json,CONCAT('$.','"key1"'));

I am doing something wrong?

I need to use dynamic key names as @variable.

CodePudding user response：

As JSON_VALUE() function description claims

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

I.e. you cannot use an expression as path parameter.

Solution: use JSON_EXTRACT() function (accompanied with JSON_UNQUOTE() for string-type data). https://dbfiddle.uk/UdJGwopG