Interactive Lesson: Advanced SQL Space Station
🚀 SQL Space Station
Master Advanced SQL Through Interactive Missions
JSON Extraction
JSON_EXTRACT basics
Window Rankings
RANK() & ROW_NUMBER()
Array Aggregation
ARRAY_AGG() mastery
QUALIFY Filtering
Advanced filtering
Complex Nested
JSON + Arrays
Final Challenge
All techniques
📡 Mission 1: JSON Data Extraction
🎯 MISSION OBJECTIVE:
Extract temperature and location data from JSON sensor readings stored in the sensor_data column.
📊 AVAILABLE DATA – Table: sensor_telemetry
JSON Structure Breakdown:
| sensor_id | sensor_data (JSON) | timestamp |
|---|---|---|
| TEMP-01 | {“readings”: {“temperature”: 22.5, “pressure”: 101.3}, “location”: “Module-A”, “status”: “active”} | 2024-01-15 10:00 |
| TEMP-02 | {“readings”: {“temperature”: 23.1, “pressure”: 101.5}, “location”: “Module-B”, “status”: “active”} | 2024-01-15 10:15 |
| TEMP-03 | {“readings”: {“temperature”: 21.8, “pressure”: 101.2}, “location”: “Module-C”, “status”: “warning”} | 2024-01-15 10:30 |
{
“readings”: {
“temperature”: 22.5, // ← Extract this using $.readings.temperature
“pressure”: 101.3
},
“location”: “Module-A”, // ← Extract this using $.location
“status”: “active”
}
🛠️ SQL FUNCTIONS TOOLKIT:
💡 HINT:
To extract the temperature: JSON_EXTRACT(sensor_data, ‘$.readings.temperature’) AS temperature
To extract the temperature: JSON_EXTRACT(sensor_data, ‘$.readings.temperature’) AS temperature
📝 SOLUTION:
SELECT
sensor_id,
JSON_EXTRACT(sensor_data, '$.readings.temperature') AS temperature,
JSON_EXTRACT_SCALAR(sensor_data, '$.location') AS location
FROM sensor_telemetry
WHERE timestamp >= '2024-01-01';
⚠️ ERROR:
Your query needs adjustment.
Your query needs adjustment.
🎉 MISSION COMPLETE!
Excellent work! You’ve mastered JSON extraction!