Extracting top level JSON keys

The Idaho field team is back at it again, they got a new weather station that is outputting JSON strings that they'd like you to get data out of for them. The JSON is in the CTE named weather_station for you to work with. It has the JSON string in the data column.

This exercise is part of the course

Introduction to Redshift

View Course

Exercise instructions

Extract the 'date' value from the data as weather_date.
Extract the 'weather' value from the data as weather_state.

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 date value
SELECT ___(___, ___) AS ___,
       -- Extract the weather value
       ___(___, ___) AS ___
  -- Using the CTE
  FROM ___;

Edit and Run Code