How to cast varchar to MAP(VARCHAR,VARCHAR) in presto

Question

I have table in presto, one column named ("mappings") have key-value pair as string

select mappings from hello;

Ex: {"foo": "baar", "foo1": "bar1" }

I want to cast "mappings" column into a MAP

like select CAST("mappings" as MAP) from hello;

This will throw error in presto. How can we translate this to map?

Answer 1

There is no canonical string representation for a MAP in Presto, so so there's no way to cast it directly to MAP(VARCHAR, VARCHAR). But, if your string contains a JSON map, you can use the json_parse function to convert the string into a value of JSON type and convert that to a SQL MAP via a cast.

Example:

WITH
data(c) AS (
    VALUES '{"foo": "baar", "foo1": "bar1"}'
),
parsed AS (
    SELECT cast(json_parse(c) as map(varchar, varchar)) AS m
    FROM data
)
SELECT m['foo'], m['foo1']
FROM parsed

produces:

 _col0 | _col1
-------+-------
 baar  | bar1