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

Mysql8 行专列

SELECT
    TRIM(BOTH '"' FROM JSON_UNQUOTE(JSON_EXTRACT(json_each.value, '$'))) AS id,
    DATE(docCreationDate) AS date
FROM
    (
        select 'SF55922,SF55923,SF55924,SF55925' as order_no,'2024-07-22' as docCreationDate
        union all
        select 'SF30816,SF30818' as order_no,'2024-07-23' as docCreationDate
    )t,
    JSON_TABLE(CONCAT('["', REPLACE(order_no, ',', '","'), '"]'),
    '$[*]' COLUMNS (value JSON PATH '$')) json_each
ORDER BY date, id ;

操作步骤释义
1、JSON_TABLE

使用 JSON_TABLE 函数将 order_no 字符串转换为JSON数组。
使用 REPLACE 函数将逗号 , 替换为 ","，并用 CONCAT 函数将整个字符串包裹成一个JSON数组格式。
2、JSON_EXTRACT

从JSON数组中提取每一个元素，生成多行。
使用 JSON_UNQUOTE 函数移除引号。
使用 TRIM 函数去掉多余的引号。
3、最终选择:

选择设备ID和对应的 docCreationDate。
按日期和设备ID排序。

-- explode拆解切片数据结构

 1 SELECT  id
 2         ,a.ad
 3 FROM    (
 4             SELECT  'news00170efe41d452d43ab90590eac59369417' AS id
 5                     ,CAST('801880,88229900' AS STRING) AS source
 6             UNION ALL
 7             SELECT  'news001abed920eb9144cc1b21c7474f2bf8e6f' AS id
 8                     ,CAST('801720' AS STRING) AS source
 9             UNION ALL
10             SELECT  'news001539b1d00e0c1478c80cbd196fc0986f0' AS id
11                     ,CAST('801880' AS STRING) AS source
12         ) t
13 LATERAL VIEW EXPLODE(SPLIT(t.source,',')) a AS ad

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

 1 SELECT  id
 2         ,a.ad
 3 FROM    (
 4             SELECT  'news00170efe41d452d43ab90590eac59369417' AS id
 5                     ,CAST('["801880,88229900"]' AS STRING) AS source
 6             UNION ALL
 7             SELECT  'news001abed920eb9144cc1b21c7474f2bf8e6f' AS id
 8                     ,CAST('["801720"]' AS STRING) AS source
 9             UNION ALL
10             SELECT  'news001539b1d00e0c1478c80cbd196fc0986f0' AS id
11                     ,CAST('["801880"]' AS STRING) AS source
12         ) t
13 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

1 user_id:20140101000000001800986278
2 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拆解

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

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

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

-- trans_array拆解json数据结构

 1 SELECT DISTINCT 
 2     user_id
 3     ,GET_JSON_OBJECT(tolower(ccc),'$.answer') as answer
 4     ,GET_JSON_OBJECT(tolower(ccc),'$.questionid') as questionid
 5     from(
 6         SELECT 
 7         user_id 
 8         ,cc
 9         ,case when cc like '%}%' then cc else concat(cc,'}') end as ccc
10         from (
11             SELECT 
12                 TRANS_ARRAY(1,'},',user_id,regexp_replace(t.data_info,'\\[|\\]','',0) ) as (user_id,cc)
13             FROM    (
14                 select   user_id,   data_info from a
15             )t
16         )t
17     )tt