Home >Database >Mysql Tutorial >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.
Storing JSON data in relational databases can simplify the handling of semi-structured or hierarchical data. It allows:
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"]}
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"]}');
MySQL provides several functions to extract data from JSON documents:
Example:
SELECT JSON_EXTRACT(details, '$.name') AS name, details->'$.age' AS age FROM users;
Output:
You can update or add elements to JSON data with:
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"}
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"}
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:
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:
SELECT JSON_OBJECT('id', 1, 'name', 'Alice', 'roles', JSON_ARRAY('admin', 'editor')) AS json_data;
The JSON_VALID() function checks whether a string is valid JSON.
Example:
{"id": 1, "name": "Alice", "roles": ["admin", "editor"]}
Output:
Use JSON_PRETTY() for human-readable JSON formatting.
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"]}');
Output:
SELECT JSON_EXTRACT(details, '$.name') AS name, details->'$.age' AS age FROM users;
MySQL offers a wide range of JSON functions beyond those covered in detail. Below are other JSON functions:
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.
The above is the detailed content of Working with JSON in MySQL. For more information, please follow other related articles on the PHP Chinese website!