How to expand a list of dict in presto db-CodePudding

I have a column in prestodb that is a list of dictionaries:

[{"id": 45238, "kind": "product", "name": "Ball", "category": "toy"}, {"id": 117852, "kind": "service", "name": "courier", "category": "transport"}]

is a there a way to expand this column to get something like this:

id     kind      name      category
4528   product   Ball      toy
117852 service   courier   transport

Also sometimes the key's can be different from the example above also can have more key's than the 4 above

I am trying:

with cte as ( select cast(divs as json) as json_field from table)
select m['id'] id, 
    m['kind'] kind,
    m['name'] name,
    m['category'] category
from cte
cross join unnest(cast(json_field as array(map(varchar, json)))) as t(m)

Error:

INVALID_CAST_ARGUMENT: Cannot cast to array(map(varchar, json)). Expected a json array, but got [{"id": 36112, "kind"....

CodePudding user response：

Assuming your data contains json - you can cast it to array of maps from varchar to json (array(map(varchar, json))) and then use unnest to flatten the array:

WITH dataset (json_str) AS (
    VALUES (json '[{"id": 45238, "kind": "product", "name": "Ball", "category": "toy"}, {"id": 117852, "kind": "service", "name": "courier", "category": "transport"}]')
) 

select m['id'] id, 
    m['kind'] kind,
    m['name'] name,
    m['category'] category
from dataset
cross join unnest(cast(json_str as array(map(varchar, json)))) as t(m)

id	kind	name	category
45238	product	Ball	toy
117852	service	courier	transport

UPD

If original column type is varchar - use json_parse to convert it to json.