MySQL Blog Archive
For the latest blogs go to blogs.oracle.com/mysql
Upgrading JSON data stored in TEXT columns

One of the more frequently asked questions with MySQL 5.7 is “How can I upgrade my JSON data from using TEXT in an earlier version of MySQL to use the native JSON data type?”. Today I wanted to show an example of how to do so, using sample data from SF OpenData.

Step 1: Preflight Checks

Since the JSON data type converts data to a native format for storage, it requires that all data inserted into it be valid. This check can be done before running the ALTER TABLE command by using the function JSON_VALID():

Searching by JSON_VALID(feature)=0 will return all invalid JSON documents, and I have intentionally corrupted one row to show an example. I will be required to manually fix this before changing the data type to JSON:

Step 2: ALTER TABLE

Provided the data is all valid, it is now time to change the column definition:

As expected, whitespace information is lost in the conversion to a native type. I should note however that since JSON does not support comments, no other information will be lost*

(* Steven Roussey brings up a good point in the comments, that character set may change).

The ALTER TABLE operation here will need to rebuild the primary key internally, and change data type is one of the few operations currently not covered by Online DDL. For large tables you may consider performing this operation first on a slave, or emulating online ddl via triggers.

But, that’s it! You are now using the native JSON data type 🙂