SQL处理json数据

CREATE TABLE `json_test` (
  `id` int NOT NULL COMMENT '主键ID',
  `json_str` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci COMMENT 'json字符串',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT INTO json_test(`id`, `json_str`) VALUES (1, '{"episodes_info":"","rate":"6.9","cover_x":1920,"title":"咒","url":"https:\/\/movie.doudoudou.com\/subject\/34850561\/","playable":false,"cover":"https://img3.doudoudouio.com\/view\/photo\/s_ratio_poster\/public\/p2871258860.jpg","id":"34850561","cover_y":2782,"is_new":false}');
INSERT INTO json_test(`id`, `json_str`) VALUES (2, '{"episodes_info":"","rate":"6.9","cover_x":1920,"title":"咒","url":"https:\/\/movie.doudoudou.com\/subject\/34850561\/","playable":false,"cover":"https://img3.doudoudouio.com\/view\/photo\/s_ratio_poster\/public\/p2871258860.jpg","id":"34850561","cover_y":2782,"is_new":false}');
INSERT INTO json_test(`id`, `json_str`) VALUES (3, '{"episodes_info":"","rate":"7.7","cover_x":750,"title":"稍微想起一些","url":"https:\/\/movie.doudoudou.com\/subject\/35597426\/","playable":false,"cover":"https://img9.doudoudouio.com\/view\/photo\/s_ratio_poster\/public\/p2702451756.jpg","id":"35597426","cover_y":1061,"is_new":true}');
INSERT INTO json_test(`id`, `json_str`) VALUES (4, '{"episodes_info":"","rate":"7.0","cover_x":743,"title":"光年正传","url":"https:\/\/movie.doudoudou.com\/subject\/35284168\/","playable":false,"cover":"https://img9.doudoudouio.com\/view\/photo\/s_ratio_poster\/public\/p2871883924.jpg","id":"35284168","cover_y":1100,"is_new":false}');
INSERT INTO json_test(`id`, `json_str`) VALUES (5, '{"episodes_info":"","rate":"8.3","cover_x":1170,"title":"祝你好运，里奥·格兰德","url":"https:\/\/movie.doudoudou.com\/subject\/35235813\/","playable":false,"cover":"https://img2.doudoudouio.com\/view\/photo\/s_ratio_poster\/public\/p2872943472.jpg","id":"35235813","cover_y":1721,"is_new":false}');
INSERT INTO json_test(`id`, `json_str`) VALUES (6, '{"episodes_info":"","rate":"8.6","cover_x":1403,"title":"宿敌","url":"https:\/\/movie.doudoudou.com\/subject\/35882880\/","playable":false,"cover":"https://img9.doudoudouio.com\/view\/photo\/s_ratio_poster\/public\/p2874026114.jpg","id":"35882880","cover_y":2048,"is_new":false}');

通过JSON_EXTRACT函数提取数据

SELECt
	id,
	JSON_EXTRACT( json_str, '$[0].rate', '$[0].cover_x', "$[0].playable" ) AS "json_list",
	JSON_EXTRACT( json_str, '$[0].title' ) AS "title" 
FROM
	json_test;

    with json_test as (
        select 
    1 as id,'{"episodes_info":"","rate":"6.9","cover_x":1920,"title":"咒","url":"https://movie.doudoudou.com/subject/34850561/","playable":false,"cover":"https://img3.doudoudouio.com/view/photo/s_ratio_poster/public/p2871258860.jpg","id":"34850561","cover_y":2782,"is_new":false}' as json_str
    union all
    select
    2 ,'{"episodes_info":"","rate":"6.9","cover_x":1920,"title":"咒","url":"https://movie.doudoudou.com/subject/34850561/","playable":false,"cover":"https://img3.doudoudouio.com/view/photo/s_ratio_poster/public/p2871258860.jpg","id":"34850561","cover_y":2782,"is_new":false}'
    union all
    select
    3 ,'{"episodes_info":"","rate":"7.7","cover_x":750,"title":"稍微想起一些","url":"https://movie.doudoudou.com/subject/35597426/","playable":false,"cover":"https://img9.doudoudouio.com/view/photo/s_ratio_poster/public/p2702451756.jpg","id":"35597426","cover_y":1061,"is_new":true}'
    union all
    select
    4 ,'{"episodes_info":"","rate":"7.0","cover_x":743,"title":"光年正传","url":"https://movie.doudoudou.com/subject/35284168/","playable":false,"cover":"https://img9.doudoudouio.com/view/photo/s_ratio_poster/public/p2871883924.jpg","id":"35284168","cover_y":1100,"is_new":false}'
    union all
    select
    5 ,'{"episodes_info":"","rate":"8.3","cover_x":1170,"title":"祝你好运，里奥·格兰德","url":"https://movie.doudoudou.com/subject/35235813/","playable":false,"cover":"https://img2.doudoudouio.com/view/photo/s_ratio_poster/public/p2872943472.jpg","id":"35235813","cover_y":1721,"is_new":false}'
    union all
    select
    6 ,'{"episodes_info":"","rate":"8.6","cover_x":1403,"title":"宿敌","url":"https://movie.doudoudou.com/subject/35882880/","playable":false,"cover":"https://img9.doudoudouio.com/view/photo/s_ratio_poster/public/p2874026114.jpg","id":"35882880","cover_y":2048,"is_new":false}'
    )

    select id,json_str,json_data.rate,json_data.cover_x,json_data.title,json_data.url,json_data_2.playable,json_data_2.cover_y from json_test
    lateral view 
    json_tuple(json_str,
        "rate",
        "cover_x",
        "title",
        "url"
    ) json_data as rate,cover_x,title,url
    lateral view 
    json_tuple(json_str,
        "playable",
        "cover_y"
    ) json_data_2 as playable,cover_y

通过SQL进行数据分发
https://blog.csdn.net/weixin_43932609/article/details/125835225?spm=1001.2014.3001.5501
kettle组件【维度查询/更新】的用法
https://blog.csdn.net/weixin_43932609/article/details/124734608?spm=1001.2014.3001.5501
kettle组件HTTP client的使用方法
https://blog.csdn.net/weixin_43932609/article/details/123984884?spm=1001.2014.3001.5502
Kettle循环导出整库数据
https://blog.csdn.net/weixin_43932609/article/details/119610480?spm=1001.2014.3001.5502
ETL工具kettle的计算方式
https://blog.csdn.net/weixin_43932609/article/details/110371110

=========================================================

人生得意须尽欢，莫使金樽空对月！
__一个热爱说唱的程序员。
今日份推荐音乐：KEY.L 刘聪《清风调 (LIVE版)》

=========================================================