🧱 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

Function	Description
JSON_OBJECT()	Create a JSON object
JSON_ARRAY()	Create a JSON array
JSON_EXTRACT()	Get value by path ($)
JSON_SET()	Insert or update value at path
JSON_REMOVE()	Delete key from object
JSON_CONTAINS()	Check if JSON contains specific value
JSON_MERGE_PATCH()	Merge objects and arrays
JSON_UNQUOTE()	Remove quotes from string

---

📘 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.

---

Share Now :