Parse JSON from Presto varchar column fails

Question

I am attempting to convert my varchar column data, which is stringifed JSON, to MAP datatype so I can reference the data as elements.

WITH
data(c) AS (
    SELECT message from mydb.mytable
),
parsed AS (
    SELECT cast(json_parse(c) as map(varchar, varchar)) AS m
    FROM data
)
SELECT m['action'], m['uuid']
FROM parsed

Sample data looks like: {"action":"send","timestamp":1566432054,"uuid":"1234"}

I tried solution provided here: How to cast varchar to MAP(VARCHAR,VARCHAR) in presto, which is where I got the query from replacing values with a select statement but it did not work. I get error:

INVALID_CAST_ARGUMENT: Value cannot be cast to map(varchar,varchar)

Answer 1

json_parse + cast work on your example data:

SELECT CAST(json_parse(str) AS map(varchar, varchar))
FROM (VALUES '{"action":"send","timestamp":1566432054,"uuid":"1234"}') t(str);

I tested this on Presto 317:

presto> SELECT CAST(json_parse(str) AS map(varchar, varchar))
     -> FROM (VALUES '{"action":"send","timestamp":1566432054,"uuid":"1234"}') t(str);
                     _col0
------------------------------------------------
 {action=send, uuid=1234, timestamp=1566432054}
(1 row)

My guess is that some data row is different than your example and this data row cannot be cast. You can find it with try:

SELECT str
FROM your_table
WHERE str IS NOT NULL
AND try(CAST(json_parse(str) AS map(varchar, varchar))) IS NULL;