6

I am trying to copy a JSON file to Redshift but keep getting the error "Invalid JSONPath format: Member is not an object."

the problem seems to be because of the presence of "[" and "]" as the first and last characters in the JSON file. Everything else in between confirms to the standard JSON format. When I modified the file and removed those two characters the table load goes through smoothly.

The file is provided directly from a developer, and I don't have the influence to persuade him change the format. So my only option, is to somehow tell the COPY command to ignore those two characters at the beginning and end of the JSON file.

I appreciate any suggestions/help from the group.

Improve this question

2 Answers 2

Reset to default
14

The problem with the COPY command is it does not really accept a valid JSON file. Instead, it expects a JSON-per-line which is shown in the documentation, but not obviously mentioned.

Hence, every line is supposed to be a valid JSON but the full file is not.

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

Comments

1

Well there are not a lot of JSON capabilities present with Redshift Copy as of now but have you tried using a JSONPaths file? http://docs.aws.amazon.com/redshift/latest/dg/r_COPY_command_examples.html#r_COPY_command_examples-copy-from-json

If this doesn't work and you can't change the source file format, include another step before the Copy and use some file transformation (if you are using some ETL tool) or Linux functions to remove the square braces from the file.

Improve this answer

2 Comments

thank you for taking the time Paladin. JSONpaths file seemed to be only helpful for matching column names to the object names. The JSON files i have to refer are API dumps. I am considering learning Python so that I can parse the JSON file. but not really sure how to use the script to pull data from Amazon S3 and put it on either the databases or redshift. Thank you for the confirmation though. i am voting your answer as helpful.
If you want to use Python to connect to S3/ Redshift, you can always use the AWS SDKs for Python (e.g. Boto libraries are quite good for most use cases).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.