JSON Operations in MySQL: Examples and Use Cases

Introduction

MySQL has supported JSON data types since version 5.7, allowing you to store, query, and manipulate JSON data directly within your database. This feature is powerful for applications that need to manage complex, hierarchical data structures, and it offers a flexible way to handle semi-structured data.

This guide will provide multiple examples of how to perform various JSON operations in MySQL, including querying, updating, and indexing JSON data.

Example 1: Creating a Table with JSON Data

Let’s start by creating a table that includes a JSON column.

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

Example 2: Inserting JSON Data

You can insert JSON data into a JSON column directly as a valid JSON string.

INSERT INTO users (name, preferences) 
VALUES ('John Doe', '{"theme": "dark", "notifications": {"email": true, "sms": false}}');

Example 3: Querying JSON Data

You can extract data from a JSON column using the -> or ->> operators.

• Retrieve a JSON object or array:

SELECT preferences->'$.theme' AS theme 
FROM users 
WHERE name = 'John Doe';

• Retrieve a scalar value from a JSON object:

SELECT preferences->>'$.notifications.email' AS email_notifications 
FROM users 
WHERE name = 'John Doe';

Example 4: Updating JSON Data

You can update specific parts of a JSON document using the JSON_SET function.

UPDATE users 
SET preferences = JSON_SET(preferences, '$.theme', 'light') 
WHERE name = 'John Doe';

This command updates the theme key inside the JSON document to light.

Example 5: Adding New Key-Value Pairs to JSON Data

Use the JSON_INSERT function to add new key-value pairs without overwriting existing data.

UPDATE users 
SET preferences = JSON_INSERT(preferences, '$.language', 'en') 
WHERE name = 'John Doe';

Example 6: Removing Key-Value Pairs from JSON Data

You can remove specific keys from a JSON object using the JSON_REMOVE function.

UPDATE users 
SET preferences = JSON_REMOVE(preferences, '$.notifications.sms') 
WHERE name = 'John Doe';

Example 7: Merging JSON Documents

To merge two JSON documents, use the JSON_MERGE_PATCH function (MySQL 8.0) or JSON_MERGE (MySQL 5.7).

• Using JSON_MERGE_PATCH (MySQL 8.0):

UPDATE users 
SET preferences = JSON_MERGE_PATCH(preferences, '{"layout": "grid"}') 
WHERE name = 'John Doe';

• Using JSON_MERGE (MySQL 5.7):

UPDATE users 
SET preferences = JSON_MERGE(preferences, '{"layout": "grid"}') 
WHERE name = 'John Doe';

Example 8: Checking if a Key Exists

You can check if a specific key exists in a JSON document using the JSON_CONTAINS_PATH function.

SELECT name 
FROM users 
WHERE JSON_CONTAINS_PATH(preferences, 'one', '$.theme');

This query selects users where the theme key exists in their preferences.

Example 9: Extracting an Array from JSON Data

You can extract arrays and work with individual elements.

INSERT INTO users (name, preferences) 
VALUES ('Jane Smith', '{"theme": "dark", "languages": ["en", "es", "fr"]}');

To retrieve the entire array:

SELECT preferences->'$.languages' AS languages 
FROM users 
WHERE name = 'Jane Smith';

To retrieve a specific element in the array:

SELECT preferences->>'$.languages[1]' AS second_language 
FROM users 
WHERE name = 'Jane Smith';

Example 10: Searching Within JSON Arrays

To check if a value exists within a JSON array, use the JSON_CONTAINS function.

SELECT name 
FROM users 
WHERE JSON_CONTAINS(preferences->'$.languages', '"es"');

This query returns users who have "es" (Spanish) in their languages array.

Example 11: Creating Indexes on JSON Columns

Indexing JSON data can improve query performance. MySQL allows indexing JSON values by creating generated columns.

ALTER TABLE users 
ADD COLUMN theme VARCHAR(255) AS (preferences->>'$.theme'),
ADD INDEX (theme);

This command creates a virtual column theme extracted from the JSON data and indexes it.

Example 12: Aggregating JSON Data

You can aggregate JSON data using functions like JSON_ARRAYAGG or JSON_OBJECTAGG.

• Aggregate values into a JSON array:

SELECT JSON_ARRAYAGG(name) AS user_names 
FROM users;

• Aggregate key-value pairs into a JSON object:

SELECT JSON_OBJECTAGG(id, name) AS users_object 
FROM users;

Example 13: Validating JSON Data

Ensure that a column contains valid JSON using the JSON_VALID function.

SELECT name 
FROM users 
WHERE JSON_VALID(preferences);

This query returns users who have valid JSON in their preferences column.

Example 14: Using JSON with Joins

You can join tables based on JSON values, although this is more complex.

CREATE TABLE orders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    details JSON
);

INSERT INTO orders (user_id, details) 
VALUES (1, '{"item": "laptop", "quantity": 1}');

To join the users and orders tables based on JSON data:

SELECT u.name, o.details->>'$.item' AS item 
FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE o.details->>'$.item' = 'laptop';

Example 15: Combining Multiple JSON Functions

You can combine multiple JSON functions for more complex operations.

SELECT JSON_UNQUOTE(JSON_EXTRACT(preferences, '$.theme')) AS theme 
FROM users 
WHERE JSON_CONTAINS_PATH(preferences, 'one', '$.theme');

This query extracts the theme key, removes the quotes, and ensures the key exists.

Conclusion

These examples illustrate the powerful capabilities of MySQL's JSON functions, enabling you to store, query, and manipulate JSON data directly in the database. Whether you're handling complex data structures or just need flexible data storage, MySQL's JSON support can be a valuable tool in your database management toolkit.