Are json with double quotes treated differently in Presto?

Question

Using Presto SQL, the original format of the column appears like this "{\"ORDERS (completed)\": 1000.00, \"ORDERS (failed) USD\": 3000.00}" and I am trying to extract the key and value by parsing and casting as map (varchar, double) and then unnesting

UNNEST(
    CAST(JSON_PARSE(json) as MAP<VARCHAR, DOUBLE>)
) x(key, value)

When I use json_parse, the forward slashes and quotes disappear, but I receive an error (Expected a json object) when trying to cast as map<varchar, double> {"ORDERS (filled)": 1000.00, "ORDERS (succeeded) USD": 3000.00}

I've also tried casting as json, but received a similar error "{\"ORDERS (completed) USD\": 1000.0}" "\"{\\\"ORDERS (failed) USD\\\": 3000.0}\""

Answer 1

Your JSON is a string that contains encoded JSON (basically it is a double-encoded JSON), so parsing it one time will not do the trick. The more correct approach would be to double parse it:

with dataset(col) as (
    values ('"{\"ORDERS (completed)\": 1000.00, \"ORDERS (failed) USD\": 3000.00}"')
)

select cast(
           json_parse(
               json_extract_scalar(
                   json_parse(col), '$')) as map(varchar, double))
from dataset;

Which gives the following output:

                          _col0                          
---------------------------------------------------------
 {ORDERS (failed) USD=3000.0, ORDERS (completed)=1000.0}