Extracting nested JSON keys
The field team would now like you to extract the current temperature and the temperature at 4AM. They said the hour_temperature key contains an array that starts at 12AM or midnight. The JSON is in the CTE for you again.
Este ejercicio forma parte del curso
Introduction to Redshift
Instrucciones del ejercicio
- Extract the current temperature AS
current_tempfrom the weather station data. - Extract the
'hourly_temperature'at 4AM asfour_am_tempfrom the weather station data.
Ejercicio interactivo práctico
Prueba este ejercicio y completa el código de muestra.
-- weather_station CTE with the JSON in the data column
WITH weather_station AS (
SELECT '
{
"location": "Salmon Challis National Forest",
"date": "2024-02-10",
"weather": "Rainy",
"temperature": {
"current": 10,
"min": 8,
"max": 12,
"hourly_temperature": [8, 8, 9, 9, 10, 10, 11, 11, 12]
}
}'::SUPER::VARCHAR as data
-- Above line casts to SUPER and then to
-- VARCHAR to ensure it's ready for parsing
)
-- Extract the current temperature
SELECT ___(data, ___, ___) AS current_temp,
-- Extract the hourly_temperature at 4AM
___(data, ___,___, ___) as four_am_temp
-- Use the CTE
FROM ___;