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 🙂

4 thoughts on “Upgrading JSON data stored in TEXT columns

    1. Hi Steven, This is a good point, and I have updated the article to mention. If I may expand a little:

      * The JSON specification (http://www.json.org/) requires storage of all unicode characters. Thus; the JSON datatype uses utf8mb4 internally and does not offer the choice of character set.

      * But a user upgrading may have used any character set (as you can see I have in my example: latin1) and thus technically offered only a subset of JSON, perhaps without noticing.

Leave a Reply

Your email address will not be published. Required fields are marked *

Please enter *
Time limit is exhausted. Please reload CAPTCHA.