CODEWITHSUNDEEP
amazon-redshift
user3333553
user3333553

Reputation: 383

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

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.

Upvotes: 0

Views: 2571

Answers (1)

Landon McDowell
Landon McDowell

Reputation: 11

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.

Upvotes: 1

Related Questions