How to remove string quotes in MySQL 5.7 for function JSON_EXTRACT?

Question

Other than doing string manipulation after each JSON_EXTRACT, is there a simpler or more correct way to have the JSON_EXTRACT return the string WITHOUT the enclosing quotes?

Or should this question be on StachExchange?

Answer 1

If you have MySQL 5.7.13 or later, you may use JSON_UNQUOTE() instead of JSON_EXTRACT() or ->> instead of ->. Example:

SELECT field->>"$.foo.barr" FROM table;

Answer 2

I can't comment on Alex Markov's answer because I don't have enough reputation. In my case using JSON_UNQUOTE instead of JSON_EXTRACT doesn't work, but I can get the result by wrapping the second inside the former, like this: JSON_UNQUOTE(JSON_EXTRACT(bla bla))

Answer 3

I have used JSON_EXTRACT_SCALAR() and it's working fine to unquote the string.

You can try this example:

SELECT emp_name,
JSON_EXTRACT_SCALAR(address,'$[1].city_name') AS city
FROM emp_details