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.

CodePudding user response：

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: