📋 JSON in SQL = Modern Data
JSON is everywhere. SQL JSON functions work with JSON data. Store, query, modify — flexible data.
📝 JSON Functions
# Store JSON
CREATE TABLE users (
id INT PRIMARY KEY,
name VARCHAR(100),
metadata JSON
);
INSERT INTO users (id, name, metadata)
VALUES (
1,
'Alice',
'{"age": 30, "city": "NYC", "preferences": {"theme": "dark"}}'
);
# Query JSON
-- Extract value
SELECT JSON_EXTRACT(metadata, '$.age') as age
FROM users;
-- Or with -> operator
SELECT metadata->>'$.age' as age
FROM users;
# JSON functions
SELECT
JSON_TYPE(metadata) as type,
JSON_KEYS(metadata) as keys,
JSON_LENGTH(metadata) as length
FROM users;
# Update JSON
UPDATE users
SET metadata = JSON_SET(metadata, '$.city', 'LA')
WHERE id = 1;
# Add to JSON
UPDATE users
SET metadata = JSON_INSERT(metadata, '$.country', 'USA');
# Remove from JSON
UPDATE users
SET metadata = JSON_REMOVE(metadata, '$.preferences.theme');
# Search JSON
SELECT * FROM users
WHERE JSON_CONTAINS(metadata, '"NYC"', '$.city');
🎯 Advanced JSON Usage
# JSON array
INSERT INTO orders (id, items)
VALUES (
1,
'[
{"product": "Laptop", "qty": 1, "price": 999},
{"product": "Mouse", "qty": 2, "price": 25}
]'
);
# Query array
SELECT JSON_EXTRACT(items, '$[0].product') as first_product
FROM orders;
# Array functions
SELECT
JSON_ARRAY_LENGTH(items) as item_count,
JSON_EXTRACT(items, '$[*].product') as products
FROM orders;
# JSON_TABLE (SQL Server)
SELECT *
FROM orders,
JSON_TABLE(items, '$[*]' COLUMNS(
product VARCHAR(100) PATH '$.product',
qty INT PATH '$.qty',
price DECIMAL(10,2) PATH '$.price'
)) AS jt;
# JSON_OBJECT (MySQL)
SELECT
JSON_OBJECT(
'id', id,
'name', name,
'age', JSON_EXTRACT(metadata, '$.age')
) as user_json
FROM users;
# JSON_ARRAY
SELECT JSON_ARRAY(name, metadata) as user_data
FROM users;
# Index JSON (MySQL 8.0+)
ALTER TABLE users
ADD INDEX idx_city ((CAST(metadata->>'$.city' AS CHAR(50))));
💡 JSON Tips
- Use JSON for flexible schemas
- Index JSON fields for performance
- Validate JSON before storing
- Use JSON with NoSQL patterns
- Consider size limits for JSON
“JSON in SQL enables modern data patterns. Flexible, powerful. Essential for modern databases.”
