MySQL JSON Data Handling

Introduction

In this lab, you will learn how to effectively use the JSON data type in MySQL. You will perform fundamental operations such as inserting JSON documents, querying specific fields using functions like JSON_EXTRACT and the ->> operator, modifying data within a JSON column, and optimizing queries by creating an index on a JSON property.

Throughout this lab, you will connect to a MySQL server, create a dedicated database and table, and then perform a series of hands-on tasks to build your skills in managing JSON data within a relational database context.

Connect to MySQL and Create the Database

In this first step, you will connect to the MySQL server and set up the database and table required for the lab.

First, open the terminal from your desktop.

Connect to the MySQL server with root user privileges. In this lab environment, sudo allows you to connect without a password.

sudo mysql -u root

Once connected, the command prompt will change to mysql>, indicating you are in the MySQL shell.

Next, create a new database named jsondb. The IF NOT EXISTS clause ensures the command runs without error if the database already exists.

CREATE DATABASE IF NOT EXISTS jsondb;

Now, switch to your newly created database to make it the active database for subsequent commands.

USE jsondb;

Finally, create a table named products. This table will include a column with the JSON data type to store detailed product information.

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

This statement defines a table with three columns:

• id: A unique, auto-incrementing integer for each record.
• product_name: A string for the product's name.
• product_details: A JSON column to hold structured data.

You have successfully set up the necessary database and table. Keep the MySQL shell open for the next step.

Insert and Query JSON Data

With the table created, you will now insert a record containing a JSON document and then perform a basic query to retrieve it.

In the same MySQL shell, execute the following INSERT statement to add a new product.

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 command inserts a 'Laptop' record. The product_details column is populated with a JSON object that includes nested data, such as specs.

To confirm that the data was inserted correctly, query the products table to view its contents.

SELECT * FROM products;

The output should display the row you just inserted. Note how the JSON data is stored in the product_details column.

+----+--------------+--------------------------------------------------------------------------------------------------------------------------------+
| id | product_name | product_details                                                                                                                |
+----+--------------+--------------------------------------------------------------------------------------------------------------------------------+
|  1 | Laptop       | {"brand": "Dell", "model": "XPS 13", "price": 1200, "specs": {"memory": "16GB", "storage": "512GB SSD", "processor": "Intel Core i7"}} |
+----+--------------+--------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

You have successfully inserted a record with JSON data. In the next step, you will learn how to extract specific pieces of information from this JSON object.

Extract Data from JSON Fields

Storing data in JSON is useful, but you also need to be able to query individual fields within it. In this step, you will use the JSON_EXTRACT function and JSON_UNQUOTE to pull specific values from the product_details column.

The JSON_EXTRACT function allows you to select a value from a JSON document using a path expression. The path starts with $ to represent the document root.

Let's extract the brand of the laptop.

SELECT JSON_EXTRACT(product_details, '$.brand') AS brand FROM products WHERE product_name = 'Laptop';

This query returns the brand, but notice that the result is a JSON string, which includes double quotes.

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

For a cleaner result, you can use JSON_UNQUOTE combined with JSON_EXTRACT. This combination extracts the value and removes the quotes, returning a standard string.

SELECT JSON_UNQUOTE(JSON_EXTRACT(product_details, '$.brand')) AS brand FROM products WHERE product_name = 'Laptop';

The output is now the plain text Dell.

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

You can also use path expressions to access values in nested objects. To get the processor from the specs object, use the path $.specs.processor.

SELECT JSON_UNQUOTE(JSON_EXTRACT(product_details, '$.specs.processor')) AS processor FROM products WHERE product_name = 'Laptop';

This will correctly extract the nested value.

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

These functions are also useful in WHERE clauses for filtering rows. To find all products with a price over 1000, you must CAST the extracted JSON value to a numeric type for comparison.

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

This query demonstrates how to filter records based on a numeric value inside a JSON field.

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

You now know how to extract and filter data based on JSON fields.

Update and Add JSON Fields

Data changes over time, and you need a way to modify JSON documents stored in your database. In this step, you will use the JSON_SET function to update existing values and add new key-value pairs.

The JSON_SET function modifies a JSON document by taking the target column, a path to the field, and the new value as arguments.

First, let's update the price of the laptop from 1200 to 1250.

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

To verify the change, query the price again.

SELECT JSON_UNQUOTE(JSON_EXTRACT(product_details, '$.price')) AS price FROM products WHERE product_name = 'Laptop';

The output should now show the new price.

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

If the specified path does not exist, JSON_SET will add the new key and value. Let's add a color property to the product.

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

Now, query the entire JSON object to see the newly added field.

SELECT product_details FROM products WHERE product_name = 'Laptop';

The output will show the product_details document, which now includes the color property.

+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| product_details                                                                                                                                     |
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
| {"brand": "Dell", "color": "Silver", "model": "XPS 13", "price": 1250, "specs": {"memory": "16GB", "storage": "512GB SSD", "processor": "Intel Core i7"}} |
+-----------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

You have successfully modified and extended a JSON document within a table.

Create an Index on a JSON Property

For large tables, querying JSON fields can be slow. To improve performance, you can create an index on a value extracted from a JSON column. In MariaDB, this is achieved by first adding a virtual column based on the JSON field, then creating an index on that virtual column.

In this step, you will create a virtual column for the price property and then index it to speed up price-based queries.

First, add a virtual column that extracts the price from the JSON data:

ALTER TABLE products ADD COLUMN price_virtual INT AS (CAST(JSON_UNQUOTE(JSON_EXTRACT(product_details, '$.price')) AS SIGNED)) STORED;

This command adds a virtual column named price_virtual that automatically computes and stores the price value from the JSON data.

Now create an index on this virtual column:

CREATE INDEX idx_product_price ON products (price_virtual);

This approach allows MariaDB to efficiently look up rows based on the numeric price by using the indexed virtual column.

To confirm that the index was created, use the SHOW INDEXES command.

SHOW INDEXES FROM products;

The output will list all indexes on the products table, including your new idx_product_price.

+----------+------------+-------------------+...
| Table    | Non_unique | Key_name          |...
+----------+------------+-------------------+...
| products |          0 | PRIMARY           |...
| products |          1 | idx_product_price |...
+----------+------------+-------------------+...

The most important part is to see if the optimizer uses the index. You can check this with the EXPLAIN command.

EXPLAIN SELECT product_name FROM products WHERE price_virtual > 1200;

In the EXPLAIN output, look at the possible_keys and key columns. You should see idx_product_price listed, confirming that MariaDB is using your index to execute the query efficiently.

You can also query using the original JSON expression, and MariaDB's optimizer should still be able to use the index on the virtual column:

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

You have successfully created a virtual column and indexed it to optimize JSON property queries.

You can now exit the MySQL shell.

exit

Summary

In this lab, you have gained practical experience with handling JSON data in MariaDB. You learned the complete workflow, from setting up the database structure to performing advanced operations.

You successfully inserted structured JSON data, queried specific fields using JSON_EXTRACT and JSON_UNQUOTE, and filtered records based on values within the JSON document. You also practiced modifying this data with JSON_SET to update and add new properties. Finally, you learned a key optimization technique by creating a virtual column for a JSON property and indexing it to improve query performance.

These skills are valuable for designing flexible database schemas and efficiently managing semi-structured data in MariaDB.