How to cast varchar to MAP(VARCHAR,VARCHAR) in presto
Asked Answered
T

2

7

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?

Tranquilizer answered 21/3, 2019 at 11:47 Comment(0)
D
18

There is no canonical string representation for a MAP in Presto/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
Drear answered 21/3, 2019 at 14:44 Comment(0)
T
1

select cast( json_parse(mappings) as MAP(VARCHAR,VARCHAR)) from hello1;

Tranquilizer answered 21/3, 2019 at 11:53 Comment(0)

© 2022 - 2024 — McMap. All rights reserved.