Fixing a Typo in JSON in MySQL

May 2020

Let's say you have the table Documents which has a column content that contains a JSON. And while writing your application logic, you've accidentally misspelled a word in a key of a JSON property and now the word is misspelled in all your stored Document entries. Fixing such a typo in a JSON property key can actually be quite easy:


UPDATE Documents
SET Documents.content = REPLACE(Documents.content, '\"myTpyo\"', '\"myTypo\"')
    

Like this, you'll replace all occurrences of that "myTpyo" with the correct string "myTypo". Note that it's important to put the quotes around the string, so you don't accidentally match the string in a text field. Even though, who knows, maybe those occurrences should be fixed as well? In that case just drop the quotes:


UPDATE Documents
SET Documents.content = REPLACE(Documents.content, 'myTpyo', 'myTypo')
    

Interested in web development?