Migrating to JSON Column
If you have JSON stored as TEXT, you can migrate to a JSON column with a safe ALTER TABLE that validates all existing data.
MySQL
Advanced
5 min read
Migrating TEXT JSON to JSON Column
Example
-- Step 1: Add new JSON column
ALTER TABLE products ADD COLUMN attrs_json JSON;
-- Step 2: Migrate data
UPDATE products
SET attrs_json = CAST(attrs_text AS JSON)
WHERE JSON_VALID(attrs_text);
-- Step 3: Verify
SELECT COUNT(*) FROM products WHERE attrs_json IS NULL AND attrs_text IS NOT NULL;
-- Step 4: Drop old column and rename Pro Tip
Use JSON_VALID() to identify rows with broken JSON before migrating.