json解析
1、json的Araay中有多条子:数量不确定、顺序不一致
表test_table的结构如下:
字段类型 date varchar json
字段 data_date plan_id data
json列data如下:
{ "creative_list": [{"creative_modify_time": "2021-10-21 18:15:12", "creative_create_time": "2021-10-21 18:14:27", "creative_id": 1714223816754206, "image_mode": "VIDEO_VERTICAL"}, {"creative_modify_time": "2021-10-21 18:14:27", "creative_create_time": "2021-10-21 18:14:27", "creative_id": 1714223816754222, "image_mode": "VIDEO_VERTICAL"}], "first_industry_id": 1902, "marketing_goal": "V"}
creative_list中,json元素的数量不确定,此时需要按照每个plan_id将其对应的creative_list中的所有creative_id解析出来。
-- 先将creative_list的所有元素放在列表中,然后通过cross join与plan_id一一对应
select * from
(select
data_date
,plan_id
,json_extract(data,'$.creative_list') d
,split(replace(replace(replace(json_extract(data,'$.creative_list'),'[{','{'),'}]','}'),'},{','}"--"{'),'"--"') t
from test_table
where plan_id ='A' limit 2
) a
CROSS JOIN UNNEST(t) as temp_table(t1)