Working with JSON in MySQL

In modern application development, managing semi-structured data is a common challenge. MySQL's support for JSON, introduced in version 5.7, provides a powerful way to store, query, and manipulate such data within a relational database. This post explores the essential JSON functions MySQL offers with practical examples to help get started.

Why JSON in MySQL?

Storing JSON data in relational databases can simplify the handling of semi-structured or hierarchical data. It allows:

• Flexibility: JSON allows for dynamic and hierarchical data storage.
• Built-in Functions: Query, update, and validate JSON data efficiently.
• Integration: Combine relational data with JSON objects for hybrid use cases.

1. Creating JSON Data

You can construct JSON objects or arrays using the JSON_OBJECT() and JSON_ARRAY() functions.

Example:

SELECT 
    JSON_OBJECT('id', 1, 'name', 'Alice', 'roles', JSON_ARRAY('admin', 'editor')) AS json_data;

Output:

{"id": 1, "name": "Alice", "roles": ["admin", "editor"]}

2. Storing JSON Data

To store JSON data, use the JSON data type for columns.

Example:

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    details JSON NOT NULL
);

INSERT INTO users (details) 
VALUES ('{"name": "Bob", "age": 30, "roles": ["viewer", "editor"]}');

3. Extracting Data from JSON

MySQL provides several functions to extract data from JSON documents:

• JSON_EXTRACT(): Fetch values using JSON paths.
• -> Operator: Shorthand for JSON_EXTRACT().

Example:

SELECT 
    JSON_EXTRACT(details, '$.name') AS name,
    details->'$.age' AS age
FROM users;

Output:

4. Modifying JSON Data

You can update or add elements to JSON data with:

• JSON_SET(): Insert or update keys.
• JSON_INSERT(): Insert if key doesn’t exist.
• JSON_REPLACE(): Update existing keys only.

Example:

UPDATE users
SET details = JSON_SET(details, '$.city', 'New York')
WHERE id = 1;

SELECT details FROM users;

Output:

{"name": "Bob", "age": 30, "roles": ["viewer", "editor"], "city": "New York"}

5. Removing Keys or Values

Use JSON_REMOVE() to delete elements from a JSON document.

Example:

UPDATE users
SET details = JSON_REMOVE(details, '$.roles')
WHERE id = 1;

SELECT details FROM users;

Output:

{"name": "Bob", "age": 30, "city": "New York"}

6. Searching in JSON

The JSON_CONTAINS() function checks if a JSON document contains a specific value.

Example:

SELECT 
    JSON_CONTAINS(details, '"New York"', '$.city') AS has_city
FROM users;

Output:

7. Aggregating JSON Data

The JSON_ARRAYAGG() and JSON_OBJECTAGG() functions help aggregate query results into JSON structures.

Example:

SELECT 
    JSON_ARRAYAGG(name) AS names
FROM (
    SELECT JSON_EXTRACT(details, '$.name') AS name FROM users
) AS subquery;

Output:

["Bob"]

8. Validating JSON Data

The JSON_VALID() function checks whether a string is valid JSON.

Example:

SELECT 
    JSON_VALID('{"key": "value"}') AS is_valid,
    JSON_VALID('Invalid JSON') AS is_invalid;

Output:

9. Pretty Printing JSON

Use JSON_PRETTY() for human-readable JSON formatting.

Example:

SELECT JSON_PRETTY(details) AS pretty_json FROM users;

Output:

{
    "name": "Bob",
    "age": 30,
    "city": "New York"
}

Additional JSON Functions

MySQL offers a wide range of JSON functions beyond those covered in detail. Below are other JSON functions:

• JSON_ARRAY_APPEND(): Appends values to the end of a JSON array.
• JSON_ARRAY_INSERT(): Inserts values into a JSON array at specified positions.
• JSON_CONTAINS_PATH(): Checks if a specified path exists within a JSON document.
• JSON_DEPTH(): Returns the maximum depth of a JSON document.
• JSON_KEYS(): Extracts the keys from a JSON object.
• JSON_LENGTH(): Calculates the number of elements in a JSON array or the number of keys in a JSON object.
• JSON_MERGE_PATCH(): Combines multiple JSON documents, overwriting existing keys with the latest values.
• JSON_MERGE_PRESERVE(): Combines multiple JSON documents, preserving all values by creating arrays for duplicates.
• JSON_OVERLAPS(): Determines if two JSON documents have any overlapping elements.
• JSON_QUOTE(): Escapes special characters in a string and returns it as a valid JSON string literal.
• JSON_SEARCH(): Returns the path to a specified value within a JSON document.
• JSON_STORAGE_FREE(): Returns the amount of space freed after modifying a JSON column.
• JSON_STORAGE_SIZE(): Provides the storage size of a JSON document in bytes.
• JSON_TABLE(): Converts JSON data into a relational table format for further processing.
• JSON_TYPE(): Returns the type of a JSON value (e.g., object, array, string, etc.).
• JSON_UNQUOTE(): Removes quotes from a JSON string.

MySQL’s JSON functions provide powerful tools for managing semi-structured data in relational databases. They make it easy to store, query, and manipulate JSON directly within SQL. Understanding these functions can help simplify workflows and open up new approaches to database design.