Search code examples
mysqljsonjson-extract

how to cast this data as json in mysql?

I have data in one of my table columns as like {x:1,y:2} . I wanted to cast the data to json like {"x":"1","y":"2"}

I tried to use JSON_QUOTE and cast as JSON with no success.

Solution

  • I used this Replace section for the answer:

    REPLACE(CONCAT("{\"",REPLACE(REPLACE(REPLACE(REPLACE(form_field.settings,"\n",","),"=",":") ,",","\",\""),":","\":\""),"\"}"),",\"\"","")

    then I completed that Like:

    JSON_EXTRACT(CAST(REPLACE(CONCAT("{\"",REPLACE(REPLACE(REPLACE(REPLACE(form_field.settings,"\n",","),"=",":") ,",","\",\""),":","\":\""),"\"}"),",\"\"","") AS JSON),"$.accessname" )