Once upon a time there was one computer. Then someone built a second one and wanted some code off the first computer. That meant we needed a way to move information without dependencies on the underlying hardware. Since then, there have been many character encoding and interchange standards (ASCII, EBCIDIC, SGML, XML, etc.) that have had their time in the spotlight. For the past few years, JavaScript Object Notation (JSON) has been the most popular.
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.
Ironic, huh?
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.
The developer creates data collections and performs the create, remove, update, & delete (CRUD) functions on the data as needed in the programming language of choice (Java, JavaScript, Node.JS, Python, C++, and more on the way). The developer needs no knowledge of SQL and probably does not care that SQL is being done for them behind the scenes, where their data is kept in a JSON column. This way they can save and use data without having to develop normalized database tables, architect their data, or twiddle their thumbs while waiting for a database administrator to establish the data store. Many projects start with no idea of what the data looks like and will proceed by evolving the data to fit the current circumstances.
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.
Drawbacks
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.
If you're ready to give it a try, download MySQL, and while you're there, download MySQL Workbench as it supports JSON columns. And both are free!
3 Comments