34

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?

Ramratan Gupta
  • 103
  • 5
Hvisage
  • 473
  • 1
  • 4
  • 6
  • Did you put an extra pair of quotes on the string as it was inserted?? – Rick James Jul 15 '16 at 02: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 08:20
  • Read more on JSON_UNQUOTE here https://dev.mysql.com/doc/refman/5.7/en/json-modification-functions.html#function_json-unquote – hriziya Aug 06 '20 at 14:57

3 Answers3

41

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;

AXM
  • 558
  • 5
  • 5
15

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

Grumoll
  • 251
  • 2
  • 3
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
mustaccio
  • 25,896
  • 22
  • 57
  • 72
Nihal Singh Verma
  • 1