SQL处理json数据

通过sql语句提取json字符串中的数据

1. 示例数据

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}');

id	json_str
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}
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}
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}
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}
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}
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}

MySql提取json字符串：

通过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;

id	json_list	title
1	[“6.9”, 1920, false]	“咒”
2	[“6.9”, 1920, false]	“咒”
3	[“7.7”, 750, false]	“稍微想起一些”
4	[“7.0”, 743, false]	“光年正传”
5	[“8.3”, 1170, false]	“祝你好运，里奥·格兰德”
6	[“8.6”, 1403, false]	“宿敌”

HIVE解析json字符串：

    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

id	json_str	rate	cover_x	title	url	playable	cover_y
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}	6.9	1920	咒	https://movie.doudoudou.com/subject/34850561/	false	2782
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}	6.9	1920	咒	https://movie.doudoudou.com/subject/34850561/	false	2782
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}	7.7	750	稍微想起一些	https://movie.doudoudou.com/subject/35597426/	false	1061
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}	7.0	743	光年正传	https://movie.doudoudou.com/subject/35284168/	false	1100
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}	8.3	1170	祝你好运，里奥·格兰德	https://movie.doudoudou.com/subject/35235813/	false	1721
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}	8.6	1403	宿敌	https://movie.doudoudou.com/subject/35882880/	false	2048

相关内容

通过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版)》

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