How to Copy Json Data to an Amazon Redshift Table

Here we show how to load JSON data into Amazon Redshift. In this example, Redshift parses the JSON data into individual columns. (It is possible to store JSON in char or varchar columns, but that’s another topic.)

First, review this introduction on how to stage the JSON data in S3 and instructions on how to get the Amazon IAM role that you need to copy the JSON file to a Redshift table.

In this example, we load 20 years of temperature data for Paphos, Cyprus. We purchased that data for $10 from OpenWeather. Of course, you could use any data.

Create a Redshift Table

First we create a table. We only want the date and these three temperature columns. We will give Redshift a JSONParse parsing configuration file, telling it where to find these elements so it will discard the others.

create table paphos (
dt_iso timestamp not null distkey sortkey,
temp real,
temp_min  real,
temp_max real
);

The weather data looks like this:

{
"city_name": "Paphos Castle",
"lat": 34.753637,
"lon": 32.406951,
"main": {
"temp": 55.35,
"temp_min": 51.8,
"temp_max": 65.53,
"feels_like": 49.44,
"pressure": 1016,
"humidity": 73
},
"wind": {
"speed": 9.17,
"deg": 20
},
"clouds": {
"all": 1
},
"weather": [{
"id": 800,
"main": "Clear",
"description": "sky is clear",
"icon": "01n"
}],
"dt": 946684800,
"timezone": 7200
}

Here is one record in the JSON Power Editor for Mac.

Note: I recommend this editor if you work with JSON a lot, as it makes editing JSON files a lot easier. You can work with objects in the right-hand screen which will create the text in the left-hand screen. That saves you the trouble of having to fix syntax error and line up curly brackets.

Create JSONPath file

We create a JSONPath file, which tells Redshift which elements to get. We have to give it the path of the item all the way down to the item. In other words, we can’t put just the top-level key weather and it will get temp, temp_min, and temp_max. We have to give it the full path of JSON keys main->temp.

We don’t have any arrays in this example, but it supports that using [array index] notation.

{
"jsonpaths": [
"$['dt_iso']",
"$['main']['temp']",
"$['main']['temp_min']",
"$['main']['temp_max']"
]
}