Storing Data in MySQL as JSON

1. Overview

Storing data in JSON format in MySQL can be advantageous for applications that require flexible, schema-less data storage. MySQL robustly supports JSON data types, enabling efficient storage, retrieval, and manipulation of JSON data.

In this tutorial, we’ll explore various ways to store data as JSON in MySQL, using practical examples.

2. JSON Data Type in MySQL

MySQL designed the JSON data type to store JSON documents. In particular, it provides several functions and operators that work with JSON data. Furthermore, the data type ensures that stored data is valid JSON. In practice, this offers advantages in both storage efficiency and performance.

Let’s start by creating a table to store JSON data:

CREATE TABLE Departments (
    id INT PRIMARY KEY NOT NULL,
    name VARCHAR(50) NOT NULL,
    info JSON
);

Here, the info column in the created table is designated to store JSON data.

3. Inserting JSON Data

Storing JSON data in MySQL is straightforward with the JSON data type. We can insert data directly or construct it dynamically using various functions. Let’s explore different methods to insert JSON data into the info column of the Departments table we created earlier.

3.1. Direct Insertion

We can directly insert JSON data into the info column by providing a valid JSON document in the INSERT statement:

INSERT INTO Departments (id, name, info) VALUES
(1, 'Computer Science', '{"head": "Dr. Smith", "location": "Building 1", "courses": ["Algorithms", "Data Structures"]}');
Query OK, 1 row affected (0.029 sec)

In this query, the info column contains JSON data representing additional information about the departments.

3.2. Constructing a JSON Object

We can use the JSON_OBJECT function to construct JSON objects from key-value pairs.

Let’s add additional data to the Departments table using this function:

INSERT INTO Departments (id, name, info) VALUES
(2, 'Mathematics', JSON_OBJECT('head', 'Dr. Johnson', 'location', 'Building 2', 'courses', JSON_ARRAY('Calculus', 'Linear Algebra')));
Query OK, 1 row affected (0.006 sec)

Here, the JSON object is constructed dynamically making it easier to build JSON documents from individual components.

3.3. Specifying Arrays of Data

We can use the JSON_ARRAY function to create JSON arrays.

For example, let’s insert another record in the Departments table leveraging the JSON_ARRAY function which deals with the collection of data within the JSON document:

INSERT INTO Departments (id, name, info) VALUES
(3, 'Physics', JSON_OBJECT('head', 'Dr. Clark', 'location', 'Building 3', 'courses', JSON_ARRAY('Quantum Mechanics', 'Thermodynamics')));
Query OK, 1 row affected (0.188 sec)

This is similar to the previous one but highlights using JSON arrays within the JSON objects.

3.4. Variable and Functions

Additionally, we can also use variables and MySQL functions to construct JSON data dynamically:

SET @head = 'Dr. Miller';
SET @location = 'Building 4';
SET @courses = JSON_ARRAY('Classical Mechanics', 'Electromagnetism');

INSERT INTO Departments (id, name, info) VALUES
(4, 'Engineering', JSON_OBJECT('head', @head, 'location', @location, 'courses', @courses));

Here, using variables allows a more dynamic and flexible construction of JSON data.

Let’s proceed to retrieving JSON data.

4. Retrieving JSON Data

Retrieving JSON data from MySQL is as simple as inserting the data. Additionally, MySQL offers several operators and functions to seamlessly extract and manipulate JSON data. Let’s explore how to retrieve JSON data from the Departments table.

4.1. Simple Retrieval

We can retrieve the JSON data directly from the Departments table by selecting the info column:

SELECT id, name, info FROM Departments;
+----+------------------+----------------------------------------------------------------------------------------------------------------+
| id | name             | info                                                                                                           |
+----+------------------+----------------------------------------------------------------------------------------------------------------+
|  1 | Computer Science | {"head": "Dr. Smith", "courses": ["Algorithms", "Data Structures"], "location": "Building 1"}                  |
|  2 | Mathematics      | {"head": "Dr. Johnson", "courses": ["Calculus", "Linear Algebra"], "location": "Building 2"}                   |
|  3 | Physics          | {"head": "Dr. Clark", "courses": ["Quantum Mechanics", "Thermodynamics"], "location": "Building 3"}            |
|  4 | Engineering      | {"head": "Dr. Miller", "courses": "[\"Classical Mechanics\", \"Electromagnetism\"]", "location": "Building 4"} |
+----+------------------+----------------------------------------------------------------------------------------------------------------+
4 rows in set (0.003 sec)

The query returns all the columns of the Departments table including the JSON data stored in the info column.

4.2. Extracting Specific JSON Values

Sometimes, we may want to select specific JSON data stored in the table in MySQL. For example, let’s extract specific values from the JSON data with the use of the JSON_EXTRACT function:

SELECT id, name, JSON_EXTRACT(info, '$.head') AS head FROM Departments;
+----+------------------+---------------+
| id | name             | head          |
+----+------------------+---------------+
|  1 | Computer Science | "Dr. Smith"   |
|  2 | Mathematics      | "Dr. Johnson" |
|  3 | Physics          | "Dr. Clark"   |
|  4 | Engineering      | "Dr. Miller"  |
+----+------------------+---------------+
4 rows in set (0.053 sec)

The query extracts the head value from the JSON document stored in the info column.

4.3. Using the Arrow Operator

Similarly, we can also use the arrow operator (->) to retrieve specific values from the JSON data:

SELECT id, name, info->'$.head' AS head FROM Departments;
+----+------------------+---------------+
| id | name             | head          |
+----+------------------+---------------+
|  1 | Computer Science | "Dr. Smith"   |
|  2 | Mathematics      | "Dr. Johnson" |
|  3 | Physics          | "Dr. Clark"   |
|  4 | Engineering      | "Dr. Miller"  |
+----+------------------+---------------+
4 rows in set (0.005 sec)

The result is the same as the previous example using JSON_EXTRACT. The arrow operator (->) is a shorthand for JSON_EXTRACT.

5. Updating JSON Data

MySQL provides various functions and methods to update specific parts of a JSON document without rewriting the entire JSON content. Furthermore, these functions offer flexibility in modifying stored JSON data within a column. Additionally, let’s explore how to update JSON data in the Departments table with practical examples.

5.1. Updating and Adding Keys

We can use the JSON_SET function to update or add values within a JSON document. Additionally, this function takes a JSON document, a path to the value to be updated, and the new value.

For example, let’s update the head of the Computer Science department to Dr.Parker:

UPDATE Departments
SET info = JSON_SET(info, '$.head', 'Dr. Parker')
WHERE id = 1;

From the query, the info column is updated. Additionally, the ‘$.head’ specifies the path to the head key in the JSON document. Then, the new value Dr. Parker is set for this key.

Given that, we can check the updated data by selecting from the table:

SELECT id, name, info FROM Departments WHERE id = 1;
+----+------------------+------------------------------------------------------------------------------------------------+
| id | name             | info                                                                                           |
+----+------------------+------------------------------------------------------------------------------------------------+
|  1 | Computer Science | {"head": "Dr. Parker", "courses": ["Algorithms", "Data Structures"], "location": "Building 1"} |
+----+------------------+------------------------------------------------------------------------------------------------+
1 row in set (0.005 sec)

The result shows that the head value in the info column has been updated to Dr. Parker.

5.2. Updating Only Existing Keys

Alternatively, the JSON_REPLACE function skips values it doesn’t recognize. For example, let’s update the location of the Mathematics department to Building 2A:

UPDATE Departments
SET info = JSON_REPLACE(info, '$.location', 'Building 2A')
WHERE id = 2;

Here, because location already exists on that record, then JSON_REPLACE replaces the new value Building 2A replaces the existing value.

We can verify that by selecting the relevant row:

SELECT id, name, info FROM Departments WHERE id = 2;
+----+-------------+-----------------------------------------------------------------------------------------------+
| id | name        | info                                                                                          |
+----+-------------+-----------------------------------------------------------------------------------------------+
|  2 | Mathematics | {"head": "Dr. Johnson", "courses": ["Calculus", "Linear Algebra"], "location": "Building 2A"} |
+----+-------------+-----------------------------------------------------------------------------------------------+
1 row in set (0.002 sec)

The result confirms that the location value in the info column has been updated to Building 2A.

Now, let’s demonstrate what happens if we attempt to use JSON_REPLACE on a key that doesn’t exist:

UPDATE Departments
SET info = JSON_REPLACE(info, '$.dean', 'Dr. Brown')
WHERE id = 2;
Query OK, 0 rows affected (0.001 sec)
Rows matched: 1  Changed: 0  Warnings: 0

Since the dean key doesn’t exist in the JSON document, JSON_REPLACE will skip the update without adding this key.

So, let’s double-check the result:

SELECT id, name, info FROM Departments WHERE id = 2;
+----+-------------+-----------------------------------------------------------------------------------------------+
| id | name        | info                                                                                          |
+----+-------------+-----------------------------------------------------------------------------------------------+
|  2 | Mathematics | {"head": "Dr. Johnson", "courses": ["Calculus", "Linear Algebra"], "location": "Building 2A"} |
+----+-------------+-----------------------------------------------------------------------------------------------+
1 row in set (0.002 sec)

As expected, the info column remains unchanged because the dean key wasn’t found in the JSON document.

In contrast, if we use JSON_SET instead, the dean key would be added:

UPDATE Departments
SET info = JSON_SET(info, '$.dean', 'Dr. Brown')
WHERE id = 2;
SELECT id, name, info FROM Departments WHERE id = 2;
+----+-------------+--------------------------------------------------------------------------------------------------------------------+
| id | name        | info                                                                                                               |
+----+-------------+--------------------------------------------------------------------------------------------------------------------+
|  2 | Mathematics | {"dean": "Dr. Brown", "head": "Dr. Johnson", "courses": ["Calculus", "Linear Algebra"], "location": "Building 2A"} |
+----+-------------+--------------------------------------------------------------------------------------------------------------------+
1 row in set (0.002 sec)

It’s important to note that the JSON_SET adds the key if it doesn’t exist while the JSON_REPLACE only updates existing keys.

5.3. Deleting Keys

We can delete keys from a JSON document using the JSON_REMOVE function. For example, let’s remove the courses array from the Physics department’s JSON document:

UPDATE Departments
SET info = JSON_REMOVE(info, '$.courses')
WHERE id = 3;

Executing this query removes the course key from the JSON document in the info column.

Furthermore, let’s confirm this by selecting the row:

SELECT id, name, info FROM Departments WHERE id = 3;
+----+---------+-------------------------------------------------+
| id | name    | info                                            |
+----+---------+-------------------------------------------------+
|  3 | Physics | {"head": "Dr. Clark", "location": "Building 3"} |
+----+---------+-------------------------------------------------+
1 row in set (0.002 sec)

The result shows that the course key has been removed from the JSON document in the info column.

5.4. Appending to JSON Arrays

We use the JSON_ARRAY_APPEND function to append values to a JSON array. For example, let’s add a new course, Artificial Intelligence to the courses array for the Engineering department:

UPDATE Departments
SET info = JSON_ARRAY_APPEND(info, '$.courses', 'Artificial Intelligence')
WHERE id = 4;

The new Artificial intelligence appends to the array.

Having done that, let’s verify if the update was successful by selecting the relevant row:

SELECT id, name, info FROM Departments WHERE id = 4;
+----+-------------+---------------------------------------------------------------------------------------------------------------------------------------------+
| id | name        | info                                                                                                                                        |
+----+-------------+---------------------------------------------------------------------------------------------------------------------------------------------+
|  4 | Engineering | {"head": "Dr. Miller", "courses": ["[\"Classical Mechanics\", \"Electromagnetism\"]", "Artificial Intelligence"], "location": "Building 4"} |
+----+-------------+---------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.003 sec)

The result confirms the successful appending of Artificial intelligence to the courses array in the info column.

6. Conclusion

In this article, we’ve explored various methods to store, retrieve, and update JSON data in MySQL using practical examples. In particular, we leveraged functions such as JSON_SET, JSON_REPLACE, JSON_REMOVE, and JSON_ARRAY_APPEND, to efficiently manage and manipulate JSON documents within the database.