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).

Radiophotograph

According to this (ecma-international.org/publications/files/ECMA-ST/ECMA-404.pdf) you're good. Maybe a bug on their end? – Brigadier

I just ran into the same exact problem. It seems to be specific to escaped double quotes, but I haven't been able to figure out any more information yet. – Prate

any other solution here than replace? – Asel

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')

Radiophotograph

sorry can you explain why '\\\\.' works? Why does that replace the single slash with nothing? – Asel

Answer 2

If you use json_extract_path_text(event_properties,'someValue', TRUE) it will return null instead of throwing an error.

Kenna