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}');
1
2
3
4
5
6
7
8
9
10
11

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;
1
2
3
4
5
6

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
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33

相关内容

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

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