MySQL JSON Data Handling

Introduction

In this lab, you will learn how to handle JSON data within a MySQL database. This includes inserting JSON data into a column, querying JSON data using the JSON_EXTRACT function, updating values within a JSON field, and creating an index on a JSON property for improved query performance.

The lab begins by connecting to a pre-configured MySQL server and creating a database and table specifically designed to store JSON data. You'll then insert a sample product record with its details stored in JSON format, setting the foundation for subsequent steps involving querying and manipulating this data.

Skills Graph

Insert JSON Data into a Column

In this step, we will learn how to insert JSON data into a MySQL column. MySQL 5.7.22 and later versions support a native JSON data type, which allows you to store and manipulate JSON documents directly within your database.

Before we begin, let's make sure you have access to a MySQL server. In this LabEx environment, a MySQL server is pre-configured.

First, let's connect to the MySQL server using the mysql client. Open a terminal and execute the following command:

mysql -u root -p

You will be prompted for the root password. Since no password was set during installation, just press Enter.

Now that you are connected to the MySQL server, let's create a database and a table to store our JSON data.

CREATE DATABASE IF NOT EXISTS jsondb;
USE jsondb;

CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(255),
    product_details JSON
);

This SQL code first creates a database named jsondb if it doesn't already exist. Then, it switches to using the jsondb database. Finally, it creates a table named products with three columns: id, product_name, and product_details. The product_details column is of the JSON data type.

Now, let's insert some JSON data into the product_details column. We'll insert a record representing a product with its details stored in JSON format.

INSERT INTO products (product_name, product_details) VALUES (
    'Laptop',
    '{
        "brand": "Dell",
        "model": "XPS 13",
        "specs": {
            "processor": "Intel Core i7",
            "memory": "16GB",
            "storage": "512GB SSD"
        },
        "price": 1200
    }'
);

This INSERT statement inserts a new row into the products table. The product_name is set to 'Laptop', and the product_details column is populated with a JSON document containing information about the laptop's brand, model, specifications, and price.

To verify that the data has been inserted correctly, let's query the table:

SELECT * FROM products;

You should see output similar to this:

1 row in set (0.00 sec)

This confirms that the JSON data has been successfully inserted into the product_details column.

You can exit the MySQL client by typing exit and pressing Enter.

exit

In this step, you have learned how to create a table with a JSON column and insert JSON data into it. This is the foundation for working with JSON data in MySQL.

Query JSON with JSON_EXTRACT

In this step, we will learn how to query JSON data stored in a MySQL column using the JSON_EXTRACT function. This function allows you to extract specific values from a JSON document based on a path.

First, let's connect to the MySQL server using the mysql client. Open a terminal and execute the following command:

mysql -u root -p

You will be prompted for the root password. Since no password was set during installation, just press Enter.

Next, select the jsondb database we created in the previous step:

USE jsondb;

Now, let's use the JSON_EXTRACT function to retrieve the brand of the laptop from the product_details column.

SELECT JSON_EXTRACT(product_details, '$.brand') AS brand FROM products;

In this query, JSON_EXTRACT(product_details, '$.brand') extracts the value associated with the key "brand" from the product_details JSON document. The $.brand is a JSON path expression that specifies the path to the desired value. The $ symbol represents the root of the JSON document.

You should see output similar to this:

+-------+
| brand |
+-------+
| "Dell"|
+-------+
1 row in set (0.00 sec)

Notice that the extracted value is enclosed in double quotes. This is because JSON_EXTRACT returns the value as a JSON string. If you want to retrieve the value as a string without the quotes, you can use the ->> operator, which is a shorthand for JSON_UNQUOTE(JSON_EXTRACT(...)).

Let's try that:

SELECT product_details->>'$.brand' AS brand FROM products;

The output will now be:

+-------+
| brand |
+-------+
| Dell  |
+-------+
1 row in set (0.00 sec)

Now, let's extract the processor from the specs object within the product_details JSON document.

SELECT product_details->>'$.specs.processor' AS processor FROM products;

Here, $.specs.processor specifies the path to the "processor" value within the "specs" object.

The output will be:

+-----------------+
| processor       |
+-----------------+
| Intel Core i7   |
+-----------------+
1 row in set (0.00 sec)

You can also use JSON_EXTRACT in the WHERE clause to filter results based on JSON values. For example, let's find all products with a price greater than 1000.

SELECT product_name FROM products WHERE CAST(product_details->>'$.price' AS SIGNED) > 1000;

In this query, we use CAST(product_details->>'$.price' AS SIGNED) to convert the extracted price value to a signed integer so that we can compare it numerically.

The output will be:

+--------------+
| product_name |
+--------------+
| Laptop       |
+--------------+
1 row in set (0.00 sec)

You can exit the MySQL client by typing exit and pressing Enter.

exit

In this step, you have learned how to use the JSON_EXTRACT function and the ->> operator to query JSON data in MySQL. You also learned how to use JSON values in the WHERE clause to filter results.

Update a JSON Field Value

In this step, we will learn how to update a JSON field value within a MySQL column using the JSON_SET function. This function allows you to modify specific values in a JSON document based on a path.

First, let's connect to the MySQL server using the mysql client. Open a terminal and execute the following command:

mysql -u root -p

You will be prompted for the root password. Since no password was set during installation, just press Enter.

Next, select the jsondb database we created in the previous step:

USE jsondb;

Now, let's use the JSON_SET function to update the price of the laptop in the product_details column. Suppose we want to increase the price to 1250.

UPDATE products
SET product_details = JSON_SET(product_details, '$.price', 1250)
WHERE product_name = 'Laptop';

In this UPDATE statement, JSON_SET(product_details, '$.price', 1250) modifies the product_details JSON document by setting the value associated with the key "price" to 1250. The $.price is a JSON path expression that specifies the path to the value to be updated.

To verify that the data has been updated correctly, let's query the table and check the price:

SELECT product_details->>'$.price' AS price FROM products WHERE product_name = 'Laptop';

You should see output similar to this:

+-------+
| price |
+-------+
| 1250  |
+-------+
1 row in set (0.00 sec)

This confirms that the price has been successfully updated to 1250.

Now, let's update the processor in the specs object within the product_details JSON document. Suppose we want to upgrade the processor to "Intel Core i9".

UPDATE products
SET product_details = JSON_SET(product_details, '$.specs.processor', 'Intel Core i9')
WHERE product_name = 'Laptop';

Here, $.specs.processor specifies the path to the "processor" value within the "specs" object.

To verify the update, let's query the table again:

SELECT product_details->>'$.specs.processor' AS processor FROM products WHERE product_name = 'Laptop';

The output will be:

+-----------------+
| processor       |
+-----------------+
| Intel Core i9   |
+-----------------+
1 row in set (0.00 sec)

This confirms that the processor has been updated to "Intel Core i9".

You can also add new fields to the JSON document using JSON_SET. For example, let's add a new field called "color" with the value "Silver".

UPDATE products
SET product_details = JSON_SET(product_details, '$.color', 'Silver')
WHERE product_name = 'Laptop';

To verify the addition, let's query the table:

SELECT product_details->>'$.color' AS color FROM products WHERE product_name = 'Laptop';

The output will be:

+--------+
| color  |
+--------+
| Silver |
+--------+
1 row in set (0.00 sec)

You can exit the MySQL client by typing exit and pressing Enter.

exit

In this step, you have learned how to use the JSON_SET function to update existing JSON field values and add new fields to a JSON document in MySQL.

Create an Index on a JSON Property

In this step, we will learn how to create an index on a JSON property in MySQL. Indexing JSON properties can significantly improve the performance of queries that filter or sort data based on values within the JSON documents.

First, let's connect to the MySQL server using the mysql client. Open a terminal and execute the following command:

mysql -u root -p

You will be prompted for the root password. Since no password was set during installation, just press Enter.

Next, select the jsondb database we created in the previous steps:

USE jsondb;

Now, let's create an index on the price property within the product_details JSON column. We will create an index named idx_product_price on the products table.

CREATE INDEX idx_product_price ON products ((CAST(product_details->>'$.price' AS SIGNED)));

In this CREATE INDEX statement:

• idx_product_price is the name of the index.
• products is the name of the table.
• ((CAST(product_details->>'$.price' AS SIGNED))) specifies the expression on which the index is created. We use product_details->>'$.price' to extract the price value from the JSON document, and then CAST(... AS SIGNED) to convert it to a signed integer. This is necessary because indexes can only be created on scalar values, not directly on JSON documents.

To verify that the index has been created, you can use the SHOW INDEXES command:

SHOW INDEXES FROM products;

You should see output that includes the idx_product_price index. The output will look something like this:

+----------+------------+---------------------+--------------+------------------------------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table    | Non_unique | Key_name            | Seq_in_index | Column_name                                    | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+----------+------------+---------------------+--------------+------------------------------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| products |          0 | PRIMARY             |            1 | id                                             | A         |           1 |     NULL | NULL   |      | BTREE      |         |               | YES     | NULL       |
| products |          1 | idx_product_price   |            1 | NULL                                           | NULL      |           1 |     NULL | NULL   | YES  | BTREE      |         |               | YES     | CAST(json_extract(`product_details`,_utf8mb4'$.price') as signed) |
+----------+------------+---------------------+--------------+------------------------------------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
2 rows in set (0.00 sec)

The important parts to notice are the Key_name which is idx_product_price and the Expression which shows the casted JSON extraction.

Now, let's analyze how the index improves query performance. To do this, we'll use the EXPLAIN command. First, let's run an EXPLAIN on a query that filters by price without the index:

(To simulate not using the index, we'll drop it first, then recreate it later)

DROP INDEX idx_product_price ON products;
EXPLAIN SELECT product_name FROM products WHERE CAST(product_details->>'$.price' AS SIGNED) > 1200;
CREATE INDEX idx_product_price ON products ((CAST(product_details->>'$.price' AS SIGNED)));

The output of the EXPLAIN command will show that MySQL has to perform a full table scan to execute the query. Look for type: ALL in the output.

Now, let's run the same EXPLAIN command with the index:

EXPLAIN SELECT product_name FROM products WHERE CAST(product_details->>'$.price' AS SIGNED) > 1200;

The output of the EXPLAIN command should now show that MySQL is using the index to execute the query. Look for type: range and possible_keys: idx_product_price and key: idx_product_price in the output. This indicates that the index is being used, which will result in significantly faster query execution, especially for large tables.

You can exit the MySQL client by typing exit and pressing Enter.

exit

In this step, you have learned how to create an index on a JSON property in MySQL and how to verify that the index is being used by the query optimizer. Indexing JSON properties is an important technique for optimizing the performance of queries that work with JSON data.

Summary

In this lab, we learned how to insert JSON data into a MySQL column. We started by connecting to a MySQL server and creating a database named jsondb and a table named products with a JSON type column called product_details.

Then, we inserted a record into the products table, populating the product_details column with a JSON document containing information about a laptop's brand, model, specifications, and price. This demonstrated the basic process of storing JSON data within a MySQL database.