How to cast VARCHAR to MAP(VARCHAR,VARCHAR) in Trino

SQL
1

This question was originally asked on Stack Overflow.

I have table in Trino, 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 Trino. How can we translate this to map?

2

There is no canonical string representation for a MAP in Trino, 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