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.
Questo esercizio fa parte del corso
Introduction to Redshift
Istruzioni dell'esercizio
- Extract the current temperature AS
current_tempfrom the weather station data. - Extract the
'hourly_temperature'at 4AM asfour_am_tempfrom the weather station data.
Esercizio pratico interattivo
Prova a risolvere questo esercizio completando il codice di esempio.
-- 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 ___;