MySQL for your JSON

Published

Since the arrival of Compose for MySQL beta, we've been asking customers about how they use the database and what they'd like to learn more about. Many of our customers rely on NoSQL databases like MongoDB and others, but there are increasing numbers who are looking at relational databases and the cost/benefits of their JSON storage and retrieval capabilities. So, we'd like to provide an overview of how to use JSON in your Compose for MySQL deployment.

We've published a similar article that looks at storing JSON in Compose PostgreSQL and asks whether PostgreSQL could be your next JSON database. There, we showed you how to store JSON inside your PostgreSQL database, and highlighted some of the pros and cons of storing documents using its JSON and JSONB data types.

JSON has been supported by MySQL since version 5.7.8. MySQL stores JSON in binary form, like PostgreSQL's JSONB format. This means that the JSON is always validated, because it's always parsed, and it's efficiently accessed as it's optimized into keys with values and arrays. The drawback? If your JSON has multiple fields with the same key, only one of them, the last one, will be retained.

The other drawback is that MySQL doesn't support indexing JSON columns, which means that searching through your JSON documents could result in a full table scan. There are a few ways to get around the indexing conundrum, but the one that MySQL advocates for is to create computed/virtual columns, which they provide an example of here.

In this article, we won't be taking a deep dive into indexing and virtual columns; we'll be focusing on keeping it simple by creating a table that stores some JSON documents that include a list of game players and the games they played. Then we'll perform some simple CRUD operations using MySQL's JSON functions to give you an idea about how they work so that you can decide whether storing JSON in MySQL is the right solution for you. We'll come back to handling indexing in a future column.

Getting started

Let's get started by creating a table players that will store an id and a JSON document within the player_and_games column.

CREATE TABLE `players` (  
  `id` INT NOT NULL,
  `player_and_games` json,
  PRIMARY KEY (`id`)
)

We'll set the id as the primary key. Because Compose for MySQL uses group replication, we'll need to set a primary key for the table otherwise we'll receive an error when entering any JSON documents.

Now, let's insert some data into our table. Below are some SQL insert commands, which have an id and a JSON document that includes the player name and some games: Battlefield, Crazy Tennis, and Puzzler. For Battlefield and Crazy Tennis, we only have "yes" and "no" as options as to whether a player played the game. "Puzzler", on the other hand, includes the time in minutes that it took a player to solve the game.

INSERT INTO `players` (`id`, `player_and_games`) VALUES (1, '{  
    "name": "Sally",  
    "games_played":{    
       "Battlefield": "yes",                                                                                                                          
       "Crazy Tennis": "yes",  
       "Puzzler": {                                                                                                                                                                                                                                                                                                                                                                                               
          "time": 7
        }
      }
   }'
);
...

We've included six players, which look like the following when inserted into our table:

SELECT * FROM players;  
+----+-----------------------------------------------------------------------------------------------------------+
| id | player_and_games                                                                                          |
+----+-----------------------------------------------------------------------------------------------------------+
|  1 | {"name": "Sally", "games_played": {"Puzzler": {"time": 7}, "Battlefield": "yes", "Crazy Tennis": "yes"}}  |
|  2 | {"name": "Thom", "games_played": {"Puzzler": {"time": 25}, "Battlefield": "yes", "Crazy Tennis": "no"}}   |
|  3 | {"name": "Ali", "games_played": {"Battlefield": "no", "Crazy Tennis": "yes"}}                             |
|  4 | {"name": "Alfred", "games_played": {"Puzzler": {"time": 10}, "Battlefield": "no", "Crazy Tennis": "yes"}} |
|  5 | {"name": "Phil", "games_played": {"Puzzler": {"time": 50}, "Battlefield": "no", "Crazy Tennis": "no"}}    |
|  6 | {"name": "Henry", "games_played": {"Battlefield": "yes", "Crazy Tennis": "yes"}}                          |
+----+-----------------------------------------------------------------------------------------------------------+
6 rows in set (0.03 sec)  

MySQL JSON Functions

MySQL provides us with a number of JSON functions that let us perform CRUD operations on JSON documents. For a complete list of functions, see MySQL's JSON function reference. We'll cover extracting, modifying, and removing elements within JSON documents below.

Extracting

To extract some of the elements in our JSON documents, we'll look at the JSON_EXTRACT function. It takes the JSON document, or the name of the column holding the JSON document, and the path of the value you want returned. For example, if we wanted the names of the players in the document, we'd write:

SELECT JSON_EXTRACT(player_and_games, '$.name') FROM players;  
+------------------------------------------+
| JSON_EXTRACT(player_and_games, '$.name') |
+------------------------------------------+
| "Sally"                                  |
| "Thom"                                   |
| "Ali"                                    |
| "Alfred"                                 |
| "Phil"                                   |
| "Henry"                                  |
+------------------------------------------+
6 rows in set (0.03 sec)  

The $ sign simply means to start the search at the top level of the JSON document. From there, we tell the function to search for the name key and return the result. We can keep going down the chain by adding more keys if they exist in the JSON document. For example, if we wanted to get the players who played "Puzzler" and their times, we'd write:

SELECT JSON_EXTRACT(player_and_games, '$.name') as player, JSON_EXTRACT(player_and_games, '$.games_played.Puzzler.time') as time FROM players WHERE JSON_EXTRACT(player_and_games, '$.games_played.Puzzler') > 0;  
+----------+------+
| player   | time |
+----------+------+
| "Sally"  | 7    |
| "Thom"   | 25   |
| "Alfred" | 10   |
| "Phil"   | 50   |
+----------+------+
4 rows in set (0.02 sec)  

One function that makes this query a little more readable is the -> operator, which has been available since MySQL 5.7.9 and is shorthand for JSON_EXTRACT. Like JSON_EXTRACT, we still have to use the column name followed by a JSON path. Writing the above query substituting JSON_EXTRACT with the -> operator, it would look like:

SELECT player_and_games->'$.name' as player, player_and_games->'$.games_played.Puzzler.time' as time FROM players WHERE player_and_games->'$.games_played.Puzzler' > 0;  

The operator is not limited to only SELECT commands, but can be used with commands such as ALTER, UPDATE, and DELETE. But, just remember that anywhere in a SQL command that you need to extract elements from JSON, either the JSON_EXTRACT function or -> operator can be used.

Searching

There are times when you don't know the path of what you're searching for in your JSON documents. That's where JSON_SEARCH comes in. The JSON_SEARCH function accepts the name of the column where your JSON is stored, an argument of "one" or "all" that indicates whether you want all of the results returned or only the first one, and the name of the item you're searching for. This is what a search for the name "Alfred" might look like:

SELECT JSON_SEARCH(player_and_games, "all", "Alfred") as name, id FROM players;  
+----------+----+
| name     | id |
+----------+----+
| NULL     |  1 |
| NULL     |  2 |
| NULL     |  3 |
| "$.name" |  4 |
| NULL     |  5 |
| NULL     |  6 |
+----------+----+
6 rows in set (0.02 sec)  

From the results, we can see that JSON_SEARCH searches through all of the documents returning NULL for the matches that were not found and the path $.name for the name "Alfred", which was found.

Updating

Updating values within our JSON documents requires us to use either JSON_INSERT, JSON_REPLACE, and JSON_SET functions depending on what our goals are. They work similarly to JSON_EXTRACT and JSON_SEARCH functions in that they require the JSON column name and the path within the JSON document in order to add or replace values within the JSON document.

To demonstrate the JSON_INSERT function, we'll add the game Puzzler to Henry. Henry completed Puzzler in approximately 20 minutes so we can use JSON_INSERT to add to the games_played key.

UPDATE players SET player_and_games = JSON_INSERT(player_and_games, '$.games_played.Puzzler', JSON_OBJECT('time', 20)) WHERE player_and_games->'$.name' = 'Henry';  

Since JSON_INSERT takes the JSON column name and the path where you want to insert your new data, we added Puzzler to the end of the games_played path. The key Puzzler was not present so MySQL automatically created it for us. From there we created another JSON object within Puzzler with a time key with a value of 20. Here, we used MySQL's JSON_OBJECT function which creates a JSON object from a list of key pairs. On the other hand, if we had written '{"time": 20}' and inserted it into our JSON object, MySQL would have evaluated it as a string instead of an object.

Querying Henry, we should now see that Puzzler has been created and that the time object has been created and stored with a value of 20.

SELECT * FROM players WHERE id = 6;  
+----+----------------------------------------------------------------------------------------------------------+
| id | player_and_games                                                                                         |
+----+----------------------------------------------------------------------------------------------------------+
|  6 | {"name": "Henry", "games_played": {"Puzzler": {"time": 20}, "Battlefield": "yes", "Crazy Tennis": "no"}} |
+----+----------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)  

JSON_REPLACE unlike JSON_INSERT will only replace values that exist in the JSON document. If the value doesn't exist, nothing will be updated. If we look at the name "Ali", the JSON document says that he has never played "Battlefield" but he played "Crazy Tennis".

+----+-------------------------------------------------------------------------------+
| id | player_and_games                                                              |
+----+-------------------------------------------------------------------------------+
|  3 | {"name": "Ali", "games_played": {"Battlefield": "no", "Crazy Tennis": "yes"}} |
+----+-------------------------------------------------------------------------------+

Using JSON_REPLACE we can set the value of "Battlefield" to "yes" with a similar UPDATE command we ran using JSON_INSERT.

UPDATE players SET player_and_games = JSON_REPLACE(player_and_games, '$.games_played.Battlefield', 'no') WHERE player_and_games->'$.name'='Ali';  
SELECT * FROM players WHERE id = 3;  
+----+--------------------------------------------------------------------------------+
| id | player_and_games                                                               |
+----+--------------------------------------------------------------------------------+
|  3 | {"name": "Ali", "games_played": {"Battlefield": "yes", "Crazy Tennis": "yes"}} |
+----+--------------------------------------------------------------------------------+

Similarly, we can use MySQL's JSON_SET function which inserts non-existing values and replaces existing ones; thus, the function performs the same operation as JSON_INSERT and JSON_REPLACE. If a value does not exist, JSON_SET will create a new value, but if it exists, then it will be replaced with the value that you set when running the function. An example of using JSON_SET is the following:

UPDATE players SET player_and_games = JSON_SET(player_and_games, '$.games_played.Battlefield', 'no', '$.games_played.Puzzler', JSON_OBJECT('time', 15)) WHERE id = 3;  

Here, we updated Ali's Battlefield game to "no" to indicate that he didn't play the game. However, we also added "Puzzler" with a time of 15 minutes to his profile. Therefore, with JSON_SET it allows us to both insert a new value with Puzzler and to update the existing Battleship value to "no".

SELECT * FROM players WHERE id = 3;  
+----+--------------------------------------------------------------------------------------------------------+
| id | player_and_games                                                                                       |
+----+--------------------------------------------------------------------------------------------------------+
|  3 | {"name": "Ali", "games_played": {"Puzzler": {"time": 15}, "Battlefield": "no", "Crazy Tennis": "yes"}} |
+----+--------------------------------------------------------------------------------------------------------+
1 row in set (0.02 sec)  

Deleting

The only function that allows us to remove elements from JSON documents is MySQL's JSON_REMOVE function. The function removes the queried element from the JSON object and returns the result. Therefore, if we wanted to remove "Puzzler" from Phil's JSON profile, we'd write something like this:

UPDATE players SET player_and_games = JSON_REMOVE(player_and_games, '$.games_played.Puzzler') WHERE id = 5;  

Again, we just have to supply the column name and path of the part of the JSON document that we want to remove. Querying Phil, we'll now see that Puzzler has been removed.

SELECT * FROM players WHERE id = 5;  
+----+-------------------------------------------------------------------------------+
| id | player_and_games                                                              |
+----+-------------------------------------------------------------------------------+
|  5 | {"name": "Phil", "games_played": {"Battlefield": "no", "Crazy Tennis": "no"}} |
+----+-------------------------------------------------------------------------------+
1 row in set (0.03 sec)  

Summing up

So we went over a few functions that MySQL offers to make it easy to create, read, update, and delete elements from your JSON documents. The advantage of MySQL's JSON types is that they give you a rich semi-structured datatype within the confines of a relational database, but the lack of direct indexing for JSON data limits its usefulness. In a follow-up article, we'll be discussing MySQL indexing and virtual columns when using JSON.


If you have any feedback about this or any other Compose article, drop the Compose Articles team a line at articles@compose.com. We're happy to hear from you.

Image by Vincent Guth
Abdullah Alger
Abdullah Alger is a content creator at Compose. Moved from academia to the forefront of cloud technology. Love this article? Head over to Abdullah Alger’s author page and keep reading.