Is it possible to parse this JSON array and convert it into a table and do this in AWS Redshift?
This array:
[
{"Event":"start","EventDateTime":"2015-09-15T03:45:16.681428Z"},
{"Event":"process","EventDateTime":"2015-09-15T03:45:16.681428Z"},
{"Event":"end","EventDateTime":"2015-09-15T03:45:16.681428Z"}
]
Convert to a table with Event and EventDateTime fields.
I have tried the available json functions and string functions and have come up empty.
Thanks!
To perform a Redshift COPY from JSON Format, you must prepare a JSON data structure. The JSON data structure is made up of a set of objects or arrays.
In your example:
Create following data set and upload it to S3:
{"event":"start","eventdatetime":"2015-09-15T03:45:16.681428Z"}
{"event":"process","eventdatetime":"2015-09-15T03:45:16.681428Z"}
{"event":"end","eventdatetime":"2015-09-15T03:45:16.681428Z"}
Create table on your cluster
CREATE TABLE events (event VARCHAR, eventdatetime TIMESTAMP);
Run the COPY command
COPY events FROM 's3://bucket/file' JSON 'auto' TIMEFORMAT 'auto' CREDENTIALS 'aws_access_key_id=???;aws_secret_access_key=???';
Test the result
SELECT * FROM events;
Please, keep in mind that in this example will use 'auto' mapping feature, and because column names are always lowercase, matching field names must also be lowercase.. If it's not an option, you can overcome this limitation using a JSONPaths file.
Your issue relates to the square brackets surrounding the json documents, if you're able to remove those with pre-processing, you'll be able to use the copy command to get what you need.
You can use copy command to load the table up in Redshift, use the following for reference, http://docs.aws.amazon.com/redshift/latest/dg/copy-usage_notes-copy-from-json.html
What is the problem you are facing when using the copy command, in my experience it works pretty well.
