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;