Examples of MySQL JSON Data Type
Example 1
Creating a Table with JSON Column:
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(100),
details JSON
);
Inserting JSON Data:
INSERT INTO products (id, name, details)
VALUES (1, 'Product A', '{"price": 10, "category": "Electronics"}');
Querying JSON Data:
-- Retrieving product details
SELECT name, details->'$.price' AS price
FROM products
WHERE details->'$.category' = 'Electronics';
Output:
name |
price |
---|---|
Product A |
10 |
Updating JSON Data:
-- Updating product details
UPDATE products
SET details = JSON_SET(details, '$.price', 15)
WHERE id = 1;
Indexing JSON Column:
-- Creating an index on JSON column
CREATE INDEX idx_category ON products ((details->'$.category'));
Example 2
Creating a Table with JSON Column
Let’s create a table named employees
with a JSON column to store additional details about each employee.
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(100),
details JSON
);
Inserting JSON Data:
INSERT INTO employees (id, name, details)
VALUES
(1, 'Alice Smith', '{"age": 30, "department": "HR", "skills": ["recruiting", "communication"]}'),
(2, 'Bob Johnson', '{"age": 35, "department": "IT", "skills": ["programming", "networking"]}');
Querying JSON Data:
Retrieve the names and departments of employees who have a specific skill:
SELECT name, details->'$.department' AS department
FROM employees
WHERE JSON_CONTAINS(details->'$.skills', '"programming"');
Output:
name department
Bob Johnson IT
Updating JSON Data
Update the JSON data to add a new skill for an employee:
UPDATE employees
SET details = JSON_ARRAY_APPEND(details, '$.skills', 'management')
WHERE id = 1;
Querying to Verify the Update
Retrieve the updated details for the employee to verify the changes:
SELECT name, details
FROM employees
WHERE id = 1;
Expected Output:
name details
Alice Smith {"age": 30, "department": "HR", "skills": ["recruiting", "communication", "management"]}
What is MySQL JSON Data Type?
The MySQL JSON data type is a significant advancement in database management, especially in meeting the needs of contemporary online applications that require dynamic and flexible data storage. JavaScript Object Notation, or JSON, is a widely used format for data transfer because it is easy to read, simple to use, and compatible with a wide range of platforms and programming languages
Contact Us