1

Introduction

Once upon a time, there was one computer. With the creation of a second computer, we experienced problems moving and sharing data between systems. It took decades of hard work, arguments, negotiations, and proposed standards, and a great deal of frustration, to get to the point where data transfer between systems is almost trivial. For many years, many very smart people argued over ASCII or EBCDIC character encoding, endianness of computer memory, and various standards such as the Standard Generalized Markup Language (SGML) and the Extensible Markup Language (XML). Today, JavaScript Object Notation, or JSON, has become the favorite way to encode data for moving between various systems. JSON-encoded data has several advantages over previous efforts in that it is very human-readable, easy to parse with programs, and not overly complicated.

Considering the vast increase in the volume of data being shared every year, it is imperative that the information be shared in an easy-to-digest format. Having an easy-to-produce format provides benefits like ease of programming, ease of proofreading or debugging, and low cost of entry. JSON provides these benefits beautifully.

MySQL had more than 20 years of life before it received a native JSON data type. So as with an INTEGER, a DECIMAL, or a DATE data type, we can store an entire JSON document in a column of a row of a schema. MySQL 5.7 arrived with the ability to store roughly a gigabyte of data in a column in a row in a table. Before the native JSON data type, there were special schemas or user-defined functions with limited JSON support. This new data type has proven to be very popular and has probably encouraged many site administrators to upgrade from much earlier versions of MySQL to gain access to it.

JSON

JavaScript Object Notation (JSON) is a text-based, language-independent data interchange format for the serialization of data. It is derived from the object literals of JavaScript as defined in the third edition of the ECMAScript Language Specification. There are actually two standards for JSON: Internet Engineering Task Force (IETF) Request For Comment (RFC) 7159 (https://tools.ietf.org/html/rfc7159) and the European Computer Manufacturers Association (ECMA) Standard 404 (https://www.ecma-international.org/publications/standards/Ecma-404.htm). The IETF’s document is about 16 pages long, while ECMA’s is 5. This is a relatively short set of standards compared to the IETF RFC 5321 for the Simple Mail Transfer Protocol (SMTP) at 95 pages. But the two standards for JSON are fairly explicit.

JSON has a grammar, and it is simple. JSON is a series of tokens: six structural characters ([, ], {, }, :, and ,), strings, numbers, and three literal names (false, null, or true). Objects begin and end with curly brackets, { and }, respectively; arrays begin and end with square brackets, [ and ], respectively. A colon (:) is used to separate a name and a value. Multiple objects or arrays are separated by commas (,). Like toy building blocks, simple components can be combined to create much more complex structures.

Although I have described the attributes, it may be easier to think of JSON data being structured as objects with name/value pairs or as ordered lists of values, also known as arrays. Most programming languages and their programmers use objects and/or arrays on a regular basis. The simple design of JSON enables it to be independent of the computer language used to generate or read the data.

So what does JSON look like? Here’s a simple example:

Example 1-1 Example of a JSON document

The data is enclosed within curly braces, which tells us it is a JSON object, rather than an array, which would have the data within square brackets. Inside this object are three key/value pairs: name/Bond, first/James, and ID/007. Although all of the data could have been placed on one line and would still be a valid JSON document, it is formatted as multiple lines for ease of reading. For now, you can consider all the keys and their values as strings.

MySQL

This is not a book on administrating, programming, or using MySQL. To use the information in this book, however, you will need access to a server running MySQL 5.7.5 or later, on a local system or system available over a network. The Community Edition of the MySQL server is free and available for Microsoft Windows, Linux, and Mac OS; or it’s available as source code. There is a paid Enterprise Edition for customers with support contracts, and the examples included herein will also work with this edition.

If you need to install MySQL, follow the directions at the MySQL Documentation site: https://dev.mysql.com/doc/. Follow the instructions appropriate for your operating system platform. You can also install MySQL within a virtualized server or container for the purposes of this book. Remember that you need at least MySQL 5.7 or 8.0 to have access to the MySQL functions.

Be aware that Oracle engineers who create and maintain the MySQL software have evolved the way JSON and the JSON functions work as the standards have changed, as users have given feedback on the product, and as part of general improvements in the product. Hopefully, all these changes are noted in the text, but be aware that the version of MySQL you are running may behave slightly differently from previous or later versions.

The Example Database

The examples in this book will mainly use the world_x example dataset or are short enough to easily be retyped by the reader. The predecessor, world database, has been used for many years by MySQL in documentation, training, examples, and blogs. After installing the latest and greatest MySQL version, you can install the world_x database after downloading it from https://dev.mysql.com/doc/index-other.html.

You can perform the installation in several ways, but the most popular two ways are from a command line and from within the MySQL shell. There are other methods as well, but these two are very reliable and simple. Following are examples.

Example 1-2 Installing the world_x database from the Linux shell

Example 1-3 Installing the world_x database from the MySQL shell

NOTE   In some cases, the schema may not be created already, and it will report an error. This is easy to fix, and you can re-create the schema with

How to Use This Book

This book was designed for readers to enter the examples on their own installations of MySQL. Some people can learn very efficiently by just reading, but many more gain additional insight by typing the examples into their own MySQL instance. The simple examples that follow can easily be deleted later when no longer needed.

The official MySQL software documentation is the definitive reference on the various features of MySQL. However, the examples in the documentation are often hard to grasp for novices at the start of the learning curve or for the experienced but not in a certain facet. So use this book to backfill areas where the manual is nebulous, confusing, or just not at your level.

Some points, such as array numbering starting at zero and document pathing, are restated over and over again and may seem tiresome and pedantic for those reading from the first page through to the last. But many readers will use this book by referring only to the part of a section that concerns them at the moment. Those folks may be oblivious to warnings presented a page or more earlier, and being dogmatic on these points will save those people grief.

One of the problems in learning computer technologies is learning to understand mistakes. This includes learning to understand error messages and warnings. If you make a mistake in entering one of the examples, examine any messages for clues and then compare what has been entered with what is in this text. It’s quite common to miss a single or double quote and transpose keywords when entering SQL, but the server will not simply tell you that you fat-fingered a code entry. So you must learn to comprehend the error messages to find out where you have goofed. Do not be afraid to make mistakes! Mistakes are part of learning, and learning to fix mistakes is part of the process. Many martial arts experts stress that the difference between a novice and a master is that a master knows when a mistake is starting and can rectify it before it becomes a problem. Rectifying fat-fingered commands is a regular occurrence and a great way for you to learn to understand what error messages are trying to tell you.

It is very hard for a static item, like this book, to keep current with ever-changing software. Some of the functions discussed herein have evolved over time, and some are in an experimental, evolving stage. Please use this book as an addendum to the official MySQL software documentation. I have tried to make this book as useful as possible, but as time marches on, the details inside may not reflect the actual software—and that is actually a good thing.

This book starts with “once upon a time,” and I would like to have it end with “they lived happily ever after.” The engineers at MySQL and contributors from the MySQL Community have put a lot of effort into the JSON data type and the supporting functions. New uses such as the MySQL Document Store will hopefully bring new changes. JSON and MySQL combine to make a lot of things very convenient for developers, and the future should only improve, thus allowing all involved to live happily ever after.

Finally, any errors or omissions are my own, and I take full responsibility for them.