Before MySQL 5.7, you could store a JSON-formatted document in a character field. But large strings are messy to search, and writing regular expressions for finding values within that string can be a frustrating experience. And if you changed one part of the string you had to rewrite the entire string, which is terribly inefficient but was necessary up to MySQL 5.6.
JSON in MySQL
MySQL introduced a native JSON data type in MySQL 5.7. So like an integer, a char, or a real, there became a way to store an entire JSON document in a column in a table of a database—and this document in a column could be roughly a gigabyte in size! The server would make sure it was a valid JSON document and then save it in a binary format that's optimized for searching. This new data type has probably been responsible for more upgrades of MySQL than any other feature.
The data type also comes with over 20 functions. These functions will extract key-value pairs from the document, update data, provide metadata about the data, output non-JSON columns in JSON format, and more. And it's much easier on the psyche than REGEX.
MySQL 8 is now in developer milestone release and will add some new functions and functionality. The JSON_PRETTY_PRINT function will format the output to make it more legible. And there will be major improvements in the way parts of the data are rewritten without rewriting the entire document.
Saving data in an unformatted fashion was considered a NoSQL feature. Many relational databases such as Oracle, SQL Server, Postgresql, and others have added JSON data types, blurring the definition of NoSQL and SQL databases.
Why the Document Store matters
Shortly after the JSON data type emerged came the MySQL Document Store feature. It has been designed for developers who do not know structured query language (SQL) but want to use a database.
And if old-fashioned relational data is needed from these JSON documents, it is easy to extract the various keys into generated columns for standard SQL work, then index the generated columns for quick searches.
JSON is a great way to store data in an informal way. But relational databases thrive on normalized data with all the information normalized into smaller component pieces for speed of access. Also, there may be a lack of rigor imposed on the data, such as when a field that records email address may be keyed as email, eMail, e-mail, or another variant. But there are many compelling reasons to ignore rigor, so long as you understand you may hit this gotcha down the road.