MySQL JSON Support

MySQL 5.7 introduced a native JSON data type and a comprehensive set of JSON functions. This allows storing, querying, and manipulating JSON (JavaScript Object Notation) data directly in MySQL — combining the flexibility of document-style storage with the power of a relational database.

What is JSON?

JSON is a lightweight format for storing structured data as key-value pairs. It is widely used in web APIs, configuration files, and modern applications. A JSON object looks like:

{"name": "Ravi Kumar", "city": "Delhi", "skills": ["MySQL", "Python"]}

The JSON Data Type

Storing data in a JSON column offers two advantages over storing JSON in a TEXT column:

• MySQL validates the JSON on insert — invalid JSON is rejected.
• JSON functions can directly query and modify the stored data.

Creating a Table with JSON Column

CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_name VARCHAR(100),
    details JSON
);

Inserting JSON Data

INSERT INTO employees (emp_name, details) VALUES
('Ravi Kumar', '{"city": "Delhi", "age": 30, "skills": ["MySQL", "Python"]}'),
('Sneha Joshi', '{"city": "Mumbai", "age": 27, "skills": ["Java", "Spring"]}'),
('Karan Das', '{"city": "Delhi", "age": 35, "skills": ["MySQL", "AWS"]}');

Extracting JSON Values with -> and ->>

The -> operator extracts a value from JSON (returns it with quotes). The ->> operator extracts it as plain text (without quotes).

-- With quotes
SELECT emp_name, details->'$.city' AS city FROM employees;

-- Without quotes (cleaner output)
SELECT emp_name, details->>'$.city' AS city FROM employees;

Result:

emp_name    | city
------------+--------
Ravi Kumar  | Delhi
Sneha Joshi | Mumbai
Karan Das   | Delhi

JSON_EXTRACT()

JSON_EXTRACT() is equivalent to using ->.

SELECT emp_name, JSON_EXTRACT(details, '$.age') AS age FROM employees;

Filtering with JSON Values in WHERE

SELECT emp_name
FROM employees
WHERE details->>'$.city' = 'Delhi';

JSON_ARRAYAGG() and JSON_OBJECTAGG()

-- Build a JSON array of all employee names
SELECT JSON_ARRAYAGG(emp_name) AS all_names FROM employees;

-- Build a JSON object of emp_id: emp_name pairs
SELECT JSON_OBJECTAGG(emp_id, emp_name) AS emp_map FROM employees;

Modifying JSON with JSON_SET()

UPDATE employees
SET details = JSON_SET(details, '$.age', 31)
WHERE emp_name = 'Ravi Kumar';

Adding a New Key with JSON_SET()

UPDATE employees
SET details = JSON_SET(details, '$.department', 'IT')
WHERE emp_name = 'Ravi Kumar';

Removing a Key with JSON_REMOVE()

UPDATE employees
SET details = JSON_REMOVE(details, '$.age')
WHERE emp_name = 'Sneha Joshi';

Working with JSON Arrays

-- Get the first skill
SELECT emp_name, details->>'$.skills[0]' AS first_skill FROM employees;

-- Check if a value exists in a JSON array
SELECT emp_name
FROM employees
WHERE JSON_CONTAINS(details->'$.skills', '"MySQL"');

JSON_VALID()

SELECT JSON_VALID('{"name": "Ravi"}');   -- 1 (valid)
SELECT JSON_VALID('{name: Ravi}');       -- 0 (invalid)

Common JSON Functions Summary

Key Points

• MySQL's native JSON type validates JSON on insert and enables direct querying of JSON fields.
• Use ->> to extract JSON values as clean text without surrounding quotes.
• JSON_SET() adds or updates keys; JSON_REMOVE() deletes keys from stored JSON.
• JSON_CONTAINS() checks whether a value exists within a JSON array or object.
• JSON columns in MySQL allow semi-structured data storage without sacrificing relational query capabilities.