Error while copying json data from S3 to redshift using COPY command

Question

I have below json data in S3:

{ "message": "{\"a\":\"b\",\"c\":\"d\"}"}

and jsonpath:

{
    "jsonpaths": [
        "$.message.a"
    ]
}

I am using below copy command to load redshift table.

copy test.table1 from 's3://bucket1/test.gz' credentials 'aws_access_key_id=<>;aws_secret_access_key=<>' json 's3://bucket1/jsonpath.json' GZIP ;

but it fails with error.

Invalid JSONPath format: Member is not an object.

This example might help: forums.aws.amazon.com/thread.jspa?messageID=538556 — Sandesh Deshmane
– Sandesh Deshmane, Commented Feb 11, 2015 at 14:25
Are the backslashes above present in the S3 file?If so, why do you need them? — isaac.hazan
– isaac.hazan, Commented Feb 15, 2015 at 6:45

Landon McDowell · Accepted Answer · 2015-02-21 15:48:35Z

1

The Redshift copy error means it is not seeing an "a" key in the "message" object in your datafile.

The quotes around your "message" object value in your json data is causing your problem. Your json should look like:

{ "message": {"a":"b","c":"d"}}

Escaping the quotes should not be necessary, so I took out the backslashes. Leaving them in may work, but I did not test it.

answered Feb 21, 2015 at 15:48

Landon McDowell

111 bronze badge

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

1 Answer 1