Mysql的列转行函数使用方式 (explode trans_array)

-- explode拆解切片数据结构

SELECT  id
        ,a.ad
FROM    (
            SELECT  'news00170efe41d452d43ab90590eac59369417' AS id
                    ,CAST('801880,88229900' AS STRING) AS source
            UNION ALL
            SELECT  'news001abed920eb9144cc1b21c7474f2bf8e6f' AS id
                    ,CAST('801720' AS STRING) AS source
            UNION ALL
            SELECT  'news001539b1d00e0c1478c80cbd196fc0986f0' AS id
                    ,CAST('801880' AS STRING) AS source
        ) t
LATERAL VIEW EXPLODE(SPLIT(t.source,',')) a AS ad

 

-- explode拆解正则表达式数据结构

SELECT  id
        ,a.ad
FROM    (
            SELECT  'news00170efe41d452d43ab90590eac59369417' AS id
                    ,CAST('["801880,88229900"]' AS STRING) AS source
            UNION ALL
            SELECT  'news001abed920eb9144cc1b21c7474f2bf8e6f' AS id
                    ,CAST('["801720"]' AS STRING) AS source
            UNION ALL
            SELECT  'news001539b1d00e0c1478c80cbd196fc0986f0' AS id
                    ,CAST('["801880"]' AS STRING) AS source
        ) t
LATERAL VIEW EXPLODE(SPLIT(regexp_replace(t.source,'\\[||\\]||"',''),',')) a AS ad

 

-- explode拆解array型数据结构

SELECT  id
        ,t.source
        ,a.ad
FROM    (
            SELECT  'news00170efe41d452d43ab90590eac59369417' AS id
                    ,CAST('801880,88229900' AS STRING) AS source
                    ,array('news00170efe41d452d43ab90590eac59369417','801880,88229900') as array_info
            UNION ALL
            SELECT  'news001abed920eb9144cc1b21c7474f2bf8e6f' AS id
                    ,CAST('801720' AS STRING) AS source
                    ,array('news001abed920eb9144cc1b21c7474f2bf8e6f','801720') as array_info
            UNION ALL
            SELECT  'news001539b1d00e0c1478c80cbd196fc0986f0' AS id
                    ,CAST('801880' AS STRING) AS source
                    ,array('news001539b1d00e0c1478c80cbd196fc0986f0','801880') as array_info
        ) t
LATERAL VIEW EXPLODE(t.array_info) a AS ad

 

-- trans_arry

user_id:20140101000000001800986278
data_info:"[{"answer": "工资、劳动报酬", "questionId": 1700, "answerScore": 2, "id": 1435}, {"answer": "", "questionId": 1701, "answerScore": 1, "id": 1439}, {"answer": "5%至20%", "questionId": 1702, "answerScore": 1, "id": 1444}, {"answer": "没有", "questionId": 1703, "answerScore": 4, "id": 1452}, {"answer": "一般：对金融产品及其相关风险具有一定的知识和理解", "questionId": 1704, "answerScore": 2, "id": 1455}, {"answer": "资产均衡地分布于存款、国债、银行理财产品、股票、基金等", "questionId": 1705, "answerScore": 2, "id": 1460}, {"answer": "5至10年", "questionId": 1706, "answerScore": 3, "id": 1466}, {"answer": "1至3年", "questionId": 1707, "answerScore": 1, "id": 1469}, {"answer": "资产稳健增长，高于通货膨胀率", "questionId": 1708, "answerScore": 2, "id": 1475}, {"answer": "稳健投资，愿意接受短期亏损，但无法接受可能出现的大幅波动", "questionId": 1709, "answerScore": 2, "id": 1480}, {"answer": "同时投资于A和B，但大部分资金投资于收益较大且风险较大的B", "questionId": 1710, "answerScore": 3, "id": 1486}, {"answer": "15%-30%", "questionId": 1711, "answerScore": 2, "id": 1490}]"

 

-- trans_array拆解

SELECT 
    TRANS_ARRAY(1,'},',user_id,t.data_info)  as (user_id,cc)
FROM    (
    select   user_id,    data_info from a
)t

 

-- trans_array拆解正则表达式数据结构

SELECT 
    TRANS_ARRAY(1,'},',user_id,regexp_replace(t.data_info,'\\[|\\]','',0) ) as (user_id,cc)
FROM    (
    select user_id, data_info from a
)t

 

-- trans_array拆解json数据结构

SELECT DISTINCT 
    user_id
    ,GET_JSON_OBJECT(tolower(ccc),'$.answer') as answer
    ,GET_JSON_OBJECT(tolower(ccc),'$.questionid') as questionid
    from(
        SELECT 
        user_id 
        ,cc
        ,case when cc like '%}%' then cc else concat(cc,'}') end as ccc
        from (
            SELECT 
                TRANS_ARRAY(1,'},',user_id,regexp_replace(t.data_info,'\\[|\\]','',0) ) as (user_id,cc)
            FROM    (
                select   user_id,   data_info from a
            )t
        )t
    )tt

 

 

 

 

 

其他相关学习链接：https://www.cnblogs.com/alidata/p/13444025.html