Copy data from a JSON file to Redshift using the COPY command

Question

I'm trying to load a JSON file into Redshift using the COPY command together with a JSONPath. From what I understood, for each record in the JSON file, the COPY command generates one record to SQL.

I need to generate multiple records to SQL from one record in JSON, but I am unclear how to do that.

Here is an example. Say we have following JSON file:

{
    {
        "id": 1,
        "value": [1, 2, 3, 4],
        "other": "ops"
    },
    {
        "id": 2,
        "value": [5, 6, 7, 8]
    }
}

I want to generate following rows to store in SQL:

What the should the JSONPath file should look like? Is it doable or not?

In a related SO post, the solution is to somehow generate data with right schema before it loading into Redshift. I could preprocess the JSON file to flatten it out somehow and store it back to S3. But that complicates things a lot.

Another related question is, how could I set a default value if one field is missing in one record (e.g. the "other" field in the second record of the aforementioned example)?

Yuri Levinsky · Accepted Answer · 2015-01-06 11:51:35Z

2

You can't perform transformation in copy command. Use ETL tools instead of direct copy to RedShift. Once you use JSON format the default value will be assigned on basis of table DDL.

answered Jan 6, 2015 at 11:51

Yuri Levinsky

1,5952 gold badges14 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Copy data from a JSON file to Redshift using the COPY command

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related