CODEWITHSUNDEEP
mysql
BenG
BenG

Reputation: 56

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

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?

Upvotes: 1

Views: 1573

Answers (1)

Eric
Eric

Reputation: 1259

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"

Upvotes: 4

Related Questions