Working with JSON in SQL

Modern applications often work with semi-structured data such as JSON.

In this lesson, we learn how SQL (MySQL) can store, read, and query JSON data directly inside database tables.

What is JSON?

JSON (JavaScript Object Notation) is a lightweight data format used to store and exchange data.

It represents data as:

• Key–value pairs
• Objects
• Arrays

Example JSON:

{
  "name": "Alice",
  "role": "Developer",
  "skills": ["SQL", "Python", "Cloud"]
}
  

Why Use JSON in SQL?

JSON support allows databases to:

• Store flexible schemas
• Handle dynamic attributes
• Integrate easily with APIs
• Support modern web applications

MySQL provides native JSON support with optimized storage and functions.

JSON Data Type

MySQL provides a native JSON data type.

CREATE TABLE users (
  id INT PRIMARY KEY,
  profile JSON
);
  

The database automatically validates JSON structure.

Inserting JSON Data

INSERT INTO users VALUES (
  1,
  '{"name":"Alice","age":30,"city":"New York"}'
);
  

Reading JSON Data

Use JSON path expressions to access values.

SELECT profile->'$.name' AS name
FROM users;
  

To remove quotes:

SELECT profile->>'$.name' AS name
FROM users;
  

Accessing Nested JSON Values

SELECT profile->>'$.address.city' AS city
FROM users;
  

JSON paths use dot notation.

Working with JSON Arrays

Access array elements using indexes.

SELECT profile->>'$.skills[0]' AS first_skill
FROM users;
  

JSON Functions

MySQL provides several JSON functions.

JSON_EXTRACT Example

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

Updating JSON Values

Use JSON_SET to modify JSON fields.

UPDATE users
SET profile = JSON_SET(profile, '$.city', 'Boston')
WHERE id = 1;
  

Checking JSON Values

Use JSON functions in WHERE clauses.

SELECT *
FROM users
WHERE profile->>'$.city' = 'Boston';
  

JSON vs Relational Columns

Best Practices

• Use JSON for flexible or optional fields
• Avoid overusing JSON for core data
• Index JSON paths when needed
• Validate JSON structure carefully

Common Beginner Mistakes

• Storing everything as JSON
• Ignoring performance impact
• Incorrect JSON path syntax
• Forgetting quotes in JSON keys

Real-World Use Cases

• User profile data
• Application settings
• API payload storage
• Event metadata

What’s Next?

In the next lesson, we will explore Dynamic SQL, which allows SQL statements to be built and executed at runtime.