A Practical Guide to MySQL JSON Data Type By Example

The JSON data type in MySQL allows you to store and manipulate structured JSON data inside a database. It is useful for handling dynamic data without requiring a strict schema.

1. Why Use JSON in MySQL?

✔ Flexible Schema – Unlike normal columns, JSON allows dynamic key-value pairs.
✔ Efficient Storage – MySQL optimizes JSON storage, making it faster than TEXT columns.
✔ Built-in Functions – MySQL provides JSON functions for easy querying and manipulation.

2. Creating a Table with a JSON Column

You can define a JSON column like this:

CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    attributes JSON
);

✔ The attributes column can store any JSON object.

3. Inserting JSON Data

You must use valid JSON format when inserting data:

INSERT INTO products (name, attributes) 
VALUES ('Laptop', '{"brand": "Dell", "processor": "Intel i7", "ram": "16GB"}');

✔ JSON keys and values must be enclosed in double quotes ("").

4. Retrieving JSON Data

To fetch JSON data:

SELECT name, attributes FROM products;

To retrieve a specific JSON key:

SELECT name, attributes->>'$.brand' AS brand FROM products;

✔ This returns the "brand" value from the JSON column.

5. Updating JSON Data

Adding a New Key-Value Pair

UPDATE products 
SET attributes = JSON_SET(attributes, '$.storage', '512GB SSD') 
WHERE name = 'Laptop';

✔ Adds a new key "storage" with value "512GB SSD".

Modifying an Existing Value

UPDATE products 
SET attributes = JSON_REPLACE(attributes, '$.ram', '32GB') 
WHERE name = 'Laptop';

✔ Changes "ram" from "16GB" to "32GB".

Removing a Key from JSON

UPDATE products 
SET attributes = JSON_REMOVE(attributes, '$.processor') 
WHERE name = 'Laptop';

✔ Removes the "processor" key.

6. Filtering Data in JSON Columns

Find Products with a Specific JSON Value

SELECT * FROM products 
WHERE attributes->>'$.brand' = 'Dell';

✔ Retrieves products where "brand" is "Dell".

Check If a JSON Key Exists

SELECT * FROM products 
WHERE JSON_CONTAINS_PATH(attributes, 'one', '$.ram');

✔ Returns products that have the "ram" key.

7. Extracting JSON Arrays

Storing Arrays in JSON

INSERT INTO products (name, attributes) 
VALUES ('Smartphone', '{"brand": "Apple", "colors": ["Black", "Silver", "Blue"]}');

✔ The "colors" key contains an array.

Accessing Array Elements

SELECT attributes->>'$.colors[0]' AS first_color FROM products;

✔ Retrieves "Black" (first color).

Checking If a Value Exists in an Array

SELECT * FROM products 
WHERE JSON_CONTAINS(attributes->'$.colors', '"Silver"');

✔ Finds products where "colors" contains "Silver".

8. Validating JSON Data

To ensure a column contains valid JSON:

SELECT JSON_VALID(attributes) FROM products;

✔ Returns 1 if valid, 0 if invalid.

9. Indexing JSON Columns for Performance

MySQL does not index JSON data directly, but you can create virtual columns:

ALTER TABLE products 
ADD COLUMN brand VARCHAR(50) AS (attributes->>'$.brand') STORED,
ADD INDEX idx_brand (brand);

✔ This extracts the "brand" value into a new indexed column.

10. Summary

JSON is useful for dynamic data storage in MySQL.
MySQL provides built-in JSON functions for retrieval and modification.
You can index JSON data using virtual columns.
JSON columns must store valid JSON format.

Would you like an example of using JSON in a real-world application? 🚀

Souy Soeng

A Practical Guide to MySQL JSON Data Type By Example