Amazon Redshift error while copying json file - Invalid JSONPath format: Member is not an object

Question

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.

Answer 1

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.