Origins: Key, Value Mechanisms

As with most things in this world, nothing is truly new in the sense that it is completely original without some form of existence that came before and is typically built from existing technologies applied in novel ways. Key, value mechanisms are a prime example of a base technology. I use the term, mechanism, because the use of the key allows you to access the value.

When we say key, value we mean there exists some tag (normally a string) that forms the key and each key is associated with a value. For example, "name":"George" is an example where key (name) has a value (George). Although the values in a key, value store are normally short strings, values can be complex: numeric; alphanumeric; lists; or even nested key, value sets.

Key, value mechanisms are best known for being easy to use programmatically while still retaining readability. That is, with diligent use of whitespace, a complex nested key, value data structure can be read by humans. The following shows one example formatted in a manner how developers would format code. As you can see, it is very easy to see what this set of key, values are storing: name, address, and phone numbers.

Recall from Chapter 1, we saw some examples of these constructs. Now we know how and why they are constructed.

One example of a key, value mechanism (or storage) is Extensible Markup Language (XML) , which has been around for some time. The following is a simple example of XML using the data above. It is the result of a SQL SELECT query with the output (rows) shown in XML format.¹ Note how XML uses tags like HTML (because it is derived from HTML) along with the key, value storage of the data. Here, the keys are <row>, <field> and the values the contents between the start and end tag symbols (<field> </field>).

There are systems designed around key, value mechanisms (called key, value or relational stores) such as the Semantic Web.² In short, the Semantic Web is an attempt to leverage associations of data to describe things, events, and so forth. Sometimes the terms relation store or triple store are used to describe the types of storage systems employed. There are several forms of key, value mechanisms used in the Sematic Web including Resource Description Framework (RDF), Web Ontology Language (OWL), and XML.

There are other examples of key, value mechanisms but the one most pertinent to the document store is JSON.

JSON

I gave a brief description of JSON in Chapter 1. Recall that JSON is a human and machine readable data exchange format. It is also platform independent, which means that there are no concepts of the format that prohibit it from being used in almost any programming language. In addition, JSON is a widely popular format that is used on the Internet.

JSON allows you to describe data in any way you want to without forcing any structure. In fact, you can format (lay out) your data any way you want to. The only real restriction is the proper use of the descriptors (curly braces, square brackets, quotes, commas, etc.) that must be aligned and in some cases paired correctly. When supported in programming languages, developers can easily read the data by accessing it via the keys. Better still, developers don’t need to know what the keys are (but it helps!) because they can use the language support mechanisms to get the keys and iterate over them. In this way, like XML, the data is self-describing.

Now let’s look at another key component of the document store—the NoSQL interface starting with the programming library.

Application Programming Interface

An application programming interface (API) , sometimes simply called a library or programming library, is a set of classes and methods that support operations for one or more capability. These capabilities, through the classes and methods, allow a programmer to use the classes and methods to perform various tasks.

For example, when we use any application with a graphical user interface on our phone, tablet, or computer, the application was built using one of several APIs. The graphical user interface itself was built using one or more APIs that encapsulated a set of classes and methods for drawing windows, creating buttons, and so forth—all of the things that the graphical user interface was engineered to provide for developers.

In the case of the MySQL document store, we use the X DevAPI to access the server through a set of classes and methods that provide connectivity to the server, abstractions of concepts (such as collections, tables, SQL operations), and more. As we learned earlier, the X DevAPI is also built on several other technologies including the X Protocol enabled through the X Plugin. These technologies are combined for a NoSQL interface to the MySQL server.

NoSQL Interface

There are several sometimes conflicting definitions (if not examples) of NoSQL. For the purpose of this book and MySQL in general, a NoSQL interface is an API that does not require the use of SQL statements to access data. The API itself provides the connection to the server as well as classes and methods for creating, retrieving, updating, and deleting data.

For example, if you want to fetch all the data that meets a specific criterion, you must first create a connection to the server, request access to the object containing the data, and then fetch the data. Each of these steps requires creating object instances and calling the methods for those object instances to manipulate the API.

In contrast, the normal mechanism used to interact with MySQL is through a SQL interface in which you must form all your interactions with objects and data with strictly formatted SQL commands. You issue the command and read the results. If you want to write an application that uses the SQL interface, say for getting data, you must use commands to search for the data then convert results into internal programming structures making the data seem like an auxiliary component rather than an integral part of the solution.

NoSQL interfaces break this mold by allowing you to use APIs to work with the data. More specific, you use programming interfaces rather than command-based interfaces.

It is at this point that you’re wondering about how MySQL handles the hybrid option of using JSON documents with relational data. In basic terms, MySQL has been designed to permit storing and retrieving JSON documents in the relational data (via the SQL interface). That is, the server has been modified to handle the JSON document. There is also a set of functions that allows you to do all manner of things with the JSON data making it easy to use JSON via the SQL interface.

However, you also can use JSON documents via the NoSQL X DevAPI either through an SQL command or as a pure document store using the special classes and methods of the X DevAPI. We will learn more about the X DevAPI in Chapter 5.

Document Store

A document store (also known as a document-oriented database) is a storage and retrieval system for managing semistructured data (hence documents). Modern document store systems support a key, value construct such as those found in XML and JSON. Document store systems are therefore sometimes considered a subclass of key, value storage systems.

Document store systems also are commonly accessed by a NoSQL interface implemented as a programming interface (API) that permits developers to incorporate the storage and retrieval of documents in their programs without need of a third-party access mechanism (the API implements the access mechanism). Indeed, the metadata that describes the data is embedded with the data itself. Roughly, this means the keys and the layout (arrangement or nesting) of the keys form the metadata and the metadata becomes opaque to the storage mechanism. More specific, how the data is arranged (how the document is formed or describes the data) is not reflected in or managed by the storage mechanism. Access to the semistructured data requires accessing the mechanism designed to process the document itself using the NoSQL interface.

These two qualities: semistructured data and NoSQL interfaces are what separate document stores from relational data. Relational data requires structure that is not flexible forcing all data to conform to a specific structure. Data is also grouped together with the same structure and there is often little allowance for data that can vary in content. Thus, we don’t normally see document store accessible via traditional relational data mechanism. That is, until now.

One thing that is interesting about working with the document store is you don’t need to be an expert on JavaScript or Python to learn how to work with the document store. Indeed, most of what you will do doesn’t require mastery of any programming language.³ That is, there are plenty of examples of how to do things so you need not learn all that there is to know about the language to get started. In fact, you can pick up what you need very quickly and then learn more about the language as your needs mature.

Now, let’s dive into what JSON documents are and how we can use them with MySQL.

JSON Format Rules

JSON data is formed using strings bracketed with certain symbols. Although we have been discussing key, value mechanisms as they relate to JSON, there are two types of JSON attributes: arrays formed by a comma separated list and objects formed from a set of key, value pairs. You also can nest JSON attributes. For example, an array can contain objects and values in object keys can contain arrays or other objects. The combination of JSON arrays and objects is called a JSON document.

A JSON array contains a list of values separated by commas and enclosed within square brackets ([ ]). For example, the following are valid JSON arrays.

Note that we started and ended the array with square brackets and used a comma to separate the values. Although I did not use whitespace, you can use whitespace and, depending on your programming language, you may be able to also use newlines, tabs, and carriage returns. For example, the following is still a valid JSON array.

A JSON object is a set of key, value pairs where each key, value pair is enclosed within open and close curly braces ({ }) and separated by commas. For example, the following are valid JSON objects. Note that the key address has a JSON object as its value.

JSON arrays are typically used to contain lists of related (well, sometimes) things, and JSON objects are used to describe complex data. JSON arrays and objects can contain scalar values such as strings or numbers, the null literal (just like in relational data), or Boolean literals true and false. Keys must always be strings and are commonly enclosed in quotes. Finally, JSON values can also contain time information (date, time, or datetime). For example, the following shows a JSON array with time values.

The following section describes how we can use JSON in MySQL. In this case, we are referring to relational data but the formatting of JSON documents is the same in the document store.

Using JSON in MySQL

When used in MySQL, JSON documents are written as strings. MySQL parses any string used in a JSON data type validating the document. If the document is not valid—it’s not a properly formed JSON document—the server will produce an error. You can use JSON documents in any SQL statement where it is appropriate. For example, you can use it in INSERT and UPDATE statements as well as in clauses like the WHERE clause.

Properly formatting JSON documents can be a bit of a challenge for some, especially those not used to formatting data structures in programming or scripting languages. The things to remember most is to balance your quotes, use commas correctly, and balance all curly braces and square brackets. Easy, right? There’s just one thing that can stymie some people: quotes!

When you specify keys and values as strings, you must use the double quote character ("), not the single quote ('). Because MySQL expects JSON documents as strings, you can use the single quote around the entire JSON document, but not within the document itself. Fortunately, MySQL provides a host of special functions that you can use with JSON documents, one of which is the JSON_VALID() function that permits you to check a JSON document for validity. It returns a 1 if the document is valid and a 0 if it is not. The following shows the results of an attempt to validate a JSON document with single quotes for the keys and values versus a properly formatted JSON document with double quotes.

Note that the string with the double quotes inside is valid but not the one with single quotes. This is what most people stumble over first when working with JSON.

Let’s look at how to use the JSON document in SQL statements. Suppose we wanted to store the addresses listed previously in a table. For this example, we keep it simple and insert the data in a very simple table. Listing 3-1 shows a transcript of the exercise starting with creating a test table then inserting the first two addresses.

Note that in the CREATE statement we used the data type JSON. This signals MySQL to allocate special storage mechanisms in the storage engine for handling JSON. Contrary to some reports, the JSON data type is not simply direct storage of a string. On the contrary, it is organized internally to optimize retrieval of the elements. Thus, it is very important that the JSON be formatted correctly. You can have multiple JSON columns in a table. However, the sum of the JSON documents in a table row is limited to the value of the variable max_allowed_packet.

Now, let’s see what happens if we use an invalid JSON document (string) in the SQL statement. The following shows an attempt to insert the last address from the previous example only without the correct quotes around the keys. Note the error thrown.

You can expect to see errors like this and others for any JSON document that isn’t formatted correctly. If you want to test your JSON first, use the JSON_VALID() function. However, there are two other functions that may also be helpful when building JSON documents: JSON_ARRAY() and JSON_OBJECT().

The JSON_ARRAY() function takes a list of values and returns a valid formatted JSON array. The following shows an example. Note that it returned a correctly formatted JSON array complete with correct quotes (double instead of single) and the square brackets.

The JSON_OBJECT() function takes a list of key, value pairs and returns a valid JSON object. The following shows an example. Note that here I used single quotes in calling the function. This is just one example in which it is confusing which quotes to use. In this case, the parameters for the function are not JSON documents; they’re normal SQL strings, which can use single or double quotes.

Note once again that the automatic conversion of the quotes in the function result. This can be helpful if you need to build JSON on the fly (dynamically).

There is one other useful function for constructing JSON documents: the JSON_TYPE() function. This function takes a JSON document and parses it into a JSON value. It returns the value's JSON type if it is valid or throws an error if it is not valid. The following shows use of this function with the above statements.

There are more functions that MySQL provides to work with the JSON data type. We will see more about these in a later section.

This section described only the basics for using JSON with MySQL in SQL statements. In fact, the formatting of the JSON document also applies to the document store. However, there is one item we haven’t talked about yet—how to access the elements in a JSON document.

To access an element—via its key—we use special notation called path expressions. The following shows a simple example. Note the WHERE clause. This shows a path expression in which I check to see if the address column includes the JSON key ‘city’ referenced with the special notation address->'$.address.city'. We see more details about path expressions in the "Path Expressions" section.

Path Expressions

If you consider that a JSON document can be a complex set of semistructured data and that at some point you will need to access certain elements in the document, you also may be wondering how to go about getting what you want from the JSON document. Fortunately, there is a mechanism to do this and it is called a path expression. More specific, it is shortcut notation that you can use in your SQL commands (or in the X DevAPI) to get an element without additional programming or scripting.

As you will see, it is a very specific syntax that, although not very expressive (it doesn’t read well in English), the notation can get you what you need without a lot of extra typing. Path expressions are initiated with the dollar sign symbol ($) enclosed in a string. But this notation must have a context. When using path expressions in SQL statements, you must use the JSON_EXTRACT() function, which allows you to use a path expression to extract data from a JSON document. This is because, unlike the X DevAPI classes and methods, path expressions are not directly supported in all SQL statements (but are for some, as we will see). For example, if you wanted the third item in an array, you would use the function as follows.

Note that this accesses data in a JSON array. Here we use an array subscript with square brackets around the index (elements start at 0) as you would for an array in many programming languages.

Now suppose you wanted to access an element by key. You can do that too. In this case, we use the dollar sign followed by a period then the key name. The following shows how to retrieve the last name for a JSON object containing the name and address of an individual.

Note that I had to use two levels of access. That is, I wanted the value for the key named first from the object named name. Hence, I used '$.name.first'. This demonstrates how to use path expressions to drill down into the JSON document. This also is why we call this a path expression because the way we form the expression gives us the “path” to the element.

Now that we’ve seen a few examples, let’s review the entire syntax for path expressions; both for use in SQL and the NoSQL interfaces. Unless otherwise stated, the syntax aspects apply to both interfaces.

If a path expression is evaluated as false or fails to locate a data item, the server will return null. For example, the following returns null because there are only 6 items in the array. Can you see why? Remember, counting starts at 0. This is a common mistake for those new to using path expressions (or arrays in programming languages).

But wait, there’s one more nifty option for path expressions. We can use a shortcut! That is, the dash and greater than symbol (->) can be used in place of the JSON_EXTRACT() function when accessing data in SQL statements by column. How cool is that? The use of the -> operation is sometimes called an inline path expression . For example, we could have written the example above to find the third item in a JSON array from a table as follows.

Note that I simply used the column name, recorded_data, and appended the -> to the end then listed the path expression. Brilliant!

There is one other form of this shortcut. If the result of the -> operation (JSON_EXTRACT) evaluates to a quoted string, we can use the ->> symbol (called the inline path operator) to retrieve the value without quotes. This is helpful when dealing with values that are numbers. The following shows two examples. One example is with the -> operation and the same with the ->> operation.

Note that the recorded_data values (age and name) were stored as a string. But what if the data were stored as an integer? Observe.

Aha! So, the ->> operation is most useful when values must be unquoted. If they were already unquoted (such as an integer), the ->> operation returns the same as the -> operation.

Now, let’s see a few more examples of path expressions. Listing 3-2 shows several examples without explanation. Take a few minutes to look through these and examine the data it is operating on so you can see how each works. With a little imagination, you can drill down to a single data element!

Note that I use the path expression in the WHERE clause checking to see if the result is not NULL. This is a good trick on selecting rows in a table that have the elements you’re looking for in the document. That is, you want only the rows that contain a specific data element (via the path expression).

Now let’s look at the various JSON functions that we can use to work with JSON documents.

JSON Functions

Mastery of these functions is not essential to working with the document store, but can help greatly when developing hybrid solutions in which you use JSON in SQL statements.

These functions can be grouped into categories based on how they are used. We will see functions useful for adding data, those for retrieving (searching) data, and more. The following show how to use the functions using brief examples.

Most functions take a JSON document as the first parameter and a path expression and value as the second and third parameters. Path expressions must be valid for the document and must not contain the wildcards * or **. The functions also return the result so you can use them in SQL statements.