1.get_json_object 获取json数据
示例:
表:src_json 字段:json
{
"store"
:
{
"fruit"
:\[{
"weight"
:
8
,
"type"
:
"apple"
},{
"weight"
:
9
,
"type"
:
"pear"
}],
"bicycle"
:{
"price"
:
19.95
,
"color"
:
"red"
}
},
"email"
:
"amy@only_for_json_udf_test.net"
,
"owner"
:
"amy"
}
>select get_json_object(src_json.json, '$.owner') from src_json;
>amy
>select get_json_object(src_json.json, '$.fruit.\[0]') from src_json;
>{
"weight"
:
8
,
"type"
:
"apple"
}
2.explode(array) 一列转多行
select explode(array('A','B','C'));
explode(map)
select explode(map('A',10,'B',20,'C',30)) as (key, value);
key value
A 10
B 20
C 30
posexplode(array) 可标注字段
select posexplode(array('A','B','C'));
select posexplode(array('A','B','C')) as (pos,val);
pos val
0 A
1 B
2 C
3.字符串长度函数
> select length('sfgea') from dual;
5
4.字符串反转函数
> select reverse('abcd') from dual;
dcba
5.带分隔符字符串连接函数
> select concat_ws('::','abc','des') from dual;
abc::des
6.字符串截取函数
substr,substring 返回字符串A从start位置到结尾的字符串
> select substr('abcdef',3) from dual;
cdef
7.字符串大小写转换
字符串转大写函数:upper,ucase
字符串小写函数:lower,lcase
> select lower('asfSHED') from dual;
asdshed
8.左右去除空格函数
左边去空格: ltrim
右边去空格:rtrim
9.正则表达式替换函数
regexp_replace(string A, string B, string C)
将字符串A中的符合java正则表达式B的部分替换为C。
> select regexp_replace('foodar', 'oo|ar', '') from dual;
fd
10.正则表达式解析函数
regexp_extract(string subject, string pattern, int index)
将字符串subject按照pattern正则表达式的规则拆分,返回index指定的字符。
>