How do you write a presto query to split a string into its own column

Question

Trying to splint a string into multiple columns in qubole using presto query.

{"field0":[{"startdate":"2022-07-13","lastnightdate":"2022-07-16","adultguests":5,"childguests":0,"pets":null}]}

Would like startdate,lastnightdate,adultguests,childguests and pets into its own column.

I tried to unnest string but that didn't work.

Answer 1

The data looks a lot like json, so you can process it using json functions first (parse, extract, cast to array(map(varchar, json)) or array(map(varchar, varcchar))) and then flatten with unnest:

-- sample data
WITH dataset(json_payload) AS (
    VALUES 
        ('{"field0":[{"startdate":"2022-07-13","lastnightdate":"2022-07-16","adultguests":5,"childguests":0,"pets":null}]}')
) 

-- query
select m['startdate'] startdate,
    m['lastnightdate'] lastnightdate,
    m['adultguests'] adultguests,
    m['childguests'] childguests,
    m['pets'] pets
from dataset,
unnest(cast(json_extract(json_parse(json_payload), '$.field0') as array(map(varchar, json)))) t(m)

Output:

startdate	lastnightdate	adultguests	childguests	pets
2022-07-13	2022-07-16	5	0	null