Redshift UNLOAD JSON

Question

Is there any way to directly export a JSON file to S3 from Redshift using UNLOAD? I'm not seeing anything in the documentation (Redshift UNLOAD documentation), but maybe I'm missing something. The COPY command supports downloading JSON, so I'm surprised that there's not JSON flag for UNLOAD.

For more context: I'm loading data from one Redshift instance to another, but the respective tables have different column orders, and I need to respect the column order in the destination. Seems like the best way to do this is to work with a serialization format that doesn't care about column order.

Answer 1

Unload as json is supported by Redshift now.

Ref: https://docs.aws.amazon.com/redshift/latest/dg/r_UNLOAD_command_examples.html#unload-examples-json

Example from AWS doc

unload ('select * from venue')
to 's3://mybucket/unload/' 
iam_role 'arn:aws:iam::0123456789012:role/MyRedshiftRole'
JSON;

Answer 2

No, unload to a JSON file is not possible with UNLOAD command in Redshift.

How to dump data from Redshift to JSON | DevelByte claims there is a workaround, I haven't tried it, but it might give you an idea.