6

JSON and Non-JSON Output

The advantages of traditional relational data and schemaless data are both large. But in some cases, data in a schema needs to be schemaless, or schemaless data needs to be in a schema. Making such changes is easy to do.

JSON-Formatted Data

The various JSON functions supplied by MySQL can also be used to create JSON-formatted output from non-JSON data. Example 6-1 shows non-JSON data output in a non-JSON format, which has been the standard for MySQL for much of its existence.

Example 6-1 Non-JSON data and output from a relational table

This is a fairly typical example of MySQL output for a regular query—good-old MySQL at its finest. But it’s not very useful if that data is needed by something that consumes data in a JSON format.

JSON_OBJECT

The query from Example 6-1 can quickly be adapted to output non-JSON data in a JSON format. JSON objects can easily be created with JSON_OBJECT, but remember that JSON objects contain pairs (key/value pairs), so there cannot be an odd number of arguments.

In Example 6-2, strings are added to the preceding query to create keys for the values. Neither "City" nor "Country" are table row names; both rows are named Name, which can be confusing for casual readers and for the server.

Example 6-2 Using JSON_OBJECT with non-JSON data

Now the non-JSON data is in a JSON format. You can use both non-JSON and JSON columns as arguments to this function.

JSON_ARRAY

In a similar fashion to using JSON_OBJECT, you can use JSON_ARRAY to create arrays from non-JSON data.

Example 6-3 Using JSON_ARRAY with non-JSON data

Examples 6-2 and 6-3 show how traditional MySQL data can be formatted as JSON objects or arrays. And, of course, you can mix and match JSON and non-JSON columns into arrays or objects.

Casting

MySQL enables you to cast one data type as another. This includes JSON. Casting from within MySQL has been around for decades before the JSON data type.

Example 6-4 Casting data as JSON

Conversely, JSON data can be cast as other data types:

Example 6-5 Casting a JSON DOUBLE as unsigned

Make sure that you provide the full path of the JSON key you are searching, or the server will return NULL. It would also be wise to choose a style and stick with it. Example 6-5 is much easier to read than

using the shortcuts for JSON_UNQUOTE wrapped around JSON_EXTRACT, but older versions of MySQL will not be able to handle that query. Wildcards will also cause problems, as shown in Example 6-6.

Example 6-6 How wildcards can affect casting

Example 6-6 illustrates how using a wildcard in the path in JSON_EXTRACT will return an array, while without the data, it is a double. In the first query, where the path $.demographics.LifeExpectancy is fully provided, it generates a warning: “Truncated incorrect INTEGER value: '[77.0999984741211]'” (use SHOW WARNINGS; to display the warnings). This can usually be ignored. But when the path includes a wildcard, such as $.*.LifeExpectancy, the server cannot cast the value returned to unsigned, because it is an array (note the square brackets around the INTEGER value). It may be intuitive to some that the two queries in Example 6-6 are pretty much equivalent, but they are not seen that way by the server.

When in doubt, cast your values to what you need. This is especially important when you’re matching data values with indexes, because it provides the query optimizer with valuable information for building query plans.

Non-JSON Output

Transforming JSON data into temporary relational tables is done with the JSON_TABLE function, which, as of this writing, is available only as a Developer Milestone Release of MySQL 8.0.3, from http://labs.mysql.com; hopefully, it will become part of the generally available release of MySQL 8.

The power to map JSON data into temporary relational tables and then query from those tables opens up the power of relational data processing without having to establish generated columns, using hard-to-debug stored procedures, or creating views. Once we have the relational table, it is easy to use like any other relational table, and we can winnow down the results with the WHERE clause. In Example 6-7, two JSON key/value pairs are extracted, formatted, and then returned in a table.

Format: JSON_TABLE(doc, path, columns (name type PATH path),...) AS Temptable-name

Example 6-7 Using JSON_TABLE to convert JSON data into a relational table

Note that the two columns—country_name and IndyYear—are named only within the JSON_TABLE. The first argument to the function is the JSON column in the table to be used and then the path is specified. The $ path can be used to specify the entire document, or a subpath can be specified.

This query also generated 67 warnings! Twenty of those can be easily removed by upping the CHAR(20) field for country_name to something longer, such as CHAR(50). Truncating data can be dangerous, and it would take some work to check all the documents to find the longest Name and then adjust the query to match. The other 47 warnings are invalid castings of a NULL to an integer. For this particular query, the desired result was for countries with years of independence since 1993; it does not matter. But it would matter if the desired result was, for example, independence years before 1515 as they would not collect the records with NULL in the IndyYear column.

Missing Data

An interesting feature of JSON_TABLE is that it offers you the ability to specify what to do when data is missing. Unlike a relational column, where missing or NULL values can be stored, the JSON document has no guarantee that all desired key/value pairs exist.

Example 6-8 Sample data for JSON_TABLE

Example 6-8 has a small dataset, where all the columns have a name key/value pair and the third record is missing an x key/value pair. The DEFAULT ON EMPTY qualifier can be used to provide data for the missing x key/value pair. Example 6-9 shows how to deal with a missing value.

Example 6-9 JSON_TABLE used with a DEFAULT value for missing data and EXISTS to determine whether the data is available

When we specify DEFAULT '999' ON EMPTY, the server will return the default value when the desired key/value pair is missing. The third record is missing a value for x, and the value 999 is inserted into the table.

The EXISTS keyword returns a Boolean answer if the referenced key/value pair exists. Because all the records have a name key/value pair, the hasname column shows a 1. But not one of the records has a mojo key/value pair, so a 0 is returned in that column. Example 6-10 shows how to use the Boolean data from JSON_TABLE in a SQL query.

Example 6-10 Using the Boolean data from JSON_TABLE’s EXIST keyword as part of a query

In this example, the desired data has the hasname column equal to 1 and the xHasValue column equal to 1. By using such queries, you can easily determine whether documents do indeed have certain key/value pairs.

Nested Data

JSON_TABLE also has the ability to walk down the JSON document path and retrieve nested data. In Example 6-11, there are several values of z for each record’s y key. The ability to extract each individual value comes from the NESTED PATH option.

Example 6-11 This name has nested values of key z within the key y.

Extracting all values of z from the y can be done with string-handling functions or some very nasty regular expression code. However, JSON_TABLE allows walking down paths with nested values. And JSON_TABLE can also provide an ordinal number for returned data.

Example 6-12 Using the NESTED PATH option with JSON_TABLE to extract all values of z from the y key/value pair

This may seem more confusing than it really is. It can often be helpful to read the SQL statements aloud to aid in comprehension. In Example 6-12, the NESTED PATH of $.y[*] (which also could have been $.y) is searched for any values of z in that path. Or, y becomes its own document and the server searched within it for any values of z.

The FOR ORDINALITY operator allows a running total for each of the values that is broken out in the NESTED PATH operation. The document with the _id of 10 has two ordinal values because there were two z values in that document’s y key/value pair. And the document with the _id of 30 has three because its document has three values for z under the y key/value pair.