As a front end web developer I'm using JSON more and more. As a back end developer I'm providing JSON as data more and more. But I'm not always storing the data as JSON. In a project I'm using mySQL to store user information, but I has extra data I wanted to store, but didn't want to make a bunch of individual fields for data that I will probably not use. So I made a catchall field and made it JSON format.
Fast forward a few months and I of course need data out of that field and need it to be queryable. So I created a new column for the data, and then moved on to figuring out how to extract the data from the JSON column and adding it to the new column.
Initially I tried using
json_extract( json_column, '$.fieldname') but that would give me the value wrapped in quotes. How to get it without the quotes? Well with
json_unquote of course.
So to update the table column with the extracted data, I ran the following bit of mySQL:
UPDATE tablename SET newcolumn = JSON_UNQUOTE( JSON_EXTRACT( json_column, '$.json_field' ) ) WHERE some_other_field = something_else;