常见sql种 lateral view用法：
1.question_option_id是数组存储格式是字符串类型
select id,questionnaire_id,user_id,question_id,option_id as question_option_id,answer_description,score,org_id,create_date as option_commit_create_date
from
nbug_ods.s_qn_user_answer
lateral view OUTER explode(split(regexp_replace(replace(replace(question_option_id,'[',''),']',''),'\"+',''),',')) tmp as option_id
where ds = ${bizdate}
AND del_flag = '0'
group by id,questionnaire_id,user_id,question_id,option_id,answer_description,score,org_id,create_date
2.question_option_id是数组，存储格式是数组类型
select id,questionnaire_id,user_id,question_id,option_id as question_option_id,answer_description,score,org_id,create_date as option_commit_create_date
from
nbug_ods.s_qn_user_answer
lateral view OUTER explode(split(question_option_id,')) tmp as option_id
where ds = ${bizdate}
AND del_flag = '0'
group by id,questionnaire_id,user_id,question_id,option_id,answer_description,score,org_id,create_date
3.字段是json，存储格式是字符串 其中users是json，存储是数组，
text是json类型，articles字段也是数组类型，存储格式是字符串类型。
,regexp_replace( regexp_replace(regexp_replace(get_json_object(text,'$.articles'),'^\\[',''),']','') ,'},{', '}&NN&{') as text_date
SELECT
GET_JSON_OBJECT(c1.user_info,'$.errorMsg') user_errorMsg
,GET_JSON_OBJECT(c1.user_info,'$.mobile') user_mobile
,GET_JSON_OBJECT(c1.user_info,'$.status') user_status
,GET_JSON_OBJECT(c1.user_info,'$.type') user_type
,GET_JSON_OBJECT(c1.user_info,'$.wxParentId') user_wxParentId
,GET_JSON_OBJECT(c1.user_info,'$.xyhUserId') xyhUserId
,GET_JSON_OBJECT(c1.text_date,'$.description') text_description
,GET_JSON_OBJECT(c1.text_date,'$.title') text_title
,GET_JSON_OBJECT(c1.text_date,'$.url') text_url
,c1.success ,c1.id ,c1.class ,c1.uniqueid ,c1.msgid ,c1.transactionid
,c1.fromuserid ,c1.corpid ,c1.orgid ,c1.fromapp ,c1.appid
,c1.gmtmodify ,c1.gmtcreate ,c1.receivetime ,c1.delaymills ,c1.errorcode
,c1.errormsg ,c1.batchcount ,c1.batchnum ,c1.parentmsgid
from (
select
b1.users ,b1.user_info ,b1.text ,b1.text_date ,b1.success ,b1.id ,b1.class
,b1.uniqueid ,b1.msgid ,b1.transactionid ,b1.fromuserid ,b1.corpid
,b1.orgid ,b1.fromapp ,b1.appid ,b1.gmtmodify ,b1.gmtcreate ,b1.receivetime ,b1.delaymills ,b1.errorcode
,b1.errormsg ,b1.batchcount
,b1.batchnum ,b1.parentmsgid
from (
SELECT
a1.id ,a1.class ,a1.uniqueid ,a1.msgid ,a1.transactionid ,a1.fromuserid
,a1.corpid ,a1.orgid ,a1.fromapp ,a1.appid ,a1.gmtmodify ,a1.gmtcreate
,a1.receivetime ,a1.delaymills ,a1.errorcode ,a1.errormsg ,a1.batchcount
,a1.batchnum ,a1.parentmsgid ,a1.users
,users_tmp.user_info
,a1.text ,a1.text_date ,a1.success
from
(
select
id,class ,uniqueid ,msgid ,transactionid ,fromuserid ,corpid ,orgid
,fromapp ,appid ,gmtmodify ,gmtcreate ,receivetime ,delaymills ,errorcode
,errormsg ,batchcount ,batchnum ,parentmsgid ,users
,concat_ws('&NN&',users) users_date
,text
,regexp_replace( regexp_replace(regexp_replace(get_json_object(text,'$.articles'),'^\\[',''),']','') ,'},{', '}&NN&{') as text_date
,success
FROM nbug_ods.s_mongodb_gmtcreate_wxmessage_detal
WHERE ds=${bizdate} and regexp_replace( regexp_replace(regexp_replace(get_json_object(text,'$.articles'),'^\\[',''),']','') ,'},{', '}&NN&{') is not null
) a1
lateral view explode(split(a1.users_date,'&NN&')) users_tmp as user_info
) b1
lateral view explode(split(b1.text_date,'&NN&')) text_tmp as text_info
) c1
