CODEWITHSUNDEEP
mysqlmysql-5.7
eComEvo
eComEvo

Reputation: 12589

Fixing json data accidentally double encoded

Accidentally wrote a ton of data into a native json column as a string due to converting it using json_encode() before saving with an ORM that already automatically converts objects/arrays into json.

Example:

"{\"created\":true,\"amount\":\"9.95\",\"number\":\"1381329132\",\"date\":{\"date\":\"2019-11-11 00:00:00.000000\",\"timezone_type\":3,\"timezone\":\"UTC\"}}"

How would I construct a repair query to update the column to be an actual json object based on the string data already in there?

MySQL 5.7

Note: This is exactly as the data would appear if pulled from the table in a select statement and is not meant to represent a value that would be added to an SQL statement. If it was meant to be used in an SQL statement, then it would not be considered double encoded.

Upvotes: 2

Views: 995

Answers (1)

ysth
ysth

Reputation: 98398

You can use JSON_UNQUOTE for this:

update yourtable set yourcolumn=json_unquote(yourcolumn) where yourcolumn like '"%';

https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=54e11ad53fb30d967c325445d0e08874

Upvotes: 4

Related Questions