20

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?

3
  • Did you put an extra pair of quotes on the string as it was inserted??
    – Rick James
    Jul 15 '16 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
    Jul 15 '16 at 8:20
  • Read more on JSON_UNQUOTE here dev.mysql.com/doc/refman/5.7/en/…
    – hriziya
    Aug 6 '20 at 14:57
25

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;

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

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
  • 1
    That's what I did in my case too.
    – hriziya
    Aug 6 '20 at 14:55
0

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
    Sep 15 at 10:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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