try 使用presto查询将字符串分割为qubole中的多个列.
{"field0":[{"startdate":"2022-07-13","lastnightdate":"2022-07-16","adultguests":5,"childguests":0,"pets":null}]}
希望startdate、lastnightdate、成人嘉宾、 children 嘉宾和宠物进入自己的专栏.
我试着解开绳子，但没用.
try 使用presto查询将字符串分割为qubole中的多个列.
{"field0":[{"startdate":"2022-07-13","lastnightdate":"2022-07-16","adultguests":5,"childguests":0,"pets":null}]}
希望startdate、lastnightdate、成人嘉宾、 children 嘉宾和宠物进入自己的专栏.
我试着解开绳子，但没用.
数据看起来很像json，因此您可以先使用json functions(解析、提取、转换为array(map(varchar, json))或array(map(varchar, varcchar)))进行处理，然后使用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)
输出:
| startdate | lastnightdate | adultguests | childguests | pets |
|---|---|---|---|---|
| 2022-07-13 | 2022-07-16 | 5 | 0 | null |