Why can't Amazon Redshift Parse this Valid JSON string?

Question

I'm using Amazon Redshift's JSON parsing abilities. Most of the time it works, but it fails in this case. I'm getting 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 was under the impression that the backslash was the proper escape for the double quote (and this was automatically generated by Python's JSON package).

Answer 1

Ok, per this Redshift forum post this is a know bug, but there is no ETA for a fix. The forum recommends this workaround:

Replace:

json_extract_path_text(event_properties,'someValue')

with

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