1、get_json_object 返回json键值
select get_json_object('{"a":"dd"}','$.a');
dd
select get_json_object('{"b":"c"}','$.a');//json 键 “b” ≠ a 这里没有返回值
➜
select get_json_object('{"c":"d"}','$.c');//json 键 “c” = c 这里返回值 d
➜
get_json_object({"c":"d"}, $.c)
d
select get_json_object('{"ac":"d"}','$.ac'); //主键 ac
➜
get_json_object({"ac":"d"}, $.ac)
d
get_json_object({"a":{"B":"c"}}, $.a)
{"B":"c"}
2、substring_index
select substring_index('www.33.vv','',3);
空串返回空;
select substring_index('www.33.vv','e',3);
不存在,就返回 原始串 www.33.vv
select substring_index('www.33.vv','.',3);
超出,就返回 原始串 www.33.vv
select substring_index('www.abcd.ory','.',1);
正常情况:www
3、hex 、unhex 、decode(... , 'utf-8')
select hex('spark sql');
hex(spark sql)
返回:737061726B2073716C
select decode(unhex('737061726B2073716C'),'utf-8');
返回:
decode(unhex(737061726B2073716C), utf-8)
spark sql
4、to_json
select to_json(named_struct('a','1','b','1'));
➜
structstojson(named_struct(a, 1, b, 1))
{"a":"1","b":"1"}
----------------
select to_json(named_struct('a','b','c')); //键值不成对 就会报错
➜
StatementCallback; uncategorized SQLException for SQL [select to_json(named_struct('a','b','c'))]; SQL state [null]; error code [0];
----------------
select to_json(named_struct('time',to_timestamp('2015-08-26','yyyy-MM-dd')),map('timestampFormat','dd/MM/yyyy'));
➜
structstojson(named_struct(time, to_timestamp('2015-08-26', 'yyyy-MM-dd')))
{"time":"26/08/2015"}
------------------------------------
structstojson(named_struct(time, to_timestamp('2015-11-12 22:10:30', 'yyyy-MM-dd HH:mm:ss'))) |
---|
{"time":"2015-11-12T22:10:30.000+08:00"} |
-------------------------
structstojson(named_struct(time, to_timestamp('2015-12-11 10:23:01', 'yyyy-MM-dd HH:mm:ss'))) |
---|
{"time":"2015-10-11 10:23:01"} |
map('timestampFormat','yyyy-HH HH:mm:ss') ); map 函数可以有选择性的显示日期格式
{"time":"2015-10 10:23:01"},输出,年月 和 时分秒;
-------------------------
两项报错:
to_timestamp('2015-11-10 XXXXX',日期时间格式 ),函数,如果没有写日期格式就会报错
select to_json(named_struct('time',to_timestamp('2015-11-11 12:23:12')) , map('timestampFormat','yyyy-MM-dd HH:mm:ss') );
select to_json(named_struct('time',to_timestamp('2015-11-11') ) );
报错:StatementCallback; uncategorized SQLException for SQL [select
.
.