Intermediate LevelSQL & MySQL
How does the JSON Data Type and JSON_EXTRACT / -> operator work in MySQL 8+?
❓ Interview Prompt: Explain MySQL JSON data type, `JSON_EXTRACT()`, `->` and `->>` inline path operators, JSON indexing via functional indexes, and document updates.
MySQL 8+ supports a native 'JSON' data type that stores binary-encoded JSON documents with automatic validation:
Operators:
•
'->' (Extract): Returns JSON-formatted value (includes quotes: '"value"').
•
'->>' (Extract Unquote): Returns unquoted scalar value ('value').
•
Indexing: Index JSON fields using MySQL 8 Functional Indexes ('CREATE INDEX idx ON table ((CAST(data->>'$.key' AS UNSIGNED)))').
Code Implementation Example
sql
-- Querying nested JSON document fields using ->> operator
SELECT
id,
attributes->>'$.color' AS color,
CAST(attributes->>'$.weight' AS DECIMAL(10, 2)) AS weight
FROM products
WHERE attributes->>'$.status' = 'in_stock';💡Extracting nested JSON document fields using the ->> operator in MySQL 8.
🎯 Interview Key Takeaway
Use the ->> operator to extract unquoted scalar values from JSON columns, and functional indexes to accelerate JSON queries.
#MySQL#JSON#JSON_EXTRACT#NoSQL in SQL#Intermediate
Back to all SQL & MySQL Questions→