MySQL: The JSON Data Type and NoSQL

JSON is a practical way, in plain text format, to exchange data regardless of programming language or platform. JSON is like a CSV file on steroids, or a more compact XML. From time to time, an evolution of files, protocols, or data exchange methods arises. JSON is one of those evolutions.

What does JSON mean? JavaScript Object Notation… Hmm… if it has “java” in the name… A Tibetan monk once said: “Java is the best way to create an application that runs on any platform, but slower and with more crashes.” I am a technician, not a philosopher. But the fact is that JSON (pronounced Djei Zon) has gained followers and is one of the ways to exchange data between applications, servers, platforms, etc. Therefore, it deserves an exclusive data type.

MySQL has implemented typing, or the JSON data type, since version 5.7. Other relational databases also implement, in some way and/or level, the JSON format (type). But I believe the best implementation is still the one made by MySQL.

Some technical considerations about JSON implementation in MySQL:

Strings submitted to a JSON column are validated; if their formatting does not comply with RFC7159, an error will be returned and the data will not be stored in the column;
Properly formatted strings that constitute JSON are converted to an internal binary format that allows working with JSON (now called a document). This enables indirect indexing and smarter, faster searches;
There are several functions that extend the functionalities available to developers (and database administrators);

For those who were on Scarif in the last 5 years and never saw a “Jason” in front of them, it looks like this:

{"Sakila Forever", "8.0", 2020, false, null}

{ "Product_ID": "0001", "Name": "Notebook XPTO", "Brand": "Apple"}

Note that a JSON document or array, ultimately, is just a “string.” Which can easily be misformatted. That is why MySQL validates this “string” when it is inserted into a JSON column.

Theoretical definition: A JSON “string” or document consists of a series of pairs known as Key/Value. The Key is separated from its value by a colon, and each key/value pair is separated from the others by commas. A complete “string” (document, or record if you prefer) is enclosed within curly braces.

Warming up, we will create a table with a JSON column:

CREATE TABLE `JTable` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `JColumn` json DEFAULT NULL,
  UNIQUE KEY `id` (`id`)
);

Let’s test MySQL to make sure it won’t accept poorly formatted “strings”:

INSERT INTO JTable VALUES (NULL, 'Testing 1… 2… 3…');

ERROR 3140 (22032): Invalid JSON text: “Invalid value.” at position 0 in value for column ‘MyJSON_table.JColumn’

Very good, at first glance, MySQL rejected the “string” submitted to the JColumn column (data type: JSON), since it cannot be transformed into a valid JSON document.

This time, let’s try with valid JSON documents (properly formatted strings):

INSERT INTO JTable VALUES (NULL, '{"Product_ID": "MEM1TB", "Description": "Memory Card 1TB", "Group": "Memory"}');