Storing JSON in Your Databases: Tips and Tricks For MySQL

Database architecture and design are becoming an increasingly lost art. With new technologies and the push towards faster development cycles, people continue to take shortcuts, often to the detriment of long-term performance, scalability, and security. Designing how your application stores, accesses, and processes data is so fundamentally important, it can not be overlooked. I want people to understand that early design choices can have a profound impact on their applications. To that end, I will be exploring database design principles and practices over the next several months. I am starting with every developer’s favorite data format: JSON!

It seems that almost every database over the last few years has introduced various degrees of support for storing and interacting with JSON objects directly. While these features are designed to make it easier for application developers to write code faster, the implementations of each implementation tend to vary wildly and can cause some, well, weirdness. Over the next few weeks/months, I will show you some methods, mistakes, and common ways developers store JSON. Just because you can use a database’s native JSON support does not always mean you should! I hope to show you which ones work best for which use cases.

For part one of this series, I am going to focus on MySQL. MySQL’s implementation of the JSON data type was introduced back in 5.7 (Late 2015/Early 2016 timeframe). Since then, a few minor enhancements have made the implementation a bit more liveable. The current iteration MySQL 8 offers a fully functional implementation of JSON functions and features. Let me show you some examples of how to store and interact with your JSON documents within MySQL.

Setup

For all my tests, I wanted a reasonable amount of data to test the performance implications of certain functions.  I opted to use the metadata JSON from http://movienet.site/, about 2.3GB of individual JSON files (one per movie).

I wrote a small python script to load and iterate through the JSON files and load them into MySQL.

I will walk through the examples and show you how I have seen many developers use MySQL to interact with JSON and point out why some of them may be incorrect or cause you issues you may not be aware of.  I will also show you a few other features you may want to look into and explore and offer some design advice.  Let us start with the following simple table definition:

You can see an example of the JSON format

101: Simple JSON Interactions in MySQL

Yes, a single column in a table with a key or two.  Each row would store one of the movies in the downloaded JSON files.  There is an auto_increment key and the IMDB ID that I extracted from the JSON during the load. This structure and setup is a straightforward design with minimal effort.  However, this design also means you generally rely on MySQL as merely the storage for your data.  Provided you are accessing everything by the imdb_id key, you can get and update your JSON to your application easily with a:

Eventually, however, you will want to search within your JSON or just return a portion of the JSON Document. For example, let’s say you only want to find the title and IMDB rating for a specified movie. You can do this with functionality is built-in:

Here you can see we can interact inside the JSON column just like we would with standard data via SQL by using the special syntax “->>’$.key’”.   You can see Avengers: Endgame has a rating of null!  That is no good, and it was a much better movie than that.  Instead of updating and storing the entire JSON document again, MySQL provides a JSON_SET function to set an element within a document.

We now have fixed the missing rating for Endgame!  But we may not know the IMDB ID when we are searching. Just like working with standard data types, you can use data from within your document in a where clause.  In this case, we will look for all movies that start with “Avengers”.

Using the “json_column->’$.title’ in the where clause got us a nice list of Avengers titled movies and TV shows.  But, you can see from this query, we got more than just the blockbuster Avengers movies.  Let’s say you want to refine this a bit more and find just Avengers movies with Robert Downey Jr. in the cast.  This is a bit more difficult, honestly, because the format of our JSON documents uses an array for cast members.

Here is what the JSON looks like:

You can access arrays in a JSON document by referencing the specific index for the element you want ( i.e. [0].name ), however, if you don’t know which one contains the data you are looking for you need to search for it.  MySQL has the function json_search to help with this (there are other functions such as json_contains as well).  json_search searches a provided value and will return the location if found and null if not found:

You will notice that I used the parameter ‘one’, this finds the first value.  You can also use ‘all’ to return every value matched.  In case you are curious as to what the json_search actually returns here is the output:

You can see it returns the position and the property that contains the value.  This output is useful for a variety of reasons.  One is if you need to find which index value contains that particular text.  In the example of searching for Robert Downey JR movies, we can use this index information to return the character he played in each movie.  The first way I have seen this done requires a bit of unholy wrangling but:

Here I am finding where in the document Robert Downey Jr is listed, then extracting the index and using that with the JSON Extract function to pull out the value of the “$[0].character” instead of “$[0].name”.  While this works, it is ugly.  MySQL provides an alternative to doing this by using json_table.

Basically, json_table takes an array and turns it into a table object, allowing you to join and query it.  You can also use this to list all the characters an actor played in any film in his career. Stay tuned for part two of this series, when we will show you some easier and faster ways to use JSON from inside MySQL.