您可以在MaxCompute SQL中使用复杂类型函数处理复杂数据类型,例如ARRAY、MAP、STRUCT、JSON。本文为您提供MaxCompute SQL支持的复杂类型函数的命令格式、参数说明及示例,指导您使用复杂类型函数完成开发。
MaxCompute SQL支持的复杂类型函数如下。其中JSON函数的使用限制请参见使用限制。
函数类别 | 函数 | 功能 |
ARRAY函数 | 判断ARRAY数组中是否所有元素都满足指定条件。 | |
判断ARRAY数组中是否存在满足指定条件的元素。 | ||
使用给定的值构造ARRAY。 | ||
检测指定的ARRAY中是否包含指定的值。 | ||
去除ARRAY数组中的重复元素。 | ||
找出在ARRAY A中,但不在ARRAY B中的元素,并去掉重复的元素后,以ARRAY形式返回结果。 | ||
计算两个ARRAY数组的交集。 | ||
将ARRAY数组中的元素按照指定字符串进行拼接。 | ||
计算ARRAY数组中的最大值。 | ||
计算ARRAY数组中的最小值。 | ||
返回根据指定p范数(p Norm)对数组元素规范化后的数组。 | ||
计算指定元素在ARRAY数组中第一次出现的位置。 | ||
将ARRAY数组的元素进行聚合。 | ||
在ARRAY数组中删除指定元素。 | ||
返回将指定元素重复指定次数后的ARRAY数组。 | ||
将ARRAY数组的元素进行排序。 | ||
计算两个ARRAY数组的并集并去掉重复元素。 | ||
判断两个ARRAY数组中是否包含相同元素。 | ||
合并多个ARRAY数组。 | ||
返回输入数组元素的N元组合组成的数组。 | ||
将ARRAY数组或字符串连接在一起。 | ||
将一行数据转为多行的UDTF。 | ||
将ARRAY数组中的元素进行过滤。 | ||
将数组类型的数组转换为单个数组。 | ||
返回ARRAY数组指定位置的元素值。 | ||
返回指定数组元素的N元语法(n-gram)数组。 | ||
将指定的ARRAY展开,每个Value一行,每行两列分别对应数组从0开始的下标和数组元素。 | ||
返回指定数组的元素倒序数组。 | ||
根据表达式生成包含指定元素的数组。 | ||
返回指定数组的元素随机排列数组。 | ||
返回指定ARRAY中的元素数目。 | ||
对ARRAY数据切片,返回从指定位置开始、指定长度的数组。 | ||
为指定的数组中的元素排序。 | ||
将字符串按照指定的分隔符分割后返回数组。 | ||
将ARRAY数组中的元素进行转换。 | ||
将2个ARRAY数组按照位置进行元素级别的合并。 | ||
MAP函数 | 将一行数据转为多行的UDTF。 | |
返回MAP类型参数中满足指定条件的Value。 | ||
使用指定的Key-Value对建立MAP。 | ||
返回多个MAP的并集。 | ||
将MAP中的Key、Value键值映射转换为STRUCT结构数组。 | ||
将MAP中的元素进行过滤。 | ||
通过给定的ARRAY数组构造MAP。 | ||
通过给定的结构体数组构造MAP。 | ||
将参数MAP中的所有Key作为数组返回。 | ||
将参数MAP中的所有Value作为数组返回。 | ||
对输入的两个MAP进行合并得到一个新MAP。 | ||
结构体数组中返回一个MAP。 | ||
返回指定MAP中的K/V对数。 | ||
对MAP进行变换,保持Value不变,根据指定函数计算新的Key。 | ||
对MAP进行变换,保持Key不变,根据指定函数计算新的Value。 | ||
STRUCT函数 | 获取STRUCT中的成员变量的取值。 | |
将指定的STRUCT数组展开。每个数组元素对应一行,每行每个STRUCT元素对应一列。 | ||
使用给定的Name、Value列表建立STRUCT。 | ||
使用给定Value列表建立STRUCT。 | ||
JSON函数 | 根据给定的JSON字符串和输出格式信息,返回ARRAY、MAP或STRUCT类型。 | |
在一个标准JSON字符串中,按照指定方式抽取指定的字符串。 | ||
在一个标准的JSON字符串中,按照输入的一组键抽取各个键指定的字符串。 | ||
将指定的复杂类型输出为JSON字符串。 | ||
生成JSON OBJECT,要求key和value成对出现。 | ||
生成JSON ARRAY。将一个可能为空的JSON类型对象,转换为包含这些类型的数组。 | ||
解析JSON表达式中对应json_path的数据,注意json_path非法时会报错。 | ||
查看json_path对应的JSON值是否存在。 | ||
美化JSON,增加换行及空格。 | ||
返回JSON数据所属的数据类型名称。 | ||
将JSON数据转换成STRING类型,默认不自动进行美化。 | ||
将STRING类型转成JSON类型,非JSON格式转换为字符串会报错。 | ||
检查字符串是否为合法的JSON格式。 | ||
支持基本类型与JSON类型的转换。 |
以下函数示例中涉及->
的使用,关于Lambda函数->
的介绍,详情请参见Lambda函数。
ALL_MATCH
命令格式
boolean all_match(array<T> <a>, function<T, boolean> <predicate>)
命令说明
判断ARRAY数组a中是否所有元素都满足predicate条件。
参数说明
a:必填。ARRAY数组。
array<T>
中的T
指代ARRAY数组元素的数据类型,数组中的元素可以为任意类型。predicate:必填。用于对ARRAY数组a中的元素进行判断的函数(内建函数或自定义函数)或表达式。输入参数的数据类型必须与ARRAY数组a中元素的数据类型一致。
返回值说明
返回BOOLEAN类型。返回规则如下:
如果ARRAY数组a中所有的元素满足predicate条件或ARRAY数组为空,返回结果为True。
如果ARRAY数组a中存在元素不满足predicate条件,返回结果为False。
如果ARRAY数组a中存在元素为NULL,且其他元素都满足predicate条件,返回结果为NULL。
示例
示例1:判断ARRAY数组
array(4, 5, 6)
的所有元素是否满足x x > 3
条件(所有元素大于3)。命令示例如下。--返回true。 select all_match(array(4, 5, 6), x -> x>3);
示例2:ARRAY数组为空。命令示例如下。
--返回true。 select all_match(array(), x -> x>3);
示例3:判断ARRAY数组
array(1, 2, -10, 100, -30)
的所有元素是否满足x-> x > 3
条件。命令示例如下。--返回false。 select all_match(array(1, 2, -10, 100, -30), x -> x>3);
示例4:判断存在NULL元素的ARRAY数组
array(10, 100, 30, null)
的所有元素是否满足x-> x > 3
条件。命令示例如下。--返回NULL。 select all_match(array(10, 100, 30, null), x -> x>3);
ANY_MATCH
命令格式
boolean any_match(array<T> <a>, function<T, boolean> <predicate>)
命令说明
判断ARRAY数组a中是否存在元素满足predicate条件。
参数说明
a:必填。ARRAY数组。
array<T>
中的T
指代ARRAY数组元素的数据类型,数组中的元素可以为任意类型。predicate:必填。用于对ARRAY数组a中的元素进行判断的函数(内建函数或自定义函数)或表达式。输入参数的数据类型必须与ARRAY数组a中元素的数据类型一致。
返回值说明
返回BOOLEAN类型。返回规则如下:
如果ARRAY数组a中存在一个或多个元素满足predicate条件,返回结果为True。
如果ARRAY数组a中没有元素满足predicate条件或ARRAY数组为空,返回结果为False。
如果ARRAY数组a中存在元素为NULL,且其他元素都不满足predicate条件,返回结果为NULL。
示例
示例1:判断ARRAY数组
array(1, 2, -10, 100, -30)
中是否有元素满足x-> x > 3
条件。命令示例如下。--返回true。 select any_match(array(1, 2, -10, 100, -30), x-> x > 3);
示例2:ARRAY数组为空。命令示例如下。
--返回false。 select any_match(array(), x-> x > 3);
示例3:判断ARRAY数组
array(1, 2, -10, -20, -30)
中是否有元素满足x-> x > 3
条件。命令示例如下。--返回false。 select any_match(array(1, 2, -10, -20, -30), x-> x > 3);
示例4:判断存在NULL元素的ARRAY数组
array(1, 2, null, -10)
中是否有元素满足x-> x > 3
条件。命令示例如下。--返回NULL。 select any_match(array(1, 2, null, -10), x-> x > 3);
ARRAY
命令格式
array array(<value>,<value>[, ...])
命令说明
使用指定的值构造ARRAY数组。
参数说明
value:必填。可以为任意类型。所有value的数据类型必须一致。
返回值说明
返回ARRAY类型。
示例
例如表
t_table
的字段为c1 bigint, c2 string, c3 string, c4 bigint, c5 bigint
,包含数据如下:+------------+----+----+------------+------------+ | c1 | c2 | c3 | c4 | c5 | +------------+----+----+------------+------------+ | 1000 | k11 | k21 | 86 | 15 | | 1001 | k12 | k22 | 97 | 2 | | 1002 | k13 | k23 | 99 | 1 | +------------+----+----+------------+------------+
命令示例如下。
--根据c2、c4、c3、c5列的数据构造ARRAY数组。 select array(c2,c4,c3,c5) from t_table; --返回结果如下。 +------+ | _c0 | +------+ | [k11, 86, k21, 15] | | [k12, 97, k22, 2] | | [k13, 99, k23, 1] | +------+
ARRAY_CONTAINS
命令格式
boolean array_contains(array<T> <a>, value <v>)
命令说明
判断ARRAY数组a中是否存在元素v。
参数说明
a:必填。ARRAY数组。
array<T>
中的T
指代ARRAY数组元素的数据类型,数组中的元素可以为任意类型。v:必填。待判断的元素。必须与ARRAY数组a中元素的数据类型一致。
返回值说明
返回BOOLEAN类型。
示例
例如表
t_table_array
的字段为c1 bigint, t_array array<string>
,包含数据如下:+------------+---------+ | c1 | t_array | +------------+---------+ | 1000 | [k11, 86, k21, 15] | | 1001 | [k12, 97, k22, 2] | | 1002 | [k13, 99, k23, 1] | +------------+---------+
命令示例如下。
--检测t_array列是否包含1。 select c1, array_contains(t_array,'1') from t_table_array; --返回结果如下。 +------------+------+ | c1 | _c1 | +------------+------+ | 1000 | false | | 1001 | false | | 1002 | true | +------------+------+
ARRAY_DISTINCT
命令格式
array<T> array_distinct(array<T> <a>)
命令说明
去除ARRAY数组a中的重复元素。
参数说明
a:必填。ARRAY数组。
array<T>
中的T
指代ARRAY数组元素的数据类型,数组中的元素可以为任意类型。返回值说明
返回ARRAY类型。返回规则如下:
新ARRAY数组无重复元素且元素顺序与a中的元素顺序保持一致。
ARRAY数组a中存在元素为NULL时,NULL值会参与运算。
输入数组为空时,返回空数组。
示例
示例1:去除ARRAY数组
array(10, 20, 30, 30, 20, 10)
中的重复元素。命令示例如下。--返回[10,20,30]。 select array_distinct(array(10, 20, 30, 30, 20, 10));
示例2:去除ARRAY数组
array(10, 20, 20, null, null, 30, 20, null)
中的重复元素。命令示例如下。--返回[10,20,null,30]。 select array_distinct(array(10, 20, 20, null, null, 30, 20, null));
示例3:ARRAY数组为空。命令示例如下。
--返回[]。 select array_distinct(array());
ARRAY_EXCEPT
命令格式
array<T> array_except(array<T> <a>, array<T> <b>)
命令说明
找出在ARRAY数组a中,但不在ARRAY数组b中的元素,并去掉重复的元素后,返回新的ARRAY数组。
参数说明
a、b:必填。ARRAY数组。
array<T>
中的T
指代ARRAY数组元素的数据类型,数组中的元素可以为任意类型。a和b的数据类型必须保持一致。返回值说明
返回ARRAY类型。返回规则如下:
新ARRAY数组无重复元素且元素顺序与a中的元素顺序保持一致。
ARRAY数组中存在元素为NULL时,NULL值会参与运算。
任一输入数组为空时,返回对非空数组去重后的新ARRAY数组。
输入数组全部为空时,返回空数组。
示例
示例1:找出在ARRAY数组
array(1, 1, 3, 3, 5, 5)
中,不在ARRAY数组array(1, 1, 2, 2, 3, 3)
中的元素并去重。命令示例如下。--返回[5]。 select array_except(array(1, 1, 3, 3, 5, 5), array(1, 1, 2, 2, 3, 3));
示例2:找出在ARRAY数组
array(1, 1, 3, 3, 5, 5, null, null)
中,不在ARRAY数组array(1, 1, 2, 2, 3, 3)
中的元素并去重。命令示例如下。--返回[5,null]。 select array_except(array(1, 1, 3, 3, 5, 5, null, null), array(1, 1, 2, 2, 3, 3));
示例3:任一输入ARRAY数组为空。命令示例如下。
--返回[2,1]。 select array_except(array(2, 1, 1, 2), cast(array() as array<int>));
示例4:输入ARRAY数组全部为空。命令示例如下。
--返回[]。 select array_except(cast(array() as array<int>), cast(array() as array<int>));
ARRAY_INTERSECT
命令格式
array<T> array_intersect(array<T> <a>, array<T> <b>)
命令说明
计算ARRAY数组a和b的交集,并去掉重复元素。
参数说明
a、b:必填。ARRAY数组。
array<T>
中的T
指代ARRAY数组元素的数据类型,数组中的元素可以为任意类型。a和b的数据类型必须保持一致。返回值说明
返回ARRAY类型。返回规则如下:
ARRAY数组中存在元素为NULL时,NULL值会参与运算。
新ARRAY数组无重复元素且元素顺序与a中的元素顺序保持一致。
如果ARRAY数组a或b为NULL,返回NULL。
示例
示例1:计算ARRAY数组
array(1, 2, 3)
和array(1, 3, 5)
的交集,并去掉重复元素。命令示例如下。--返回[1,3]。 select array_intersect(array(1, 2, 3), array(1, 3, 5));
示例2:计算ARRAY数组
array(10, 20, 20, 30, 30, null, null)
和array(30, 30, 20, 20, 40, null, null)
的交集,并去掉重复元素。命令示例如下。--返回[20,30,null]。 select array_intersect(array(10, 20, 20, 30, 30, null, null), array(30, 30, 20, 20, 40, null, null));
ARRAY_JOIN
命令格式
array_join(array<T> <a>, <delimiter>[, <nullreplacement>])
命令说明
将ARRAY数组a中的元素使用delimiter拼接为字符串。当数组中元素为NULL时,用nullreplacement替代,没有设置nullreplacement时,会忽略NULL元素。
参数说明
a:必填。ARRAY数组。
array<T>
中的T
指代ARRAY数组元素的数据类型。说明当ARRAY数组中的元素非STRING类型时,MaxCompute会将非STRING类型元素转换为STRING类型。
delimiter:必填。STRING类型。连接ARRAY数组a中元素的字符串。
nullreplacement:可选。替代NULL元素的字符串。
返回值说明
返回STRING类型。
示例
--返回10,20,20,30。 select array_join(array(10, 20, 20, null, null, 30), ","); --返回10##20##20##null##null##30。 select array_join(array(10, 20, 20, null, null, 30), "##", "null");
ARRAY_MAX
命令格式
T array_max(array<T> <a>)
命令说明
计算ARRAY数组a中的最大元素。
参数说明
a:必填。ARRAY数组。
array<T>
中的T
指代ARRAY数组元素的数据类型。数组中的元素可以为如下类型:
TINYINT、SMALLINT、INT、BIGINT
FLOAT、DOUBLE
BOOLEAN
DECIMAL、DECIMALVAL
DATE、DATETIME、TIMESTAMP、IntervalDayTime、IntervalYearMonth
STRING、BINARY、VARCHAR、CHAR
ARRAY、STRUCT、MAP
返回值说明
返回ARRAY数组a中的最大元素。返回规则如下:
如果ARRAY数组a为NULL,返回NULL。
如果ARRAY数组a中存在元素为NULL,NULL值不参与运算。
示例
--返回20。 select array_max(array(1, 20, null, 3));
ARRAY_MIN
命令格式
T array_min(array<T> <a>)
命令说明
计算ARRAY数组a中的最小元素。
参数说明
a:必填。ARRAY数组。
array<T>
中的T
指代ARRAY数组元素的数据类型。数组中的元素可以为如下类型:
TINYINT、SMALLINT、INT、BIGINT
FLOAT、DOUBLE
BOOLEAN
DECIMAL、DECIMALVAL
DATE、DATETIME、TIMESTAMP、IntervalDayTime、IntervalYearMonth
STRING、BINARY、VARCHAR、CHAR
ARRAY、STRUCT、MAP
返回值说明
返回ARRAY数组a中的最小元素。返回规则如下:
如果ARRAY数组a为NULL,返回NULL。
如果ARRAY数组a中存在元素为NULL时,NULL值不参与运算。
示例
--返回1。 select array_min(array(1, 20, null, 3));
ARRAY_NORMALIZE
命令格式
array_normalize(array, p)
命令说明
返回根据指定p范数(p Norm)对数组元素规范化后的数组。
此函数等价于
TRANSFORM(array, v -> v / REDUCE(array, 0, (a, v) -> a + POW(ABS(v), p), a -> POW(a, 1 / p))
,但是REDUCE
部分只执行一次。参数说明
array:输入数组,数组元素只支持Float和Double类型。
p: 数组的p Norm。
返回值说明
返回规范化之后的数组。
如果数组为null或者有null数组元素,则返回NULL。
如果
p=0
则返回原数组;p<0
则抛出异常。
示例
SELECT array_normalize(array(10.0, 20.0, 50.0), 1.0);
返回结果如下:
[0.125, 0.25, 0.625]
ARRAY_POSITION
命令格式
bigint array_position(array<T> <a>, T <element>)
命令说明
计算元素element在ARRAY数组a中第一次出现的位置。ARRAY数组元素位置编号自左往右,从1开始计数。
参数说明
a:必填。ARRAY数组。
array<T>
中的T
指代ARRAY数组元素的数据类型。支持的数据类型如下:TINYINT、SMALLINT、INT、BIGINT
FLOAT、DOUBLE
BOOLEAN
DECIMAL、DECIMALVAL
DATE、DATETIME、TIMESTAMP、IntervalDayTime、IntervalYearMonth
STRING、BINARY、VARCHAR、CHAR
ARRAY、STRUCT、MAP
element:必填。待查询的元素,数据类型必须与a中元素的数据类型相同。
返回值说明
返回BIGINT类型。返回规则如下:
如果ARRAY数组a或element为NULL,返回NULL。
未找到元素时返回0。
示例
示例1:计算元素
1
第一次出现在ARRAY数组array(3, 2, 1)
中的位置。命令示例如下。--返回3。 select array_position(array(3, 2, 1), 1);
示例2:element为NULL。命令示例如下。
--返回NULL。 select array_position(array(3, 1, null), null);
ARRAY_REDUCE
命令格式
R array_reduce(array<T> <a>, buf <init>, function<buf, T, buf> <merge>, function<buf, R> <final>)
命令说明
对ARRAY数组a中的元素进行聚合。
参数说明
a:必填。ARRAY数组。
array<T>
中的T
指代ARRAY数组元素的数据类型,数组中的元素可以为任意类型。init:必填。用于聚合的中间结果的初始值。
merge:必填。将ARRAY数组a中的每一个元素与中间结果进行运算的函数(内建函数或自定义函数)或表达式。它的两个输入参数为ARRAY数组a的元素和init。
final:必填。将中间结果转换为最终结果的函数(内建函数或自定义函数)或表达式。它的输入参数为merge运行结果,R指代输出结果的数据类型。
返回值说明
返回结果类型与final函数定义的输出结果类型一致。
示例
--返回6。 select array_reduce(array(1, 2, 3), 0, (buf, e)->buf + e, buf->buf); --返回2.5。 select array_reduce(array(1, 2, 3, 4), named_struct('sum', 0, 'count', 0), (buf, e)->named_struct('sum', buf.sum + e, 'count', buf.count + 1), buf -> buf.sum / buf.count);
ARRAY_REMOVE
命令格式
array<T> array_remove(array<T> <a>, T <element>)
命令说明
在ARRAY数组a中删除与element相等的元素。
参数说明
a:必填。ARRAY数组。
array<T>
中的T
指代ARRAY数组元素的数据类型。支持的数据类型如下:TINYINT、SMALLINT、INT、BIGINT
FLOAT、DOUBLE
BOOLEAN
DECIMAL、DECIMALVAL
DATE、DATETIME、TIMESTAMP、IntervalDayTime、IntervalYearMonth
STRING、BINARY、VARCHAR、CHAR
ARRAY、STRUCT、MAP
element:必填。待删除的元素,数据类型必须与a中元素的数据类型相同。
返回值说明
返回ARRAY类型。返回规则如下:
如果ARRAY数组a中存在元素为NULL时,NULL值不参与运算。
如果ARRAY数组a或element为NULL,返回NULL。
ARRAY数组a中不存在element时返回原ARRAY数组a。
示例
示例1:删除ARRAY数组
array(3, 2, 1)
中等于1
的元素。命令示例如下。--返回[3,2]。 select array_remove(array(3, 2, 1), 1);
示例2:element为NULL。命令示例如下。
--返回NULL。 select array_remove(array(3, 1, null), null);
示例3:删除ARRAY数组
array(3, 1, null)
中等于2
的元素。命令示例如下。--返回[3,1,null]。 select array_remove(array(3, 1, null), 2);
ARRAY_REPEAT
命令格式
array<T> array_repeat(T <element>, int <count>)
命令说明
返回将元素t重复count次后新生成的ARRAY数组。
参数说明
t:必填。待重复的元素。支持的类型如下:
TINYINT、SMALLINT、INT、BIGINT
FLOAT、DOUBLE
BOOLEAN
DECIMAL、DECIMALVAL
DATE、DATETIME、TIMESTAMP、IntervalDayTime、IntervalYearMonth
STRING、BINARY、VARCHAR、CHAR
ARRAY、STRUCT、MAP
count:必填。重复的次数,INT类型。必须大于等于0。
返回值说明
返回ARRAY类型。返回规则如下:
如果count为NULL,返回NULL。
如果count小于0,返回空数组。
示例
示例1:将
123
重复2
次,生成新的ARRAY数组。命令示例如下。--返回[123, 123]。 select array_repeat('123', 2);
示例2:count为NULL。命令示例如下。
--返回NULL。 select array_repeat('123', null);
示例3:count小于0。命令示例如下。
--返回[]。 select array_repeat('123', -1);
ARRAY_SORT
命令格式
array<T> array_sort(array<T> <a>, function<T, T, bigint> <comparator>)
命令说明
将ARRAY数组a中的元素根据comparator进行排序。
参数说明
a:必填。ARRAY数组。
array<T>
中的T
指代ARRAY数组元素的数据类型,数组中的元素可以为任意类型。comparator:必填。用于比较ARRAY数组中2个元素大小的函数(内建函数或自定义函数)或表达式。
comparator(a, b)
的处理逻辑为:当a等于b时,返回0。当a小于b时,返回负整数。当a大于b时,返回正整数。如果comparator(a, b)
返回NULL,则返回报错。重要ARRAY_SORT中的比较函数要求是自洽的,即:
compare(a, b) > 0
,则要求compare(b, a) < 0
。compare(a, b) = 0
,则要求compare(b, a) = 0
。compare(a, b) < 0
,则要求compare(b, a) > 0
。
不自洽函数示例如下:
(left, right) -> CASE WHEN left <= right THEN -1L ELSE 0L END
:假如设置
a = 1
,b = 1
,则compare(a, b) = -1
,compare(b, a) = -1
,两个比较结果相互矛盾即函数不自洽。(left, right) -> CASE WHEN left < right THEN -1L WHEN left = right THEN 0L ELSE 1L END
:假如设置
a = NULL
,b = 1
,则compare(a, b) = 1
,compare(b, a) = 1
,两个比较结果相互矛盾即函数不自洽。
返回值说明
返回ARRAY类型。
示例
示例1:对数组
array(5,6,1)
进行排序。SELECT array_sort(array(5,6,1), (left,right) -> CASE WHEN left < right THEN -1L WHEN left > right THEN 1L ELSE 0L END); --返回结果 +------------+ | _c0 | +------------+ | [1,5,6] | +------------+
示例2:
--返回[{"a":1,"b":10},{"a":2,"b":12},{"a":3,"b":11}]。 select array_sort(a, (a,b)->case when a.a> b.a then 1L when a.a=b.a then 0L else -1L end) from values ( array(named_struct('a', 1, 'b', 10), named_struct('a', 3, 'b', 11), named_struct('a', 2, 'b', 12))) as t(a);
ARRAY_UNION
命令格式
array<T> array_union(array<T> <a>, array<T> <b>)
命令说明
计算ARRAY数组a和b的并集,并去掉重复元素。
参数说明
a、b:必填。ARRAY数组。
array<T>
中的T
指代ARRAY数组元素的数据类型,数组中的元素可以为任意类型。a和b中元素的数据类型必须一致。数组中的元素可以为如下类型:
TINYINT、SMALLINT、INT、BIGINT
FLOAT、DOUBLE
BOOLEAN
DECIMAL、DECIMALVAL
DATE、DATETIME、TIMESTAMP、IntervalDayTime、IntervalYearMonth
STRING、BINARY、VARCHAR、CHAR
ARRAY、STRUCT、MAP
返回值说明
返回ARRAY类型。如果a或b为NULL,返回NULL。
示例
示例1:计算ARRAY数组
array(1, 2, 3)
和array(1, 3, 5)
的并集,并去掉重复元素。命令示例如下。--返回[1,2,3,5]。 select array_union(array(1, 2, 3), array(1, 3, 5));
示例2:任一ARRAY数组为NULL。命令示例如下。
--返回NULL。 select array_union(array(1, 2, 3), null);
ARRAYS_OVERLAP
命令格式
boolean arrays_overlap(array<T> <a>, array<T> <b>)
命令说明
判断ARRAY数组a和b是否存在相同元素。
参数说明
a、b:必填。ARRAY数组。
array<T>
中的T
指代ARRAY数组元素的数据类型,数组中的元素可以为任意类型。a和b中元素的数据类型必须一致。数组中的元素可以为如下类型:
TINYINT、SMALLINT、INT、BIGINT
FLOAT、DOUBLE
BOOLEAN
DECIMAL、DECIMALVAL
DATE、DATETIME、TIMESTAMP、IntervalDayTime、IntervalYearMonth
STRING、BINARY、VARCHAR、CHAR
ARRAY、STRUCT、MAP
返回值说明
返回BOOLEAN类型。返回规则如下:
如果ARRAY数组a中至少包含ARRAY数组b中的一个非NULL元素,返回结果为True。
如果ARRAY数组a和b中没有公共元素、都非空,且其中任意一个数组中包含NULL元素,返回结果为NULL。
如果ARRAY数组a和b中没有公共元素、都非空,且其中任意一个数组中都不包含NULL元素,返回结果为False。
示例
示例1:判断ARRAY数组
array(1, 2, 3)
和array(3, 4, 5)
中是否存在相同元素。命令示例如下。--返回true。 select arrays_overlap(array(1, 2, 3), array(3, 4, 5));
示例2:判断ARRAY数组
array(1, 2, 3)
和array(6, 4, 5)
中是否存在相同元素。命令示例如下。--返回false。 select arrays_overlap(array(1, 2, 3), array(6, 4, 5));
示例3:任一ARRAY数组中存在NULL元素。命令示例如下。
--返回NULL。 select arrays_overlap(array(1, 2, 3), array(5, 4, null));
ARRAYS_ZIP
命令格式
array<struct<T, U, ...>> arrays_zip(array<T> <a>, array<U> <b>[, ...])
命令说明
合并多个给定数组并返回一个结构数组,其中第N个结构包含输入数组的所有第N个值。
参数说明
a、b:必填。ARRAY数组。
array<T>
及array<U>
中的T
和U
指代ARRAY数组元素的数据类型,数组中的元素可以为任意类型。数组中的元素可以为如下类型:
TINYINT、SMALLINT、INT、BIGINT
FLOAT、DOUBLE
BOOLEAN
DECIMAL、DECIMALVAL
DATE、DATETIME、TIMESTAMP、IntervalDayTime、IntervalYearMonth
STRING、BINARY、VARCHAR、CHAR
ARRAY、STRUCT、MAP
返回值说明
返回ARRAY类型。返回规则如下:
生成的结构数组中第N个结构包含输入数组的所有第N个值, 不足N的元素以NULL填充。
如果输入ARRAY数组中任意一个为NULL,返回结果为NULL。
示例
示例1:通过ARRAY数组
array(1, 2, 3)
和array(2, 3, 4)
构造结构数组。命令示例如下。--返回[{0:1, 1:2}, {0:2, 1:3}, {0:3, 1:4}]。 select arrays_zip(array(1, 2, 3), array(2, 3, 4));
示例2:通过ARRAY数组
array(1, 2, 3)
和array(4, 5)
构造结构数组。命令示例如下。--返回[{0:1, 1:4}, {0:2, 1:5}, {0:3, 1:NULL}]。 select arrays_zip(array(1, 2, 3), array(4, 5));
COMBINATIONS
命令格式
combinations(array(T), n)
命令说明
返回输入数组元素的N元组合组成的数组。
参数说明
array:为输入数组。
n:元数。
返回值说明
返回输入数组元素的N元组合组成的数组。
如果输入数组没有重复项,则返回N元子集数组。子集是确定的但顺序不保证,子集中元素是确定的但顺序不保证。
目前n的值默认不能大于5(可通过设置odps.sql.max.combination.length值进行调整), 生成总子集数量默认不能大于100000(可通过设置odps.sql.max.combinations值进行调整)。
如果n大于输入数组元素数,则返回数组为空。
示例
SELECT combinations(array('foo', 'bar', 'boo'),2);
返回结果如下:
[['foo', 'bar'], ['foo', 'boo']['bar', 'boo']]
SELECT combinations(array(1,2,3,4,5),3);
返回结果如下:
[[1, 2, 3], [1, 2, 4], [1, 3, 4], [2, 3, 4], [1, 2, 5], [1, 3, 5], [2, 3, 5], [1, 4, 5], [2, 4, 5], [3, 4, 5]]
SELECT combinations(array(1,2,2),2);
返回结果如下:
[[1,2], [1,2], [2,2]]
CONCAT
命令格式
array<T> concat(array<T> <a>, array<T> <b>[,...]) string concat(string <str1>, string <str2>[,...])
命令说明
输入为ARRAY数组:将多个ARRAY数组中的所有元素连接在一起,生成一个新的ARRAY数组。
输入为字符串:将多个字符串连接在一起,生成一个新的字符串。
参数说明
a、b:必填。ARRAY数组。
array<T>
中的T
指代ARRAY数组元素的数据类型,数组中的元素可以为任意类型。a和b中元素的数据类型必须一致。数组中的元素为NULL值时会参与运算。str1、str2:必填。STRING类型。如果输入参数为BIGINT、DOUBLE、DECIMAL或DATETIME类型,则会隐式转换为STRING类型后参与运算,其他类型会返回报错。
返回值说明
返回ARRAY类型。如果任一输入ARRAY数组为NULL,返回结果为NULL。
返回STRING类型。如果没有参数或任一参数为NULL,返回结果为NULL。
示例
示例1:连接ARRAY数组
array(10, 20)
和array(20, -20)
。命令示例如下。--返回[10, 20, 20, -20]。 select concat(array(10, 20), array(20, -20));
示例2:ARRAY数组元素包含NULL。命令示例如下。
--返回[10, NULL, 20, -20]。 select concat(array(10, null), array(20, -20));
示例3:任一ARRAY数组为NULL。命令示例如下。
--返回NULL。 select concat(array(10, 20), null);
示例4:连接字符串
aabc
和abcde
。命令示例如下。--返回aabcabcde。 select concat('aabc','abcde');
示例5:输入为空。命令示例如下。
--返回NULL。 select concat();
示例6:任一字符串输入为NULL。命令示例如下。
--返回NULL。 select concat('aabc', 'abcde', null);
EXPLODE
使用限制
在一个
select
中只能出现一个explode
函数,不可以出现表的其他列。不可以与
group by
、cluster by
、distribute by
、sort by
一起使用。
命令格式
explode (<var>)
命令说明
将一行数据转为多行的UDTF。
如果参数是
array<T>
类型,则将列中存储的ARRAY转为多行。如果参数是
map<K, V>
类型,则将列中存储的MAP的每个Key-Value对转换为包含两列的行,其中一列存储Key,另一列存储Value。
参数说明
var:必填。
array<T>
类型或map<K, V>
类型。返回值说明
返回转换后的行。
示例
例如表
t_table_map
的字段为c1 bigint, t_map map<string,bigint>
,包含数据如下:+------------+-------+ | c1 | t_map | +------------+-------+ | 1000 | {k11:86, k21:15} | | 1001 | {k12:97, k22:2} | | 1002 | {k13:99, k23:1} | +------------+-------+
命令示例如下。
select explode(t_map) from t_table_map; --返回结果如下。 +-----+------------+ | key | value | +-----+------------+ | k11 | 86 | | k21 | 15 | | k12 | 97 | | k22 | 2 | | k13 | 99 | | k23 | 1 | +-----+------------+
FIELD
命令格式
T field(struct <s>, string <fieldName>)
命令说明
获取STRUCT对象中成员变量的取值。
参数说明
s:必填。STRUCT类型对象。STRUCT的结构为
{f1:T1, f2:T2[, ...]}
,f1
、f2
代表成员变量,T1
、T2
分别代表成员变量f1
、f2
的取值。fieldName:必填。STRING类型。STRUCT类型对象的成员变量。
返回值说明
返回STRUCT类型对象的成员变量的取值。
示例
--返回hello。 select field(named_struct('f1', 'hello', 'f2', 3), 'f1');
FILTER
命令格式
array<T> filter(array<T> <a>, function<T,boolean> <func>)
命令说明
将ARRAY数组a中的元素利用func进行过滤,返回一个新的ARRAY数组。
参数说明
a:必填。ARRAY数组。
array<T>
中的T
指代ARRAY数组元素的数据类型,数组中的元素可以为任意类型。func:必填。用于对a中元素进行过滤的函数(内置函数或自定义函数)或表达式,其输入参数类型必须与a中元素的数据类型一致,其输出结果数据类型为BOOLEAN。
返回值说明
返回ARRAY类型。
示例
--返回[2, 3]。 select filter(array(1, 2, 3), x -> x > 1);
FLATTEN
命令格式
flatten(arrayOfArray)
命令说明
将数组类型的数组转换为单个数组。
参数说明
arrayOfArray:为数组类型的数组。
返回值说明
将数组类型的数组按元素顺序展开为单个数组。
如果输入值为
null
,则返回NULL。如果输入参数不是数组类型的数组,则抛出异常。
示例
SELECT flatten(array(array(1, 2), array(3, 4)));
返回结果如下:
[1,2,3,4]
FROM_JSON
命令格式
from_json(<jsonStr>, <schema>)
命令说明
根据JSON字符串jsonStr和schema信息,返回ARRAY、MAP或STRUCT类型。
参数说明
jsonStr:必填。输入的JSON字符串。
schema:必填。写法与建表语句的类型一致。例如
array<bigint>
、map<string, array<string>>
或struct<a:int, b:double, `C`:map<string,string>>
。说明STRUCT的Key区分大小写。此外,STRUCT类型还有一种写法
a bigint, b double
,等同于struct<a:bigint, b:double>
。JSON数据类型与MaxCompute数据类型的对应关系如下。
JSON数据类型
MaxCompute数据类型
OBJECT
STRUCT、MAP、STRING
ARRAY
ARRAY、STRING
NUMBER
TINYINT、SMALLINT、INT、BIGINT、FLOAT、DOUBLE、DECIMAL、STRING
BOOLEAN
BOOLEAN、STRING
STRING
STRING、CHAR、VARCHAR、BINARY、DATE、DATETIME
NULL
所有类型
说明对于OBJECT和ARRAY类型,会采用尽可能解析的方式,不匹配的类型会忽略。为了便于使用,所有的JSON类型都可以转换为MaxCompute的STRING类型。同时您需要注意,对应FLOAT、DOUBLE、DECIMAL三种数据类型无法保证小数的精度,如果需要确保精度可以先用STRING类型取出数据,再转换为对应数值类型。
返回值说明
返回ARRAY、MAP或STRUCT类型。
示例
示例1:将指定JSON字符串以指定格式输出。命令示例如下。
--返回{"a":1,"b":0.8}。 select from_json('{"a":1, "b":0.8}', 'a int, b double'); --返回{"time":"26/08/2015"}。 select from_json('{"time":"26/08/2015"}', 'time string'); --返回{"a":1,"b":0.8}。 select from_json('{"a":1, "b":0.8}', 'a int, b double, c string'); --返回[1,2,3]。 select from_json('[1, 2, 3, "a"]', 'array<bigint>'); --返回{"d":"v","a":"1","b":"[1,2,3]","c":"{}"}。 select from_json('{"a":1,"b":[1,2,3],"c":{},"d":"v"}', 'map<string, string>');
示例2:使用
map_keys
函数和from_json
函数实现类似JSON_KEYS的作用,获取JSON字符串中所有key的集合。命令示例如下。--返回["a","b"]。 select map_keys(from_json('{"a":1,"b":2}','map<string,string>'));
GET_JSON_OBJECT
使用说明
GET_JSON_OBJECT函数的作用是在一个标准JSON字符串中,按照JSON PATH抽取指定的字符串。当前函数的入参支持两种类型:
入参为JSON类型:基于最新支持的JSON数据类型,采用更为规范的JSON PATH。
入参为STRING类型:原有的JSON PATH解析方式。
入参类型不同时函数的使用方式和注意事项不同,本文为您展示入参分别为JSON和STRING类型时,GET_JSON_OBJECT函数的使用方法。
新JSON类型所使用的JSON PATH与原有的JSON PATH规范不同,可能存在兼容性问题。
GET_JSON_OBJECT不支持JSON PATH的正则语法。
入参为JSON类型
命令格式
string get_json_object(json <json>, string <json_path>)
命令说明
在一个标准JSON字符串中,按照JSON PATH抽取指定的字符串。
参数说明
json:必填,待处理的JSON数据。
json_path:必填,需要返回的值的JSON路径。
返回值说明
返回STRING类型。
示例
示例1:从JSON中获取key为a的value值。
select get_json_object(json '{"a":1, "b":2}', '$.a');
返回结果:
+-----+ | _c0 | +-----+ | 1 | +-----+
示例2:从JSON中获取key为c的value值。
select get_json_object(json '{"a":1, "b":2}', '$.c');
返回结果:
+-----+ | _c0 | +-----+ | NULL | +-----+
示例3:JSON Path非法时,返回NULL。
select get_json_object(json '{"a":1, "b":2}', '$invalid_json_path');
返回结果:
+-----+ | _c0 | +-----+ | NULL | +-----+
入参为STRING类型
命令格式
string get_json_object(string <json>, string <path>)
命令说明
在一个标准JSON字符串中,按照path抽取指定的字符串。每次调用该函数时,都会读一次原始数据,因此反复调用可能影响性能和产生费用。您可以通过
get_json_object
,结合UDTF,轻松转换JSON格式日志数据,避免多次调用函数,详情请参见利用MaxCompute内建函数及UDTF转换JSON格式日志数据。参数说明
json:必填。STRING类型。标准的JSON格式对象,格式为
{Key:Value, Key:Value,...}
。如果遇到英文双引号("),需要用两个反斜杠(\\)进行转义。如果遇到英文单引号('),需要用一个反斜杠(\)进行转义。path:必填。STRING类型。表示在json中的path,以
$
开头。更多path信息,请参见LanguageManual UDF。相关最佳实践案例,请参见JSON数据从OSS迁移至MaxCompute。不同字符的含义如下:$
:表示根节点。.
或['']
:表示子节点。MaxCompute支持用这两种字符解析JSON对象,当JSON的Key本身包含.
时,可以用['']
来替代。[]
:[number]
表示数组下标,从0开始。*
:Wildcard for []
,返回整个数组。*
不支持转义。
限制条件
用
['']
取数只在新版本支持,您需要添加设置Flag的语句set odps.sql.udf.getjsonobj.new=true;
。返回值说明
如果json为空或非法的json格式,返回NULL。
如果json合法,path也存在,则返回对应字符串。
您可以通过在Session级别设置
odps.sql.udf.getjsonobj.new
属性来控制函数的返回方式:当设置
set odps.sql.udf.getjsonobj.new=true;
时,函数返回行为采用了保留原始字符串的方式进行输出。推荐您使用此配置,函数返回行为更标准,处理数据更方便,性能更好。如果MaxCompute项目有使用JSON保留字符转义行为的存量作业,建议保留原有行为方式,避免因未验证而直接使用该行为产生错误或正确性问题。函数返回行为规则如下:
返回值仍是一个JSON字符串,可以继续当做JSON来解析,而不再需要额外使用
replace
或regexp_replace
等函数替换反斜线。一个JSON对象中可以出现相同的Key,可以成功解析。
--返回1。 select get_json_object('{"a":"1","a":"2"}', '$.a');
支持Emoji表情符号对应的编码字符串。但DataWorks暂不支持输入Emoji表情符号,仅支持通过数据集成等工具直接将Emoji表情符号对应的编码字符串写入MaxCompute,再用
get_json_object
函数处理。--返回Emoji符号。 select get_json_object('{"a":"<Emoji符号>"}', '$.a');
输出结果按照JSON字符串的原始排序方式输出。
--返回{"b":"1","a":"2"}。 select get_json_object('{"b":"1","a":"2"}', '$');
当设置
set odps.sql.udf.getjsonobj.new=false;
时,函数返回行为采用了JSON保留字符转义的方式进行输出。函数返回行为规则如下:换行符(\n)、引号(")等JSON保留字符使用字符串
'\n'
、'\"'
显示。一个JSON对象中不可以出现相同的Key,可能导致无法解析。
--返回NULL。 select get_json_object('{"a":"1","a":"2"}', '$.a');
不支持解析Emoji表情符号编码的字符串。
--返回NULL。 select get_json_object('{"a":"<Emoji符号>"}', '$.a');
输出结果按照字典排序方式输出。
--返回{"a":"2","b":"1"}。 select get_json_object('{"b":"1","a":"2"}', '$');
说明自2021年1月21日及之后新创建的MaxCompute项目中,
get_json_object
函数的返回行为默认为保留原始字符串。2021年1月21日之前创建的MaxCompute项目中,get_json_object
函数的返回行为默认为JSON保留字符转义。您可以通过以下示例判断MaxCompute项目中get_json_object
函数采用了哪种行为,执行命令如下:select get_json_object('{"a":"[\\"1\\"]"}', '$.a'); --JSON保留字符转义的行为返回: [\"1\"] --保留原始字符串的行为返回: ["1"]
您可以通过申请链接或搜索(钉钉群号:11782920)加入MaxCompute开发者社区钉群联系MaxCompute技术支持团队,将您的项目中的
get_json_object
函数返回行为切换为保留原始字符串的行为,避免在Session级别频繁设置属性。
示例
示例1:提取JSON对象
src_json.json
中的信息。命令示例如下。--JSON对象src_json.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" } --提取owner字段信息,返回amy。 select get_json_object(src_json.json, '$.owner') from src_json; --提取store.fruit字段第一个数组信息,返回{"weight":8,"type":"apple"}。 select get_json_object(src_json.json, '$.store.fruit[0]') from src_json; --提取不存在的字段信息,返回NULL。 select get_json_object(src_json.json, '$.non_exist_key') from src_json;
示例2:提取数组型JSON对象的信息。命令示例如下。
--返回2222。 select get_json_object('{"array":[["aaaa",1111],["bbbb",2222],["cccc",3333]]}','$.array[1][1]'); --返回["h0","h1","h2"]。 set odps.sql.udf.getjsonobj.new=true; select get_json_object('{"aaa":"bbb","ccc":{"ddd":"eee","fff":"ggg","hhh":["h0","h1","h2"]},"iii":"jjj"}','$.ccc.hhh[*]'); --返回["h0","h1","h2"]。 set odps.sql.udf.getjsonobj.new=false; select get_json_object('{"aaa":"bbb","ccc":{"ddd":"eee","fff":"ggg","hhh":["h0","h1","h2"]},"iii":"jjj"}','$.ccc.hhh'); --返回h1。 select get_json_object('{"aaa":"bbb","ccc":{"ddd":"eee","fff":"ggg","hhh":["h0","h1","h2"]},"iii":"jjj"}','$.ccc.hhh[1]');
示例3:提取带有
.
的JSON对象中的信息。命令示例如下。--创建一张表。 create table mf_json (id string, json string); --向表中插入数据,Key带.。 insert into table mf_json (id, json) values ("1", "{ \"China.beijing\":{\"school\":{\"id\":0,\"book\":[{\"title\": \"A\", \"price\": 8.95},{\"title\": \"B\",\"price\": 10.2}]}}}"); --向表中插入数据,Key不带.。 insert into table mf_json (id, json) values ("2", "{ \"China_beijing\":{\"school\":{\"id\":0,\"book\":[{\"title\": \"A\", \"price\": 8.95},{\"title\": \"B\",\"price\": 10.2}]}}}"); --取id的值,查询key为China.beijing,返回0。由于包含.,只能用['']来解析。 select get_json_object(json, "$['China.beijing'].school['id']") from mf_json where id =1; --取id的值,查询key为China_beijing,返回0。查询方法有如下两种。 select get_json_object(json, "$['China_beijing'].school['id']") from mf_json where id =2; select get_json_object(json, "$.China_beijing.school['id']") from mf_json where id =2;
示例4:JSON输入为空或非法格式。命令示例如下。
--返回NULL。 select get_json_object('','$.array[1][1]'); --返回NULL。 select get_json_object('"array":["aaaa",1111],"bbbb":["cccc",3333]','$.array[1][1]');
示例5:JSON字符串涉及转义。命令示例如下。
set odps.sql.udf.getjsonobj.new=true; --返回"1"。 select get_json_object('{"a":"\\"1\\"","b":"2"}', '$.a'); --返回'1'。 select get_json_object('{"a":"\'1\'","b":"2"}', '$.a');
INDEX
命令格式
index(<var1>[<var2>])
命令说明
如果var1是
array<T>
类型,获取var1的第var2个元素。ARRAY数组元素编号自左往右,从0开始计数。如果var1是
map<K, V>
类型,获取var1中Key为var2的Value。
说明使用该函数时需要去掉
index
,请直接执行<var1>[<var2>]
,否则会返回报错。参数说明
var1:必填。
array<T>
类型或map<K, V>
类型。array<T>
中的T
指代ARRAY数组元素的数据类型,数组中的元素可以为任意类型。map<K, V>
中的K
、V
指代MAP对象的Key、Value。var2:必填。
如果var1是
array<T>
类型,则var2为BIGINT类型且大于等于0。如果var1是
map<K, V>
类型,则var2与K的类型保持一致。
返回值说明
如果var1是
array<T>
类型,函数返回T类型。返回规则如下:如果var2超出var1的元素数目范围,返回结果为NULL。
如果var1为NULL,返回结果为NULL。
如果var1是
map<K, V>
类型,函数返回V类型。返回规则如下:如果
map<K, V>
中不存在Key为var2的情况,返回结果为NULL。如果var1为NULL,返回结果为NULL。
示例
示例1:var1为
array<T>
类型。命令示例如下。--返回c。 select array('a','b','c')[2];
示例2:var1为
map<K, V>
类型,命令示例如下。--返回1。 select str_to_map("test1=1,test2=2")["test1"];
INLINE
命令格式
inline(array<struct<f1:T1, f2:T2[, ...]>>)
命令说明
将给定的STRUCT数组展开。每个数组元素对应一行,每行每个STRUCT元素对应一列。
参数说明
f1:T1、f2:T2:必填。可以为任意类型。
f1
、f2
代表成员变量,T1
、T2
分别代表成员变量f1
、f2
的取值。返回值说明
返回STRUCT数组展开的数据。
示例
例如表
t_table
的字段为t_struct struct<user_id:bigint,user_name:string,married:string,weight:double>
,包含数据如下:+----------+ | t_struct | +----------+ | {user_id:10001, user_name:LiLei, married:N, weight:63.5} | | {user_id:10002, user_name:HanMeiMei, married:Y, weight:43.5} | +----------+
命令示例如下。
--将t_struct列展开。 select inline(array(t_struct)) from t_table; --返回结果如下。 +------------+-----------+---------+------------+ | user_id | user_name | married | weight | +------------+-----------+---------+------------+ | 10001 | LiLei | N | 63.5 | | 10002 | HanMeiMei | Y | 43.5 | +------------+-----------+---------+------------+
JSON_TUPLE
命令格式
string json_tuple(string <json>, string <key1>, string <key2>,...)
命令说明
用于一个标准的JSON字符串中,按照输入的一组键
(key1,key2,...)
抽取各个键指定的字符串。参数说明
json:必填。STRING类型,标准的JSON格式字符串。
key:必填。STRING类型,用于描述在JSON中的
path
,一次可输入多个,不能以美元符号($)开头。MaxCompute支持用.
或['']
这两种字符解析JSON,当JSON的Key本身包含.
时,可以用['']
来替代。
返回值
返回STRING类型。
说明如果JSON为空或者为非法的JSON格式,返回NULL。
如果键Key为空或者不合法(JSON中不存在)返回NULL。
如果JSON合法,键Key也存在,则返回对应字符串。
支持包含中文的JSON数据解析。
支持多层嵌套的JSON数据解析。
支持包含多重嵌套的数组的JSON数据解析。
解析行为和设置了
set odps.sql.udf.getjsonobj.new=true;
后的GET_JSON_OBJECT的行为保持一致。在需要对同一个JSON字符串多次解析的情况下,相比于多次调用GET_JSON_OBJECT,JSON_TUPLE可以一次输入多个Key,且JSON字符串只被解析一次,效率更高。JSON_TUPLE是UDTF,在需要选取其他列时应配合Lateral View使用。
示例
--创建一张表school。 create table school (id string, json string); --向表中插入数据。 insert into school (id, json) values ("1", "{ \"school\": \"湖畔大学\", \"地址\":\"杭州\", \"SchoolRank\": \"00\", \"Class1\":{\"Student\":[{\"studentId\":1,\"scoreRankIn3Year\":[1,2,[3,2,6]]}, {\"studentId\":2,\"scoreRankIn3Year\":[2,3,[4,3,1]]}]}}");
示例1:提取JSON对象信息。命令示例如下。
select json_tuple(school.json,"SchoolRank","Class1") as (item0, item1) from school; --等效于如下语句。 select get_json_object(school.json,"$.SchoolRank") item0,get_json_object(school.json,"$.Class1") item1 from school; --返回结果如下。 +-------+-------+ | item0 | item1 | +-------+-------+ | 00 | {"Student":[{"studentId":1,"scoreRankIn3Year":[1,2,[3,2,6]]},{"studentId":2,"scoreRankIn3Year":[2,3,[4,3,1]]}]} | +-------+-------+
示例2:使用
['']
提取JSON对象信息。命令示例如下。select json_tuple(school.json,"school","['Class1'].Student") as (item0, item2) from school where id=1; --返回结果如下。 +-------+-------+ | item0 | item2 | +-------+-------+ | 湖畔大学 | [{"studentId":1,"scoreRankIn3Year":[1,2,[3,2,6]]},{"studentId":2,"scoreRankIn3Year":[2,3,[4,3,1]]}] |
示例3:支持解析包含中文的JSON数据。命令示例如下。
select json_tuple(school.json,"校名","地址") as (item0,item1) from school; --返回结果如下。 +-------+-------+ | item0 | item1 | +-------+-------+ | 湖畔大学 | 杭州 | +-------+-------+
示例4:支持解析多层嵌套的JSON数据。命令示例如下。
select sc.Id, q.item0, q.item1 from school sc lateral view json_tuple(sc.json,"Class1.Student[*].studentId","Class1.Student[0].scoreRankIn3Year") q as item0,item1; --返回结果如下。 +------------+-------+-------+ | id | item0 | item1 | +------------+-------+-------+ | 1 | [1,2] | [1,2,[3,2,6]] | +------------+-------+-------+
示例5:支持解析包含多重嵌套数组的JSON数据。命令示例如下。
select sc.Id, q.item0, q.item1 from school sc lateral view json_tuple(sc.json,"Class1.Student[0].scoreRankIn3Year[2]","Class1.Student[0].scoreRankIn3Year[2][1]") q as item0,item1; --返回结果如下。 +------------+-------+-------+ | id | item0 | item1 | +------------+-------+-------+ | 1 | [3,2,6] | 2 | +------------+-------+-------+
JSON_OBJECT
命令格式
json json_object(<key1>,<value1>[,<keyn>,<valuen>])
命令说明
生成JSON OBJECT,要求key和value成对出现。
参数说明
key:最少需要存在1个key,也可以存在多个,支持的类型为STRING。
value:最少需要存在1个value,也可以存在多个,支持的类型包括STRING、BIGINT、INT和BOOLEAN。
返回值说明
返回JSON类型。
示例
示例1:只包含一对key和value。
--生成json对象 select json_object('a', 123);
返回结果:
+-----+ | _c0 | +-----+ | {"a":123} | +-----+
示例2:包含多对key和value。
--生成json对象 select json_object('a', 123,'b','hello');
返回结果:
+-----+ | _c0 | +-----+ | {"a":123,"b":"hello"} | +-----+
JSON_ARRAY
命令格式
json json_array(<element>)
命令说明
生成JSON ARRAY。
参数说明
element:必填。该参数支持的类型包括STRING、BIGINT、BOOLEAN和JSON。
返回值说明
返回JSON类型。
示例
--生成json array select json_array('a', 45, true, 13, json '{"a":456}');
返回结果:
+-----+ | _c0 | +-----+ | ["a",45,true,13,{"a":456}] | +-----+
JSON_EXTRACT
命令格式
json json_extract(<json>, <json_path>)
命令说明
解析JSON表达式中对应json_path的数据,注意json_path非法时会报错。
参数说明
json:必填,待处理的JSON。
json_path:必填,需要返回的值的JSON路径。
返回值说明
返回JSON类型。
示例
示例1:从JSON中获取key为a的value值。
select json_extract(json '{"a":1, "b":2}', '$.a');
返回结果:
+-----+ | _c0 | +-----+ | 1 | +-----+
示例2:当JSON值不存在时,返回NULL。
select json_extract(json '{"a":1, "b":2}', 'strict $.c');
返回结果:
+-----+ | _c0 | +-----+ | NULL | +-----+
示例3:JSON Path格式非法时,返回报错信息
Invalid argument - Param json path $invalid_json_path is invalid
。select json_extract(json '{"a":1, "b":2}', '$a');
返回结果:
--返回报错信息 Invalid argument - Param json path $invalid_json_path is invalid
JSON_EXISTS
命令格式
boolean json_exists(<json>, <json_path>)
命令说明
查看json_path对应的JSON值是否存在。
参数说明
json:必填,待处理的JSON。
json_path:必填,需要返回的值的JSON路径。
返回值说明
返回BOOLEAN类型的true或者false。
示例
示例1:从JSON中查询key为a的value值是否存在。
select json_exists(json '{"a":1, "b":2}', '$.a');
返回结果:
+------+ | _c0 | +------+ | true | +------+
示例2:从JSON中查询key为c的value值是否存在。
select json_exists(json '[1,2, {"a":34}]', '$[2].a');
返回结果:
+------+ | _c0 | +------+ | true | +------+
示例3:根据下标位置取回对应的values是否存在。
select json_exists(json '{"a":1, "b":2}', 'strict $.c');
返回结果:
+------+ | _c0 | +------+ | false | +------+
JSON_PRETTY
命令格式
string json_pretty(<json>)
命令说明
美化JSON,增加换行及空格。
参数说明
json:必填,待处理的JSON。
返回值说明
返回STRING类型。
示例
--美化json select json_pretty(json '{"a":1, "b":2}');
返回结果:
+-----+ | _c0 | +-----+ | { "a":1, "b":2 } | +-----+
JSON_TYPE
命令格式
string|number|boolean|null|object|array json_type(<json>)
命令说明
返回JSON数据所属的数据类型名称。
参数说明
json:必填,待处理的JSON表达式。
返回值说明
返回STRING类型。
示例
示例1:返回JSON内的数据类型(array类型)。
select json_type(json '[{"a":1}, 23]');
返回结果:
+-----+ | _c0 | +-----+ | array | +-----+
示例2:返回JSON内的数据类型(number类型)。
select json_type(json '123');
返回结果:
+-----+ | _c0 | +-----+ | number | +-----+
示例3:返回JSON内的数据类型(string类型)。
select json_type(json '"123"');
返回结果:
+-----+ | _c0 | +-----+ | string | +-----+
JSON_FORMAT
命令格式
string json_format(<json>)
命令说明
将JSON数据转换成STRING类型,默认不自动进行美化。
参数说明
json:必填,待处理的JSON。
返回值说明
返回STRING类型。
示例
示例1:将NUMBER类型的JSON数据转换为字符串。
select json_format(json '123');
返回结果:
+-----+ | _c0 | +-----+ | 123 | +-----+
示例2:将STRING类型的JSON数据转换为字符串。
select json_format(json '"123"');
返回结果:
+-----+ | _c0 | +-----+ | "123" | +-----+
JSON_PARSE
命令格式
json json_parse(<string>)
命令说明
将STRING类型转成JSON类型,非JSON格式转换为字符串会报错。
参数说明
string:必填,待处理的STRING字符串。
返回值说明
返回JSON类型。
示例
示例1:字符串转换为JSON类型。
select json_parse('{"a":1, "b":2}');
返回结果:
+-----+ | _c0 | +-----+ | {"a":1,"b":2} | +-----+
示例2:字符串转换为JSON类型。
select json_parse('"abc"');
返回结果:
+-----+ | _c0 | +-----+ | "abc" | +-----+
示例3:无效字符串转换为JSON报错。
select json_parse('abc');
返回结果:
Invalid input syntax for type json, detail:Token "abc" is invalid.
JSON_VALID
命令格式
boolean json_valid(<string>)
命令说明
检查字符串是否为合法的JSON格式。
参数说明
string:必填,待处理的JSON字符串。
返回值说明
返回BOOLEAN类型的true或者false。
示例
示例1:检查
"abc"
是否为合法的JSON格式字符串。select json_valid('"abc"');
返回结果:
+------+ | _c0 | +------+ | true | +------+
示例2:检查
abc
是否为合法的JSON格式字符串。select json_valid('abc');
返回结果:
+------+ | _c0 | +------+ | false | +------+
CAST
命令格式
json/string/bigint/int/tinyint/smallint/double/float/boolean/sql-type cast(json as string/ string as json/ json as bigint/ bigint as json/ json as int/ int as json/ json as tinyint/ tinyint as json/ json as smallint/ smallint as json/ json as double/ double as json/ json as float/ float as json/ boolean as json/ json as boolean/ null as json/ json 'null' as ... )
命令说明
支持基本类型与JSON类型的转换。
参数说明
支持的参数类型包括
JSON/STRING/BIGINT/INT/TINYINT/SMALLINT/DOUBLE/FLOAT/BOOLEAN/SQL-TYPE
。JSON类型转换为STRING:JSON数据要求为非ARRAY和OBJECT类型。
STRING转换为JSON:输出的JSON数据在JSON语法中为STRING类型。注意其与JSON_PARSE和JSON_FORMAT的区别:
JSON_PARSE只支持合法的JSON STRING转换为JSON,而且可以转换成JSON OBJECT。
而CAST函数可以将任意STRING转换为JSON STRING,转换后的JSON数据为STRING类型。
json 'null'和null会转换成sql null。
返回值说明
返回对应的JSON类型和基础数据类型。
示例
示例1:STRING和JSON类型相互转换。
--json转成string select cast(json '123' as string); --返回: +-----+ | _c0 | +-----+ | 123 | +-----+ --json转成string select cast(json '"abc"' as string); --返回: +-----+ | _c0 | +-----+ | abc | +-----+ --json转成string select cast(json 'true' as string); --返回: +-----+ | _c0 | +-----+ | TRUE | +-----+ --json转成string select cast(json 'null' as string); --返回: +-----+ | _c0 | +-----+ | NULL | +-----+ --string转成json select cast('{"a":2}' as json); --返回: +-----+ | _c0 | +-----+ | "{\"a\":2}" | +-----+ --json转成string的错误示例,不支持array/object类型的JSON表达式转换为string。 select cast(json '{"a":2}' as string); --返回报错: FAILED: ODPS-0123091:Illegal type cast - Unsupported cast from json array/object to string
示例2:NUMBER和JSON类型相互转换。
--json转成bigint select cast(json '123' as bigint); --返回: +------------+ | _c0 | +------------+ | 123 | +------------+ --json转成float select cast(json '"1.23"' as float); --返回: +------+ | _c0 | +------+ | 1.23 | +------+ --json转成double select cast(json '1.23' as double); --返回: +------------+ | _c0 | +------------+ | 1.23 | +------------+ --int转成json select cast(123 as json); --返回: +-----+ | _c0 | +-----+ | 123 | +-----+ --float转成json select cast(1.23 as json); --返回: +-----+ | _c0 | +-----+ | 1.23 | +-----+
示例3:BOOLEAN和JSON类型的相互转换。
--boolean转成bigint select cast(true as json); --返回: +-----+ | _c0 | +-----+ | true | +-----+ --json转成boolean select cast(json 'false' as boolean); --返回: +------+ | _c0 | +------+ | false | +------+ --json转成boolean select cast(json '"abc"' as boolean); --返回: +------+ | _c0 | +------+ | true | +------+ --array/object不能转成boolean select cast(json '[1,2]' as boolean); --返回报错: Unsupported cast from json array/object to boolean
示例4:NULL和JSON类型的相互转换。
--null转成string select json_type(cast(null as json)); --返回: +-----+ | _c0 | +-----+ | NULL | +-----+
MAP
命令格式
map(K, V) map(K <key1>, V <value1>, K <key2>, V <value2>[, ...])
命令说明
使用给定的Key-Value对生成MAP。
参数说明
key:必填。所有key类型一致(包括隐式转换后类型一致),必须是基本类型。
value:必填。所有value类型一致(包括隐式转换后类型一致),支持除Decimal之外的其它数据类型。
返回值说明
返回MAP类型。
说明您可以在Session级别通过
odps.sql.map.key.dedup.policy
参数设置出现重复Key时的处理方式。取值范围如下:exception:如果出现重复的Key,返回报错。
last_win:如果出现重复的Key,后边的值将覆盖前边的值。
不设置时,该参数默认值为last_win。
示例
示例1:无重复Key。例如表
t_table
的字段为c1 bigint, c2 string, c3 string, c4 bigint, c5 bigint
,包含数据如下。+------------+----+----+------------+------------+ | c1 | c2 | c3 | c4 | c5 | +------------+----+----+------------+------------+ | 1000 | k11 | k21 | 86 | 15 | | 1001 | k12 | k22 | 97 | 2 | | 1002 | k13 | k23 | 99 | 1 | +------------+----+----+------------+------------+
命令示例如下。
--将c2、c4,c3、c5组成MAP。 select map(c2,c4,c3,c5) from t_table; --返回结果如下。 +------+ | _c0 | +------+ | {k11:86, k21:15} | | {k12:97, k22:2} | | {k13:99, k23:1} | +------+
示例2。有重复Key。例如表
t_table
的字段为c1 bigint, c2 string, c3 string, c4 bigint, c5 bigint
,包含数据如下。1000,'k11','k11',86,15 1001,'k12','k22',97,2 1002,'k13','k23',99,1 1003,'k13','k24',100,1 1004,'k12','k25',95,1
命令示例如下。
--将c2、c4,c3、c5组成MAP。 select map(c2,c4,c3,c5) from t_table; --返回结果如下。 +------+ | _c0 | +------+ | {'k11':15} | | {'k12':97, 'k22':2} | | {'k13':99, 'k23':1} | | {'k13':100, 'k24':1} | | {'k12':95, 'k25':1} | +------+
MAP_CONCAT
命令格式
map<K, V> map_concat([string <mapDupKeyPolicy>,] map<K, V> <a>, map<K, V> <b>[,...])
命令说明
计算多个MAP对象的并集。
参数说明
mapDupKeyPolicy:可选。STRING类型。指定出现重复Key时的处理方式。取值范围如下:
exception:如果出现重复的Key,返回报错。
last_win:如果出现重复的Key,后边的值将覆盖前边的值。
该参数也可以在Session级别通过
odps.sql.map.key.dedup.policy
参数进行设置,例如set odps.sql.map.key.dedup.policy=exception;
,不设置时该参数默认值为last_win。说明MaxCompute的行为实现优先以函数中mapDupKeyPolicy的取值为准,当函数未配置mapDupKeyPolicy时,以
odps.sql.map.key.dedup.policy
参数的取值为准。a、b:必填。MAP对象。多个MAP对象的参数数据类型必须一致。
map<K, V>
中的K
、V
指代MAP对象的Key、Value。
返回值说明
返回MAP类型。返回规则如下:
某个MAP对象为NULL或某个MAP对象的Key为NULL时,返回报错。
多个MAP对象的数据类型不一致时,返回报错。
示例
--返回{1:a, 2:b, 3:c}。 select map_concat(map(1, 'a', 2, 'b'), map(3, 'c')); --返回{1:a, 2:d, 3:c}。 select map_concat('last_win', map(1, 'a', 2, 'b'), map(3, 'c'), map(2, 'd'));
MAP_ENTRIES
命令格式
array<struct<K, V>> map_entries(map<K, V> <a>):
命令说明
将MAP对象a的K、Value映射转换为STRUCT结构数组。
参数说明
a:必填。MAP对象。
map<K, V>
中的K
、V
指代MAP对象的Key、Value。返回值说明
返回STRUCT结构数组。如果输入为NULL,返回结果为NULL。
示例
--返回[{key:1, value:a}, {key:2, value:b}]。 select map_entries(map(1, 'a', 2, 'b'));
MAP_FILTER
命令格式
map<K, V> map_filter(map<K, V> <input>, function <K, V, boolean> <predicate>)
命令说明
将MAP对象input的元素进行过滤,只保留满足predicate条件的元素。
参数说明
input:必填。MAP类型。
map<K, V>
中的K
、V
指代MAP对象的Key、Value。predicate:必填。用于对输入MAP对象中的元素进行过滤的函数(内建函数或自定义函数)或表达式。它的两个输入参数,分别对应input中的Key和Value,输出结果为BOOLEAN类型。
返回值说明
返回MAP类型。
示例
--返回{-30:100, 20:50}。 select map_filter(map(10, -20, 20, 50, -30, 100, 21, null), (k, v) -> (k+v) > 10);
MAP_FROM_ARRAYS
命令格式
map<K, V> map_from_arrays([string <mapDupKeyPolicy>,] array<K> <a>, array<V> <b>))
命令说明
将ARRAY数组a和b组合成一个MAP对象。
参数说明
mapDupKeyPolicy:可选。STRING类型。指定出现重复Key时的处理方式。取值范围如下:
exception:如果出现重复的Key,返回报错。
last_win:如果出现重复的Key,后边的值将覆盖前边的值。
该参数也可以在Session级别通过
odps.sql.map.key.dedup.policy
参数进行设置,例如set odps.sql.map.key.dedup.policy=exception;
,不设置时该参数默认值为last_win。说明MaxCompute的行为实现优先以函数中mapDupKeyPolicy的取值为准,当函数未配置mapDupKeyPolicy时,以
odps.sql.map.key.dedup.policy
参数的取值为准。a:必填。ARRAY数组。对应生成MAP的Key值。
array<K>
中的K
指代ARRAY数组元素的数据类型,数组中的元素可以为任意类型。b:必填。ARRAY数组。对应生成MAP的Value值。
array<V>
中的V
指代ARRAY数组元素的数据类型,数组中的元素可以为任意类型。
返回值说明
返回MAP类型。返回规则如下:
如果a或b为NULL,返回结果为NULL。
如果a中元素包含NULL值或两个数组长度不相等,会返回报错。
示例
--返回{1:2, 3:4}。 select map_from_arrays(array(1.0, 3.0), array('2', '4')); --返回{1:2, 3:6}。 select map_from_arrays('last_win', array(1.0, 3.0, 3), array('2', '4', '6'));
MAP_FROM_ENTRIES
命令格式
map<K, V> map_from_entries([string <mapDupKeyPolicy>,] array <struct<K, V> , struct<K, V>[,...]>)
命令说明
将多个结构数组组合成一个MAP对象。
参数说明
mapDupKeyPolicy:可选。STRING类型。指定出现重复Key时的处理方式。取值范围如下:
exception:如果出现重复的Key,返回报错。
last_win:如果出现重复的Key,后边的值将覆盖前边的值。
该参数也可以在Session级别通过
odps.sql.map.key.dedup.policy
参数进行设置,例如set odps.sql.map.key.dedup.policy=exception;
,不设置时该参数默认值为last_win。说明MaxCompute的行为实现优先以函数中mapDupKeyPolicy的取值为准,当函数未配置mapDupKeyPolicy时,以
odps.sql.map.key.dedup.policy
参数的取值为准。输入为STRUCT类型的数据。其中:K对应生成MAP的Key值,V对应生成MAP的Value值。
struct<K, V>
中的K
、V
指代STRUCT的Key、Value。
返回值说明
返回MAP类型。返回规则如下:
如果结构体数组为NULL,返回结果为NULL。
如果结构体的Field数量不是2或K包含NULL值,会返回报错。
示例
--返回{1:a, 2:b}。 select map_from_entries(array(struct(1, 'a'), struct(2, 'b'))); --返回{1:a, 2:c}。 select map_from_entries(array(struct(1, 'a'), struct(2, 'b'), struct(2, 'c')));
MAP_KEYS
命令格式
array<K> map_keys(map<K, V> <a>)
命令说明
将MAP对象a中的所有Key生成ARRAY数组。
参数说明
a:必填。MAP对象。
map<K, V>
中的K
、V
指代MAP对象的Key、Value。返回值说明
返回ARRAY类型。输入MAP对象为NULL时,返回结果为NULL。
示例
例如表
t_table_map
的字段为c1 bigint,t_map map<string,bigint>
,包含数据如下:+------------+-------+ | c1 | t_map | +------------+-------+ | 1000 | {k11:86, k21:15} | | 1001 | {k12:97, k22:2} | | 1002 | {k13:99, k23:1} | +------------+-------+
命令示例如下。
--将t_map中的Key作为数组返回。 select c1, map_keys(t_map) from t_table_map; --返回结果如下。 +------------+------+ | c1 | _c1 | +------------+------+ | 1000 | [k11, k21] | | 1001 | [k12, k22] | | 1002 | [k13, k23] | +------------+------+
MAP_VALUES
命令格式
array<V> map_values(map<K, V> <a>)
命令说明
将MAP对象a中的所有Value生成ARRAY数组。
参数说明
a:必填。MAP对象。
map<K, V>
中的K
、V
指代MAP对象的Key、Value。返回值说明
返回ARRAY类型。输入MAP对象为NULL时,返回结果为NULL。
示例
例如表
t_table_map
的字段为c1 bigint,t_map map<string,bigint>
,包含数据如下:+------------+-------+ | c1 | t_map | +------------+-------+ | 1000 | {k11:86, k21:15} | | 1001 | {k12:97, k22:2} | | 1002 | {k13:99, k23:1} | +------------+-------+
命令示例如下。
--将t_map中的Key作为数组返回。 select c1,map_values(t_map) from t_table_map; --返回结果如下。 +------------+------+ | c1 | _c1 | +------------+------+ | 1000 | [86, 15] | | 1001 | [97, 2] | | 1002 | [99, 1] | +------------+------+
MAP_ZIP_WITH
命令格式
<K, V1, V2, V3> map<K, V3> map_zip_with(map<K, V1> <input1>, map<K, V2> <input2>, function<K, V1, V2, V3> <func>)
命令说明
对输入的两个MAP对象input1和input2进行合并得到一个新MAP对象。新MAP的Key是两个MAP的Key的并集。针对新MAP的每一个Key,通过func来计算它的Value。
参数说明
input1、input2:必填。MAP对象。
map<K, V>
中的K
、V
指代MAP对象的Key、Value。func:必填。func有三个输入参数,分别对应MAP的Key、Key相对应的input1以及input2的Value。如果Key在input1或者input2中不存在,func对应参数补充为NULL。
返回值说明
返回func定义的类型。
示例
--返回{1:[1, 1, 4], 2:[2, 2, 5], 3:[3, NULL, NULL], 4:[4, NULL, 7]}。 select map_zip_with(map(1, 1, 2, 2, 3, null), map(1, 4, 2, 5, 4, 7), (k, v1, v2) -> array(k, v1, v2));
MULTIMAP_FROM_ENTRIES
命令格式
multimap_from_entries(array<struct<K, V>>)
命令说明
返回由结构体数组中的Key和包含所有Value的数组所组成的Map。
参数说明
array<struct<K, V>>:为Key/Value组成的结构体数组。
返回值说明
返回由结构体数组中的Key和包含所有Value的数组所组成的Map,Map格式为
map<K, array<V>>
。在返回的Map中每个Key可以与多个值相关联,这些相关联的值存放在一个数组里。
如果数组为Null,返回Null值。
如果结构体中的字段(StructFiled)数量不是2或Key包含Null值,则抛出异常。
示例
SELECT multimap_from_entries(array(struct(1, 'a'), struct(2, 'b'), struct(1, 'c')));
返回结果如下:
{1 : ['a', 'c'], 2: ['b']}
NAMED_STRUCT
命令格式
struct named_struct(string <name1>, T1 <value1>, string <name2>, T2 <value2>[, ...])
命令说明
使用指定的name、value列表建立STRUCT。
参数说明
value:必填。可以为任意类型。
name:必填。指定STRING类型的Field名称。此参数为常量。
返回值说明
返回STRUCT类型。Field的名称依次为
name1,name2,…
。示例
--返回{user_id:10001, user_name:LiLei, married:F, weight:63.5}。 select named_struct('user_id',10001,'user_name','LiLei','married','F','weight',63.50);
NGRAMS
命令格式
ngrams(array(T), n)
命令说明
返回指定数组元素的N元语法(n-gram)数组。
参数说明
array:为输入数组。
n:元数。
返回值说明
返回指定数组元素的N元语法(n-gram)数组。
如果
n <= 0
, 则抛出异常。示例
SELECT ngrams(array('foo', 'bar', 'baz', 'foo'), 2);
返回结果如下:
[['foo', 'bar'], ['bar', 'baz'], ['baz', 'foo']]
SELECT ngrams(array('foo', 'bar', 'baz', 'foo'), 3);
返回结果如下:
[['foo', 'bar', 'baz'], ['bar', 'baz', 'foo']]
SELECT ngrams(array('foo', 'bar', 'baz', 'foo'), 4);
返回结果如下:
[['foo', 'bar', 'baz', 'foo']]
SELECT ngrams(array('foo', 'bar', 'baz', 'foo'), 5);
返回结果如下:
[['foo', 'bar', 'baz', 'foo']]
SELECT ngrams(array(1, 2, 3, 4), 2);
返回结果如下:
[[1, 2], [2, 3], [3, 4]]
POSEXPLODE
命令格式
posexplode(array<T> <a>)
命令说明
将ARRAY数组a展开,每个Value一行,每行两列分别对应数组从0开始的下标和数组元素。
参数说明
a:必填。ARRAY数组。
array<T>
中的T
指代ARRAY数组元素的数据类型,数组中的元素可以为任意类型。返回值说明
返回表。
示例
select posexplode(array('a','c','f','b')); --返回结果如下。 +------------+------------+ | pos | val | +------------+------------+ | 0 | a | | 1 | c | | 2 | f | | 3 | b | +------------+------------+
REVERSE
命令格式
array reverse(array <value>)
命令说明
根据输入数组生成一个元素倒序的数组。
参数说明
value:输入数组。
返回值说明
返回输入数组元素倒序的数组。如果输入值为null,则返回NULL。
示例
--返回[3, 4, 1, 2] SELECT reverse(array(2, 1, 4, 3));
SEQUENCE
命令格式
sequence(start, stop, [step]) -> array
命令说明
根据表达式生成包含指定元素的数组。
参数说明
start:表示元素序列开始的表达式,元素序列包含start。
start和stop支持的整数类型包括:Tinyint 、SmallInt 、Int、BigInt;对应的step类型分别为:Tinyint 、SmallInt 、Int 、BigInt。
start和stop支持的时间日期类型包括:Date、DateTime、Timestamp;对应的step类型为IntervalDayTime或IntervalYearMonth。
stop:表示元素序列结束的表达式,元素序列包含stop。
step:可选参数。元素序列步长值。
默认情况下, 当start小于等于stop时, step为1,否则为-1。
如果元素序列为时间类型时,默认分别为1天或-1天;如果提供step值,当start大于stop时,step必须为负数,反之必须为正数,否则抛出异常。
返回值说明
返回由指定表达式生成元素组成的数组。
如果start大于stop而step为正数时抛出异常,反之亦然。
sequence函数默认生成的元素数量上限为10000,可以通过设置
odps.sql.max.sequence.length
Flag值改变元素数量上限。
示例
SELECT sequence(1, 5);
返回结果如下:
[1, 2, 3, 4, 5]
SELECT sequence(5, 1);
返回结果如下:
[5, 4, 3, 2, 1]
SELECT sequence(to_date('2018-01-01'), to_date('2018-03-01'), interval 1 month);
返回结果如下:
[2018-01-01, 2018-02-01, 2018-03-01]
SHUFFLE
命令格式
shuffle(array)
命令说明
参数说明
array:输入数组。
返回值说明
返回指定数组的元素随机排列数组。
如果输入值为null,则返回NULL。
该函数的返回结果是不确定的。
示例
SELECT shuffle(array(1, 20, 3, 5));
返回结果如下:
[3,1,5,20]
SELECT shuffle(array(1, 20, null, 3));
返回结果如下:
[20,null,3,1]
SIZE
命令格式
int size(array<T> <a>) int size(map<K, V> <b> )
命令说明
输入为ARRAY数组:计算ARRAY数组a中的元素数目。
输入为MAP对象:计算MAP对象b中的Key-Value对数。
参数说明
a:必填。ARRAY数组。
array<T>
中的T
指代ARRAY数组元素的数据类型,数组中的元素可以为任意类型。b:必填。MAP对象。
map<K, V>
中的K
、V
指代MAP对象的Key、Value。
返回值说明
返回INT类型。
示例
示例1:计算ARRAY数组
array('a','b')
中的元素数目。命令示例如下。--返回2。 select size(array('a','b'));
示例2:计算MAP对象
map('a',123,'b',456)
中的Key-Value对数。--返回2。 select size(map('a',123,'b',456));
SLICE
命令格式
array<T> slice(array<T> <a>, <start>, <length>)
命令说明
对ARRAY数组切片,截取从start位置开始长度为length的元素组成新的ARRAY数组。
参数说明
a:必填。ARRAY数组。
array<T>
中的T
指代ARRAY数组元素的数据类型,数组中的元素可以为任意类型。start:必填。切片起点,从1开始,表示从数组的首个元素开始向右切片。start可以为负数,表示从数组的末尾元素开始向右切片。
length:必填。切片长度,必须大于或等于0。切片长度如果大于ARRAY数组长度时,会返回从start位置开始到末尾元素组成的ARRAY数组。
返回值说明
返回ARRAY类型。
示例
示例1:截取ARRAY数组
array(10, 20, 20, null, null, 30)
从第1
个位置开始,切片长度为3
的元素。命令示例如下。--返回[10, 20, 20]。 select slice(array(10, 20, 20, null, null, 30), 1, 3);
示例2:截取ARRAY数组
array(10, 20, 20, null, null, 30)
从第-2
个位置开始,切片长度为2
的元素。命令示例如下。--返回[NULL, 30]。 select slice(array(10, 20, 20, null, null, 30), -2, 2);
示例3:截取ARRAY数组
array(10, 20, 20, null, null, 30)
从第3
个位置开始,切片长度为10
的元素。命令示例如下。--返回[20, NULL, NULL, 30]。 select slice(array(10, 20, 20, null, null, 30), 3, 10);
示例4:截取ARRAY数组
array(10, 20, 20, null, null, 30)
从第3
个位置开始,切片长度为0
的元素。命令示例如下。--返回[]。 select slice(array(10, 20, 20, null, null, 30), 3, 0);
SORT_ARRAY
命令格式
array<T> sort_array(array<T> <a>[, <isasc>])
命令说明
对ARRAY数组中的元素进行排序。
参数说明
a:必填。ARRAY数组。
array<T>
中的T
指代ARRAY数组元素的数据类型,数组中的元素可以为任意类型。isasc:可选。用于设置排序规则。取值为True(升序)或False(降序)。默认为升序。
返回值说明
返回ARRAY类型。NULL值为最小值。
示例
示例1:例如表
t_array
的字段为c1 array<string>,c2 array<int> ,c3 array<string>
,包含数据如下:+------------+---------+--------------+ | c1 | c2 | c3 | +------------+---------+--------------+ | [a, c, f, b] | [4, 5, 7, 2, 5, 8] | [你, 我, 他] | +------------+---------+--------------+
对表的每列数据进行排序。命令示例如下。
--返回[a, b, c, f] [2, 4, 5, 5, 7, 8] [他, 你, 我]。 select sort_array(c1),sort_array(c2),sort_array(c3) from t_array;
示例2:对ARRAY数组
array(10, 20, 40, 30, 30, null, 50)
进行降序排序。命令示例如下。--返回[50, 40, 30, 30, 20, 10, NULL]。 select sort_array(array(10, 20, 40, 30, 30, null, 50), false);
SPLIT
命令格式
split(<str>, <pat>)
命令说明
通过pat将str分割后返回数组。
参数说明
str:必填。STRING类型。指被分割的字符串。
pat:必填。STRING类型的分隔符。支持正则表达式。更多正则表达式信息,请参见RLIKE字符匹配。
返回值说明
返回ARRAY数组。数组中的元素为STRING类型。
示例
--返回[a, b, c]。 select split("a, b, c", ",");
STRUCT
命令格式
struct struct(<value1>,<value2>[, ...])
命令说明
使用指定value列表建立STRUCT。
参数说明
value:必填。可以为任意类型。
返回值说明
返回STRUCT类型。Field的名称依次为
col1,col2,…
。示例
--返回{col1:a, col2:123, col3:true, col4:56.9}。 select struct('a',123,'true',56.90);
TO_JSON
命令格式
string to_json(<expr>)
命令说明
将给定的复杂类型expr,以JSON字符串格式输出。
参数说明
expr:必填。ARRAY、MAP、STRUCT复杂类型。
说明如果输入为STRUCT类型(
struct<key1:value1, key2:value2
):转换为JSON字符串时,Key会全部转为小写。
value
如果为NULL,则不输出value
本组的数据。例如value2
为NULL,则key2:value2
不会输出到JSON字符串。
返回值说明
返回JSON格式的字符串。
示例
示例1:将指定复杂类型以指定格式输出。命令示例如下。
--返回{"a":1,"b":2}。 select to_json(named_struct('a', 1, 'b', 2)); --返回{"time":"26/08/2015"}。 select to_json(named_struct('time', "26/08/2015")); --返回[{"a":1,"b":2}]。 select to_json(array(named_struct('a', 1, 'b', 2))); --返回{"a":{"b":1}}。 select to_json(map('a', named_struct('b', 1))); --返回{"a":1}。 select to_json(map('a', 1)); --返回[{"a":1}]。 select to_json(array((map('a', 1))));
示例2:输入为STRUCT类型的特殊情况。命令示例如下。
--返回{"a":"B"}。STRUCT类型转换为JSON字符串时,key会全部转为小写。 select to_json(named_struct("A", "B")); --返回{"k2":"v2"}。NULL值所在组的数据,不会输出到JSON字符串。 select to_json(named_struct("k1", cast(null as string), "k2", "v2"));
TRANSFORM
命令格式
array<R> transform(array<T> <a>, function<T, R> <func>)
命令说明
将ARRAY数组a的元素利用func进行转换,返回一个新的ARRAY数组。
参数说明
a:必填。ARRAY数组。
array<T>
中的T
指代ARRAY数组元素的数据类型,数组中的元素可以为任意类型。func:必填。用于对a中元素进行转换的函数(内建函数或自定义函数)或表达式,其输入类型应与a中的元素类型一致。
R
指代输出结果的数据类型。
返回值说明
返回ARRAY类型。
示例
--返回[2, 3, 4]。 select transform(array(1, 2, 3), x -> x + 1);
TRANSFORM_KEYS
命令格式
map<K2, V> transform_keys([string <mapDupKeyPolicy>,] map<K1, V> <input>, function<K1, V, K2> <func>)
命令说明
对MAP对象input进行变换,保持Value不变,通过func计算新的Key值。
参数说明
mapDupKeyPolicy:可选。STRING类型。指定出现重复Key时的处理方式。取值范围如下:
exception:如果出现重复的Key,返回报错。
last_win:如果出现重复的Key,后边的值将覆盖前边的值。
该参数也可以在Session级别通过
odps.sql.map.key.dedup.policy
参数进行设置,例如set odps.sql.map.key.dedup.policy=exception;
,不设置时该参数默认值为last_win。说明MaxCompute的行为实现优先以函数中mapDupKeyPolicy的取值为准,当函数未配置mapDupKeyPolicy时,以
odps.sql.map.key.dedup.policy
参数的取值为准。input:必填。MAP对象。
map<K1, V>
中的K1
、V
指代MAP对象的Key、Value。func:必填。变换的函数(内建函数或自定义函数)或表达式。它的两个输入参数分别对应input的Key和Value,
K2
指代新MAP的Key类型。
返回值说明
返回MAP类型。如果计算的新Key为NULL,会返回报错。
示例
--返回{-10:-20, 70:50, 71:101}。 select transform_keys(map(10, -20, 20, 50, -30, 101), (k, v) -> k + v); --不报错,返回的结果依赖于输入map中元素的顺序。 select transform_keys("last_win", map(10, -20, 20, 50, -30, 100), (k, v) -> k + v); --因出现重复Key,返回报错。 select transform_keys("exception", map(10, -20, 20, 50, -30, 100), (k, v) -> k + v);
TRANSFORM_VALUES
命令格式
map<K, V2> transform_values(map<K, V1> <input>, function<K, V1, V2> <func>)
命令说明
对输入MAP对象input进行变换,保持Key不变,通过func计算新的Value值。
参数说明
input:必填。MAP对象。
map<K, V1>
中的K
、V1
指代MAP对象的Key、Value。func:必填。变换的函数(内建函数或自定义函数)或表达式。它的两个输入参数分别对应input的Key和Value,
V2
指代新MAP的Value类型。
返回值说明
返回MAP类型。
示例
--返回{-30:71, 10:-10, 20:NULL}。 select transform_values(map(10, -20, 20, null, -30, 101), (k, v) -> k + v);
ZIP_WITH
命令格式
array<R> zip_with(array<T> <a>, array<S> <b>, function<T, S, R> <combiner>)
命令说明
将ARRAY数组a和b的元素按照位置,使用combiner进行元素级别的合并,返回一个新的ARRAY数组。
参数说明
a、b:必填。ARRAY数组。
array<T>
、array<S>
中的T
、S
指代ARRAY数组元素的数据类型,数组中的元素可以为任意类型。combiner:必填。用于合并ARRAY数组a、b中元素的函数(内置函数或自定义函数)或表达式。它的两个输入参数类型分别与ARRAY数组a、b中元素的数据类型一致。
返回值说明
返回ARRAY类型。返回规则如下:
新生成的ARRAY数组中元素位置与a、b中相应元素的位置相同。
如果ARRAY数组a和b的长度不一致,会将长度较短的ARRAY数组使用NULL值进行填充,然后进行合并。
示例
--返回[2, 4, 6, NULL]。 select zip_with(array(1,2,3), array(1,2,3,4), (x,y) -> x + y);