How to UNNEST a variable list of JSON key value pairs values from a SQL column-CodePudding

I have a json dictionary in a cell of my table. The keys of the dictionary can be varied and I don't know the full list of them in advance.

How can I unnest the key value pairs?

FWIW, I'm using Presto.

with example(json_info) as (
    VALUES
    ('{"Key A": "ABC","Key B": "DEF", "Key C": "XYZ"}') 
)

select
    key
    , value
from example
CROSS JOIN
    UNNEST(
            CAST(
                JSON_PARSE(json_info)
                as ARRAY(ROW(key VARCHAR, value VARCHAR))
            )
    ) as x(key, value)

When I run the above code I get the following error which makes me think I'm on the wrong path.

Cannot cast to array(row(type varchar,value varchar)). Expected a json array, but got { {"Key A": "123","Key B": "456", "Key C": "789"}

CodePudding user response：

You can cast the json object to map(varchar, varchar) and then unnest it into a key and a value column:

...
UNNEST(
    CAST(json_parse(json_info) AS map(varchar, varchar))
) AS x(key, value)
...

CodePudding user response：

Figured it out. I needed to use a map(varchar, varchar)

with example(json_info) as (
    VALUES
    ('{"Key A": "123","Key B": "456", "Key C": "789"}') 
)

select
    key
    , value
from example
CROSS JOIN
    UNNEST(
            CAST(
                JSON_PARSE(json_info)
                as map(varchar, varchar)
            )
    ) as x(key, value)

which gave me this:

key	value
Key A	ABC
Key B	DEF
Key C	XYZ