Why doesn't Amazon Redshift parse this Valid JSON string?

Question

I am using JASON Amazon Redshift parsing capabilities. Most of the time it works, but in this case it fails. I get the following error:

error: JSON parsing error code: 8001 context: invalid json object {"collection_id": 12, "activity_name": "Hour of Zen: The \"Dead Sea\" Float"} 

I had the impression that the backslash was the correct escape for the double quote (and this was automatically generated by the Python JSON package).

Answer 1

Well, for this post in the Redshift forum , this is a mistake, but there is no ETA to fix it. The forum recommends this workaround:

Replace:

 json_extract_path_text(event_properties,'someValue') 

with

 json_extract_path_text(regexp_replace(event_properties,'\\\\.',''),'someValue')