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?

  • Did you put an extra pair of quotes on the string as it was inserted??
    – Rick James
    Commented Jul 15, 2016 at 2:26
  • The JSON from the REST call is a simple: {"key": "value to return" } Looks like the only way is to do a TRIM(BOTH " variable) <something like that ;) >
    – Hvisage
    Commented Jul 15, 2016 at 8:20
  • Read more on JSON_UNQUOTE here dev.mysql.com/doc/refman/5.7/en/…
    – hriziya
    Commented Aug 6, 2020 at 14:57

3 Answers 3


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;

  • 2
    A much nicer syntax, I'm glad you answered that way.
    – John
    Commented May 15, 2020 at 2:35

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))

  • 1
    That's what I did in my case too.
    – hriziya
    Commented Aug 6, 2020 at 14:55

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
  • 1
    This is not a valid function for MySQL 5.7 (or any other current version).
    – John Rix
    Commented Sep 15, 2021 at 10:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.