MySQL JSON Support – Store, Query, and Manipulate JSON Data in SQL

Introduction – Why Use JSON in MySQL?

JSON (JavaScript Object Notation) is a flexible and lightweight data format used widely in APIs, configurations, and unstructured data storage. Since MySQL 5.7, native support for JSON data types and functions enables structured querying of semi-structured data—bringing NoSQL-like capabilities into a relational system.

In this guide, you’ll learn:

• How to define JSON columns in MySQL
• How to insert and query JSON data
• Use powerful JSON functions like JSON_EXTRACT(), JSON_SET()
• Indexing, validation, and performance tips

Declaring JSON Columns

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  info JSON
);

info is a native JSON column.

Stored efficiently as a binary JSON format
Validated to ensure well-formed JSON

Inserting JSON Data

INSERT INTO users (id, name, info)
VALUES (
  1,
  'Alice',
  JSON_OBJECT('email', 'alice@example.com', 'age', 30, 'active', true)
);

Explanation:

• JSON_OBJECT(...): Creates a JSON object within SQL
• Values like strings, numbers, booleans are supported

Querying JSON Data

1. Using -> (shorthand operator)

SELECT info->'$.email' AS email FROM users;

2. Using JSON_EXTRACT()

SELECT JSON_EXTRACT(info, '$.age') AS age FROM users;

3. Using ->> to return unquoted scalar values

SELECT info->>'$.active' AS is_active FROM users;

Modifying JSON Fields

JSON_SET() – Add or update key

UPDATE users 
SET info = JSON_SET(info, '$.location', 'India')
WHERE id = 1;

JSON_REMOVE() – Delete a key

UPDATE users 
SET info = JSON_REMOVE(info, '$.active')
WHERE id = 1;

JSON Search & Filter Examples

Find users with specific key/value:

SELECT * FROM users
WHERE JSON_EXTRACT(info, '$.age') > 25;

Or shorthand:

SELECT * FROM users
WHERE info->>'$.age' > 25;

Check if key exists using JSON_CONTAINS_PATH

SELECT * FROM users 
WHERE JSON_CONTAINS_PATH(info, 'one', '$.email');

Match a nested object

SELECT * FROM users 
WHERE JSON_CONTAINS(info, '{"active": true}');

JSON Array Functions

Store array of values

INSERT INTO users (id, name, info)
VALUES (2, 'Bob', JSON_OBJECT('skills', JSON_ARRAY('SQL', 'Python')));

Access array values

SELECT JSON_EXTRACT(info, '$.skills[0]') AS first_skill FROM users;

Get array length

SELECT JSON_LENGTH(info->'$.skills') AS total_skills FROM users;

Indexing JSON Data (Generated Columns)

MySQL does not allow direct indexing of JSON, but you can index virtual columns derived from JSON.

ALTER TABLE users 
ADD email_generated VARCHAR(255) 
    GENERATED ALWAYS AS (info->>'$.email') STORED,
ADD INDEX idx_email (email_generated);

You can now filter by email efficiently:

SELECT * FROM users WHERE email_generated = 'alice@example.com';

JSON Validation & Type Safety

Validate JSON syntax

SELECT JSON_VALID('{"email": "user@example.com"}'); -- returns 1
SELECT JSON_VALID('{email: user@example.com}');     -- returns 0

Convert Between JSON and SQL

JSON to Table (MySQL 8+)

SELECT *
FROM JSON_TABLE(
  '[{"id":1,"name":"Alice"},{"id":2,"name":"Bob"}]',
  "$[*]" COLUMNS (
    id INT PATH "$.id",
    name VARCHAR(100) PATH "$.name"
  )
) AS jt;

Converts a JSON array to a tabular format using JSON_TABLE

Useful JSON Functions

Best Practices

Use JSON when schema flexibility is needed
Use STORED GENERATED COLUMNS to enable indexing
Avoid deeply nested JSON for performance-sensitive queries
Use ->> when you need raw values (e.g., numbers, booleans)
Prefer SQL columns for structured data when possible

Summary – Recap & Next Steps

MySQL JSON support allows you to flexibly store, query, and manipulate semi-structured data while maintaining the power of SQL. Whether you’re building an API backend or working with unpredictable user data, JSON columns can be a game changer.

Key Takeaways

• Use JSON_OBJECT, JSON_EXTRACT, JSON_SET, and JSON_TABLE to work with JSON
• Use generated columns to index and filter JSON efficiently
• JSON support is native, type-safe, and fully integrated into MySQL 5.7+

Real-World Relevance
JSON is now a critical component in eCommerce apps, RESTful APIs, config stores, and logging systems—all of which benefit from MySQL’s native JSON functions.

FAQ – MySQL JSON Support

What version of MySQL introduced JSON?
Native JSON support was added in MySQL 5.7.

Can I index JSON fields directly?
No. You need to use generated virtual columns for indexing.

What’s the difference between -> and ->>?
-> returns JSON value; ->> returns unquoted plain value.

How can I convert JSON array to rows?
Use JSON_TABLE() in MySQL 8.0 or split manually in older versions.

Is JSON storage slower than traditional columns?
It depends. For large data sets, deeply nested JSON can be slower to query. Use structured columns where performance is critical.