2

JSON as String Data vs. JSON as a Data Type

Developers were using JSON in MySQL long before there was a MySQL JSON data type in version 5.7. There is nothing special about JSON that keeps it from being used in earlier versions of MySQL, and there are cases where not using the MySQL data type is preferred. This may seem confusing or contradictory but all will be explained later in this chapter.

JSON String Data

In versions of MySQL released before version 5.7, developers were storing JSON in MySQL in text fields (such as CHAR, VARCHAR, TEXT, or BLOB). And this remains a viable option for developers running versions of MySQL prior to version 5.7.

Example 2-1 JSON stored as a string

This method enables JSON data to be stored. Basically, MySQL stores a string, and nothing is done to validate that it is a valid JSON document. Nothing is done to enforce rigor on the data to ensure that the correct type of data or value range is being inserted. There is no way to ensure that the tags are consistent, and it can be painful to search. For instance, a field for e-mail may be labeled email, eMail, electronic-mail, or one of dozens of other variations. The ability to examine textual JSON information lies with various string functions in MySQL or other programming languages, which are often cumbersome to search.

One benefit of keeping JSON data in a string, however, is that the data will come out as it was put in—this is known as impotency. Later in this chapter, you’ll see that the native MySQL JSON data type “optimizes” the data and sorts the keys in the key/value pairs so that the native JSON data type does not ensure impotency. If exact regurgitation of the data is needed, then the JSON data type should not be used.

Searching can be done using regular expressions (REGEX). Regular expressions are often messy, hard to document, and even harder to understand. Many developers avoid them at all costs, but that is a bit extreme. It is not uncommon to fail to comprehend your own REGEX code written weeks or months before. Here’s an example of REGEX code used in a SELECT query:

Example 2-2 Using a REGEX in a SELECT query

Many developers are very good at writing regular expressions, but many more are not. Often regular expressions are easy to overcomplicate and painful to debug. Take the following example, in which the entire first name of the person to search for is not exactly known, so the search is written to look for a Jim, Jam, or James using a wildcard character (*):

Example 2-3 For an experiment, change ‘j*m’ to ‘Fred’ to observe the message for no matches.

This example worked because a record was found. But suppose the searcher could not remember whether the name was Jim, James, Robert, or Lynn. The regular expression could be rewritten to search for these variables, but it gets much more difficult to interpret. And it’s more difficult to maintain.

Indexing columns to speed searches is common, popular, and highly encouraged under the right circumstances. Indexing an entire text column (but not BLOBs) can be done in most circumstances, but, once again, REGEX has to be used for searching. And the indexes could end up bigger than the data, which would remove any speed advantage to having an index. So do not index blobs including JSON data type columns.

Any changes in the data require that you completely rewrite the string into the database, instead of writing only the changes, which is far more efficient. Early editions of the MySQL 5.7 functions did complete rewrites of JSON data type fields, but the engineers quickly sought to go the more efficient route.

The JSON Data Type

MySQL 5.7 introduced a JSON data type. This means that JSON is a data type, just like INT, REAL, CHAR, VARCHAR, or BLOB. The JSON data type is designed to hold valid JSON documents. Here’s an example:

Example 2-4 Using the JSON data type

The insertion string is the same as the one used in the first example in the chapter, where the data was stored in a CHAR(250) column.

Example 2-5 Selecting JSON data from a JSON data type column

Notice the order of the returned data. The ID column is now first instead of last, as it was in the preceding example. The MySQL server stores JSON data in a binary format optimized for quick searches, which may cause the keys to be returned in an order different from how they were entered. Why is this?

The server first checks to ensure that the document is in a valid JSON format. (If it’s not, the server will return an error.) Then the data is stored in a special internal format optimized for quick lookup by keys or array index position. Think of it as similar to a B-tree or B+-tree, as used by MySQL for indexes within a binary search, when the keys have to be set up in alphabetical order to allow for fast binary-style searches to retrieve the data. The order of the keys may change when the data is stored.