Why MySQL JSON_EXTRACT do not accept the return value of JSON_SEARCH?

Question

For example:

SET @key = '["a","b"]';
SELECT JSON_SEARCH(@key, 'one', 'b');

...will return the path:

"$[1]"

Insert this as the path in JSON_EXTRACT like:

SET @value = '["1","2"]';
SELECT JSON_EXTRACT(@value, "$[1]");

...this will return the value:

"2"

But if I write following:

SET @key = '["a","b"]';
SET @value = '["1","2"]';
SET @path = (SELECT JSON_SEARCH(@key, 'one', 'b'));
SELECT JSON_EXTRACT(@value, @path);

...this will drop an error:

SQL Fehler (3143): Invalid JSON path expression. The error is around character position 1 in '"$[1]"'.

Trimming the double quotes works, but I don't like this solution:

SELECT JSON_EXTRACT(@value, TRIM(BOTH '"' FROM @path));

Is there an other way or am I missing something?

Eric · Accepted Answer · 2016-05-03 14:43:25Z

4

JSON_PATH returns a JSON object (a JSON string) that needs to be unquoted when used as string:

SELECT JSON_EXTRACT(@value, JSON_UNQUOTE(@path));

"2"

answered May 3, 2016 at 14:43

Eric

1,28115 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1