starrocks中的json解析

LATERAL 作用

允许子查询或表函数访问主查询的列。
支持对每一行执行子查询，返回与该行相关的结果。
常用于展开数组、JSON、或者调用表函数

运用测试

with t1 as (SELECT json_array(1, true, 'starrocks', 1.1) as ja )
SELECT
parse_json(a.value)  as value 
,*
from t1,LATERAL JSON_EACH(ja) a ; 

运用举例

INSERT into dwd.`dwd_cust_exp_user_task_event_detail_di` 
with base_data as (
SELECTds ,extend_params,anonymous_id,serial_no ,event_name 
from ods.ods_unity_traffic_log_di
where ds='2025-07-02' and event_name in( 'task_expose','task_verify_click')
), task_verify_click as (
SELECTds ,get_json_string(extend_params,'$.task_id')    as task_key,anonymous_id,serial_no ,event_name 
from base_data 
where event_name = 'task_verify_click'
), task_keys as (
SELECTds ,json_query(parse_json(extend_params),'$.expose_list')    as tks ,anonymous_id,serial_no ,event_name 
from base_data
where  event_name  =  'task_expose'
),details as (
SELECT ds ,anonymous_id,serial_no ,event_name ,parse_json(data.value) detail  
from task_keys, LATERAL JSON_EACH(tks) data
), expose_every as (
SELECTds ,get_json_string(d.detail,'$.task_id') as task_key ,anonymous_id,serial_no  ,event_name 
from details d 
), rest_data as (
SELECT ds,task_key,anonymous_id,serial_no,event_name
from expose_every
union all 
SELECTds,task_key,anonymous_id,serial_no,event_name
from expose_every
), task_config as (
SELECT id  as task_id ,task_type,task_key 
from ods.`ods_sosponge_activity_task_s_task_config` 
where ds='2025-07-02' and  activity_type = 0  -- 代表是 exp活动
and enable_flag = 0
)SELECTds ,t2.task_id 
--  ,t1.task_key ,t2.task_type ,t1.event_name ,bitmap_hash(anonymous_id)   as anonymous_id_bitmap,bitmap_hash(serial_no)  as view_bitmap
from  rest_data  t1 
LEFT JOIN task_config t2 
on t1.task_key = t2.task_key
where t2.task_id is not null