presto和hive处理json字符串数据

hive和presto可以使用get_json_object函数解析json字符串。

简单json字符串

准备数据

drop table if exists test.test_zw;
CREATE TABLE if not exists test.test_zw(
  stu_id int COMMENT 'id',
  info string comment '学生信息'

)
COMMENT '测试表'
STORED as parquet TBLPROPERTIES('parquet.compression'='SNAPPY');
  
-- 插入数据 
insert into test.test_zw values
(1,'{"name":"小明","grade":60}'),
(2,'{"name":"小红","grade":100}');

简单查看数据

select *
from  test.test_zw

解析json

select stu_id
,get_json_object(info,'$.name') as name
,get_json_object(info,'$.grade') as grade
from  test.test_zw

嵌套json字符串

有时候我们需要解析嵌套json字符串，只需取数的时候用”.“连接即可。

准备数据

drop table if exists test.test_zw;
CREATE TABLE if not exists test.test_zw(
  stu_id int COMMENT 'id',
  info string comment '学生信息'
)
COMMENT '测试表'
STORED as parquet TBLPROPERTIES('parquet.compression'='SNAPPY');
  
-- 插入数据 
insert into test.test_zw values
(1,'{"name":"小明","course":{"math":60,"yuwen":70}}'),
(2,'{"name":"小红","course":{"math":100,"yuwen":80}}');

简单查询

select *
from  test.test_zw

解析json

select stu_id
,get_json_object(info,'$.name') as name
,get_json_object(info,'$.course.math') as math
,get_json_object(info,'$.course.yuwen') as yunwen
from  test.test_zw

变动键json字符串

有时候json的键存储的是某个id，跟行的id一致，这时候可以使用concat拼接字符串来提取指定的值。

准备数据

drop table if exists test.test_zw;
CREATE TABLE if not exists test.test_zw(
  stu_id int COMMENT '学生id',
  course_id int comment '课程id',
  info string comment '学生信息'
)
COMMENT '测试表'
STORED as parquet TBLPROPERTIES('parquet.compression'='SNAPPY');
  
  
insert into test.test_zw values
(1,101,'{"1":"小明","101":60}'),
(2,102,'{"2":"小红","102":100}');

简单查询

select *
from  test.test_zw

解析json

select stu_id
,course_id
,get_json_object(info,concat('$.',stu_id)) as name
,get_json_object(info,concat('$.',course_id)) as grade
from  test.test_zw