Redshift Parquet → Spark Parquet round-trip doubles JSON escapes

Question

What happens:

1. UNLOAD from Redshift to S3 as Parquet a dataset that contains a column with nested JSON structure that looks like
   {"a":1,"b":2}
2. Read that Parquet file in Spark and write it back to S3 as Parquet (Snappy).
3. COPY into another Redshift cluster yields the nested JSON column as
   {\"a\":1,\"b\":2}

Expected:
The JSON should remain unescaped, i.e.
{"a":1,"b":2}

Why it matters:
• Redshift treats the backslash-escaped JSON as plain text (VARCHAR) instead of SUPER/JSON, preventing use of json\_extract\_path\_text() or other JSON functions.
• Downstream queries and transformations that depend on native JSON support break.
• You cannot access or manipulate the underlying JSON structure without first stripping escapes.

Environment:
Redshift cluster type: ra3.xlplus
Redshift cluster version: 1.0.115865
Spark 3.5 on Glue 5.0
Parquet (Snappy)

Repro steps:

1. UNLOAD ('SELECT ...') TO 's3://…' FORMAT AS PARQUET;
2. In Spark:
   df = spark.read.parquet("s3://…")
   df.write.parquet("s3://…-roundtrip")
3. In target Redshift:
   COPY dest_table
   FROM 's3://…-roundtrip'
   FORMAT AS PARQUET;
4. SELECT nested_json_col FROM dest_table; shows {"…"}

Workarounds:
• If I run the COPY command directly on the unloaded data from source Redshift, the data column is populated correcly, if I run COPY command on the data after being read/written by Spark (to improve compression and do some transformations) it populates the values with escaped quotes

re:Post Agent · Answer

This issue occurs because of how JSON data is handled during the Parquet format round-trip between Redshift and Spark.

When you UNLOAD data from Redshift to Parquet format, JSON data in SUPER columns is properly stored in the Parquet file. However, when Spark reads this Parquet file, it doesn't recognize the JSON structure as a native JSON object but rather as a string that contains JSON. When Spark then writes this back to Parquet, it treats the JSON as a string value, which causes the quotes to be escaped.

When the second Redshift cluster reads this Parquet file with the COPY command, it sees the data as a string with escaped quotes rather than as native JSON data that should be parsed into a SUPER column.

There are a few potential workarounds:

1. If possible, avoid the Spark round-trip for columns containing JSON data.

2. In Redshift after the COPY operation, you can use the PARSE_JSON function to convert the escaped JSON string back to a proper SUPER type:
```
UPDATE dest_table SET nested_json_col = PARSE_JSON(nested_json_col);
```

3. If you must process the data in Spark, you could explicitly parse the JSON in Spark before writing it back out, ensuring it's handled as structured data rather than a string.

4. Another approach is to use CSV format with JSON option for the UNLOAD/COPY operations instead of Parquet, which might handle the JSON data differently.

The core issue is that Spark and Redshift have different ways of representing JSON data in Parquet files, and this mismatch causes the double escaping when the data makes a round trip through both systems.  
**Sources**  
[Redshift super datatype not enough to store json data type column from Postgres | AWS re:Post](https://repost.aws/questions/QU3qxbjNucSZKAdDsHtSGPzQ/redshift-super-datatype-not-enough-to-store-json-data-type-column-from-postgres)  
[UNLOAD - Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/dg/r_UNLOAD.html)  
[Load super data on Redshift with COPY scapes JSON fields | AWS re:Post](https://repost.aws/questions/QUWtdXPCKzT4CogbJlOO_F7A/load-super-data-on-redshift-with-copy-scapes-json-fields)

Redshift Parquet → Spark Parquet round-trip doubles JSON escapes

Add your answer

Relevant content