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.

This exercise is part of the course

Introduction to Redshift

View Course

Exercise instructions

Extract the current temperature AS current_temp from the weather station data.
Extract the 'hourly_temperature' at 4AM as four_am_temp from the weather station data.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

-- 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 ___;

Edit and Run Code