您可以在MaxCompute SQL中使用复杂类型函数处理复杂数据类型,例如ARRAY、MAP、STRUCT、JSON。本文为您提供MaxCompute SQL支持的复杂类型函数的命令格式、参数说明及示例,指导您使用复杂类型函数完成开发。

MaxCompute SQL支持的复杂类型函数如下。

函数类别 函数 功能
ARRAY函数 ALL_MATCH 判断ARRAY数组中是否所有元素都满足指定条件。
ANY_MATCH 判断ARRAY数组中是否存在满足指定条件的元素。
ARRAY 使用给定的值构造ARRAY。
ARRAY_CONTAINS 检测指定的ARRAY中是否包含指定的值。
ARRAY_DISTINCT 去除ARRAY数组中的重复元素。
ARRAY_EXCEPT 找出在ARRAY A中,但不在ARRAY B中的元素,并去掉重复的元素后,以ARRAY形式返回结果。
ARRAY_INTERSECT 计算两个ARRAY数组的交集。
ARRAY_JOIN 将ARRAY数组中的元素按照指定字符串进行拼接。
ARRAY_MAX 计算ARRAY数组中的最大值。
ARRAY_MIN 计算ARRAY数组中的最小值。
ARRAY_POSITION 计算指定元素在ARRAY数组中第一次出现的位置。
ARRAY_REDUCE 将ARRAY数组的元素进行聚合。
ARRAY_REMOVE 在ARRAY数组中删除指定元素。
ARRAY_REPEAT 返回将指定元素重复指定次数后的ARRAY数组。
ARRAY_SORT 将ARRAY数组的元素进行排序。
ARRAY_UNION 计算两个ARRAY数组的并集并去掉重复元素。
ARRAYS_OVERLAP 判断两个ARRAY数组中是否包含相同元素。
ARRAYS_ZIP 合并多个ARRAY数组。
CONCAT 将ARRAY数组或字符串连接在一起。
EXPLODE 将一行数据转为多行的UDTF。
FILTER 将ARRAY数组中的元素进行过滤。
INDEX 返回ARRAY数组指定位置的元素值。
POSEXPLODE 将指定的ARRAY展开,每个Value一行,每行两列分别对应数组从0开始的下标和数组元素。
SIZE 返回指定ARRAY中的元素数目。
SLICE 对ARRAY数据切片,返回从指定位置开始、指定长度的数组。
SORT_ARRAY 为指定的数组中的元素排序。
TRANSFORM 将ARRAY数组中的元素进行转换。
ZIP_WITH 将2个ARRAY数组按照位置进行元素级别的合并。
MAP函数 EXPLODE 将一行数据转为多行的UDTF。
INDEX 返回MAP类型参数中满足指定条件的Value。
MAP 使用指定的Key-Value对建立MAP。
MAP_CONCAT 返回多个MAP的并集。
MAP_ENTRIES 将MAP中的Key、Value键值映射转换为STRUCT结构数组。
MAP_FILTER 将MAP中的元素进行过滤。
MAP_FROM_ARRAYS 通过给定的ARRAY数组构造MAP。
MAP_FROM_ENTRIES 通过给定的结构体数组构造MAP。
MAP_KEYS 将参数MAP中的所有Key作为数组返回。
MAP_VALUES 将参数MAP中的所有Value作为数组返回。
MAP_ZIP_WITH 对输入的两个MAP进行合并得到一个新MAP。
SIZE 返回指定MAP中的K/V对数。
TRANSFORM_KEYS 对MAP进行变换,保持Value不变,根据指定函数计算新的Key。
TRANSFORM_VALUES 对MAP进行变换,保持Key不变,根据指定函数计算新的Value。
STRUCT函数 FIELD 获取STRUCT中的成员变量的取值。
INLINE 将指定的STRUCT数组展开。每个数组元素对应一行,每行每个STRUCT元素对应一列。
STRUCT 使用给定Value列表建立STRUCT。
NAMED_STRUCT 使用给定的Name、Value列表建立STRUCT。
JSON函数 FROM_JSON 根据给定的JSON字符串和输出格式信息,返回ARRAY、MAP或STRUCT类型。
GET_JSON_OBJECT 在一个标准JSON字符串中,按照指定方式抽取指定的字符串。
JSON_TUPLE 在一个标准的JSON字符串中,按照输入的一组键抽取各个键指定的字符串。
TO_JSON 将指定的复杂类型输出为JSON字符串。

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条件。命令示例如下:
      --返回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数组。

  • 参数说明

    ab:必填。ARRAY数组。array<T>中的T指代ARRAY数组元素的数据类型,数组中的元素可以为任意类型。ab的数据类型必须保持一致。

  • 返回值说明
    返回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数组ab的交集,并去掉重复元素。

  • 参数说明

    ab:必填。ARRAY数组。array<T>中的T指代ARRAY数组元素的数据类型,数组中的元素可以为任意类型。ab的数据类型必须保持一致。

  • 返回值说明
    返回ARRAY类型。返回规则如下:
    • ARRAY数组中存在元素为NULL时,NULL值会参与运算。
    • 新ARRAY数组无重复元素且元素顺序与a中的元素顺序保持一致。
    • 如果ARRAY数组ab为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_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数组aelement为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_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数组aelement为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_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_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类型。

  • 示例
    --返回[{"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数组ab的并集,并去掉重复元素。

  • 参数说明

    ab:必填。ARRAY数组。array<T>中的T指代ARRAY数组元素的数据类型,数组中的元素可以为任意类型。ab中元素的数据类型必须一致。

    数组中的元素可以为如下类型:
    • TINYINT、SMALLINT、INT、BIGINT
    • FLOAT、DOUBLE
    • BOOLEAN
    • DECIMAL、DECIMALVAL
    • DATE、DATETIME、TIMESTAMP、IntervalDayTime、IntervalYearMonth
    • STRING、BINARY、VARCHAR、CHAR
    • ARRAY、STRUCT、MAP
  • 返回值说明

    返回ARRAY类型。如果ab为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数组ab是否存在相同元素。

  • 参数说明

    ab:必填。ARRAY数组。array<T>中的T指代ARRAY数组元素的数据类型,数组中的元素可以为任意类型。ab中元素的数据类型必须一致。

    数组中的元素可以为如下类型:
    • 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数组ab中没有公共元素、都非空,且其中任意一个数组中包含NULL元素,返回结果为NULL。
    • 如果ARRAY数组ab中没有公共元素、都非空,且其中任意一个数组中都不包含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个值。

  • 参数说明

    ab:必填。ARRAY数组。array<T>array<U>中的TU指代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));

CONCAT

  • 命令格式
    array<T> concat(array<T> <a>, array<T> <b>[,...])
    string concat(string <str1>, string <str2>[,...])
  • 命令说明
    • 输入为ARRAY数组:将多个ARRAY数组中的所有元素连接在一起,生成一个新的ARRAY数组。
    • 输入为字符串:将多个字符串连接在一起,生成一个新的字符串。
  • 参数说明
    • ab:必填。ARRAY数组。array<T>中的T指代ARRAY数组元素的数据类型,数组中的元素可以为任意类型。ab中元素的数据类型必须一致。数组中的元素为NULL值时会参与运算。
    • str1str2:必填。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:连接字符串aabcabcde。命令示例如下。
      --返回aabcabcde。
      select concat('aabc','abcde');
    • 示例5:输入为空。命令示例如下:
      --返回NULL。
      select concat();
    • 示例6:任一字符串输入为NULL。命令示例如下:
      --返回NULL。
      select concat('aabc', 'abcde', null);

EXPLODE

  • 使用限制
    • 在一个select中只能出现一个explode函数,不可以出现表的其他列。
    • 不可以与group bycluster bydistribute bysort 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_array array<string>,包含数据如下:

      +------------+-------+
    | 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          |
    +-----+------------+

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);

INDEX

  • 命令格式
    index(<var1>[<var2>])
  • 命令说明
    • 如果var1array<T>类型,获取var1的第var2个元素。ARRAY数组元素编号自左往右,从0开始计数。
    • 如果var1map<K, V>类型,获取var1中Key为var2的Value。
    说明 使用该函数时需要去掉index,请直接执行<var1>[<var2>],否则会返回报错。
  • 参数说明
    • var1:必填。array<T>类型或map<K, V>类型。array<T>中的T指代ARRAY数组元素的数据类型,数组中的元素可以为任意类型。map<K, V>中的KV指代MAP对象的Key、Value。
    • var2:必填。
      • 如果var1array<T>类型,则var2为BIGINT类型且大于等于0。
      • 如果var1map<K, V>类型,则var2K的类型保持一致。
  • 返回值说明
    • 如果var1array<T>类型,函数返回T类型。返回规则如下:
      • 如果var2超出var1的元素数目范围,返回结果为NULL。
      • 如果var1为NULL,返回结果为NULL。
    • 如果var1map<K, V>类型,函数返回V类型。返回规则如下:
      • 如果map<K, V>中不存在Key为var2的情况,返回结果为NULL。
      • 如果var1为NULL,返回结果为NULL。
  • 示例
    • 示例1:var1array<T>类型。命令示例如下:
      --返回c。
      select array('a','b','c')[2];
    • 示例2:var1map<K, V>类型,命令示例如下:
      --返回1。
      select str_to_map("test1=1,test2=2")["test1"];

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          |
    +------------+------------+

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>中的KV指代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);

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);

ZIP_WITH

  • 命令格式
    array<R> array_sort(array<T> <a>, array<S> <b>, function<T, S, R> <combiner>)
  • 命令说明

    将ARRAY数组ab的元素按照位置,使用combiner进行元素级别的合并,返回一个新的ARRAY数组。

  • 参数说明
    • ab:必填。ARRAY数组。array<T>array<S>中的TS指代ARRAY数组元素的数据类型,数组中的元素可以为任意类型。
    • combiner:必填。用于合并ARRAY数组ab中元素的函数(内置函数或自定义函数)或表达式。它的两个输入参数类型分别与ARRAY数组ab中元素的数据类型一致。
  • 返回值说明
    返回ARRAY类型。返回规则如下:
    • 新生成的ARRAY数组中元素位置与ab中相应元素的位置相同。
    • 如果ARRAY数组ab的长度不一致,会将长度较短的ARRAY数组使用NULL值进行填充,然后进行合并。
  • 示例
    --返回[2, 4, 6, NULL]。
    select zip_with(array(1,2,3), array(1,2,3,4), (x,y) -> x + y);

MAP

  • 命令格式
    map(K, V) map(K <key1>, V <value1>, K <key2>, V <value2>[, ...])
  • 命令说明

    使用给定的Key-Value对生成MAP。

  • 参数说明
    • key:必填。所有key类型一致(包括隐式转换后类型一致),必须是基本类型。
    • value:必填。所有value类型一致(包括隐式转换后类型一致),可为任意类型。
  • 返回值说明

    返回MAP类型。

  • 示例
    例如表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} |
    +------+

MAP_CONCAT

  • 命令格式
    map<K, V> map_concat(map<K, V> <a>, map<K, V> <b>[,...])
  • 命令说明

    计算多个MAP对象的并集。

  • 参数说明

    ab:必填。MAP对象。多个MAP对象的参数数据类型必须一致。map<K, V>中的KV指代MAP对象的Key、Value。

  • 返回值说明

    返回MAP类型。如果某个MAP对象为NULL或某个MAP对象的Key为NULL,返回NULL。

  • 示例
    --返回{1:a, 2:b, 3:c}。
    select map_concat(map(1, 'a', 2, 'b'), map(3, 'c'));

MAP_ENTRIES

  • 命令格式
    array<struct<K, V>> map_entries(map<K, V> <a>): 
  • 命令说明

    将MAP对象a的K、Value映射转换为STRUCT结构数组。

  • 参数说明

    a:必填。MAP对象。map<K, V>中的KV指代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>中的KV指代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(array<K> <a>, array<V> <b>))
  • 命令说明

    将ARRAY数组ab组合成一个MAP对象。

  • 参数说明
    • a:必填。ARRAY数组。对应生成MAP的Key值。array<K>中的K指代ARRAY数组元素的数据类型,数组中的元素可以为任意类型。
    • b:必填。ARRAY数组。对应生成MAP的Value值。array<V>中的V指代ARRAY数组元素的数据类型,数组中的元素可以为任意类型。
  • 返回值说明
    返回MAP类型。返回规则如下:
    • 如果ab为NULL,返回结果为NULL。
    • 如果a中元素包含NULL值或两个数组长度不相等,会返回报错。
  • 示例
    --返回{1:2, 3:4}。
    select map_from_arrays(array(1.0, 3.0), array('2', '4'));

MAP_FROM_ENTRIES

  • 命令格式
    map<K, V> map_form_entries(array <struct<K, V>, <struct<K, V>>[,...]))
  • 命令说明

    将多个结构数组组合成一个MAP对象。

  • 参数说明

    输入为STRUCT类型的数据。其中:K对应生成MAP的Key值,V对应生成MAP的Value值。struct<K, V>中的KV指代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')));

MAP_KEYS

  • 命令格式
    array<K> map_keys(map<K, V> <a>)
  • 命令说明

    将MAP对象a中的所有Key生成ARRAY数组。

  • 参数说明

    a:必填。MAP对象。map<K, V>中的KV指代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>中的KV指代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_wuth(map<K, V1> <input1>, map<K, V2> <input2>, function<K, V1, V2, V3> <func>)
  • 命令说明

    对输入的两个MAP对象input1input2进行合并得到一个新MAP对象。新MAP的Key是两个MAP的Key的并集。针对新MAP的每一个Key,通过func来计算它的Value。

  • 参数说明
    • input1input2:必填。MAP对象。map<K, V>中的KV指代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));

TRANSFORM_KEYS

  • 命令格式
    map<K2, V> transform_keys([string <mapDupKeyPolicy>,] map<K1, V> <input>, function<K1, V, K2> <func>)
  • 命令说明

    对MAP对象input进行变换,保持Value不变,通过func计算新的Key值。

  • 参数说明
    • mapDupKeyPolicy:可选。用于设置出现重复的Key时如何处理。取值如下:
      • exception:出现重复的Key时报错。
      • last_win:后面计算的Key值将覆盖前面计算的Key值。

      不指定该参数时,您可以通过在Session级别设置odps.sql.map.key.dedup.policy属性来设置出现重复Key时的行为。默认取值为last_win。例如set odps.sql.map.key.dedup.policy=exception;

    • input:必填。MAP对象。map<K1, V>中的K1V指代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<K2, V> transform_values(map<K, V1> <input>, function<K, V1, V2> <func>)
  • 命令说明

    对输入MAP对象input进行变换,保持Key不变,通过func计算新的Value值。

  • 参数说明
    • input:必填。MAP对象。map<K1, V>中的KV1指代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);

FIELD

  • 命令格式
    T field(struct <s>, string <fieldName>)
  • 命令说明

    获取STRUCT对象中成员变量的取值。

  • 参数说明
    • s:必填。STRUCT类型对象。STRUCT的结构为{f1:T1, f2:T2[, ...]}f1f2代表成员变量,T1T2分别代表成员变量f1f2的取值。
    • fieldName:必填。STRING类型。STRUCT类型对象的成员变量。
  • 返回值说明

    返回STRUCT类型对象的成员变量的取值。

  • 示例
    --返回hello。
    select field(named_struct('f1', 'hello', 'f2', 3), 'f1');

INLINE

  • 命令格式
    inline(array<struct<f1:T1, f2:T2[, ...]>>)
  • 命令说明

    将给定的STRUCT数组展开。每个数组元素对应一行,每行每个STRUCT元素对应一列。

  • 参数说明

    f1:T1f2:T2:必填。可以为任意类型。f1f2代表成员变量,T1T2分别代表成员变量f1f2的取值。

  • 返回值说明

    返回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       |
    +------------+-----------+---------+------------+

STRUCT

  • 命令格式
    struct struct(<value1>,<value2>[, ...])
  • 命令说明

    使用指定value列表建立STRUCT。

  • 参数说明

    value:必填。可以为任意类型。

  • 返回值说明

    返回STRUCT类型。Field的名称依次为col1,col2,…

  • 示例
    --返回{col1:a, col2:123, col3:ture, col4:56.9}。
    select struct('a',123,'ture',56.90);

NAMED_STRUCT

  • 命令格式
    struct named_struct(string <name1>, T1 <value1>, string <name2>, T2 <value2>[, ...])
  • 命令说明

    使用指定的namevalue列表建立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);

FROM_JSON

  • 命令格式
    from_json(<jsonStr>, <schema>)
  • 命令说明

    根据JSON字符串jsonStrschema信息,返回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类型。
  • 返回值说明

    返回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

  • 命令格式
    string get_json_object(string <json>, string <path>)
  • 命令说明

    在一个标准JSON字符串中,按照path抽取指定的字符串。每次调用该函数时,都会读一次原始数据,因此反复调用可能影响性能和产生费用。您可以通过get_json_object,结合UDTF,轻松转换JSON格式日志数据,避免多次调用函数,详情请参见利用MaxCompute内建函数及UDTF转换JSON格式日志数据

  • 参数说明
    • json:必填。STRING类型。标准的JSON格式对象。
    • path:必填。STRING类型。表示在json中的path,以$开头。更多path信息,请参见LanguageManual UDF。相关最佳实践案例,请参见JSON数据从OSS迁移至MaxCompute。不同字符的含义如下:
      • $:表示根节点。
      • .:表示子节点。
      • [][number]表示数组下标,从0开始。
      • *Wildcard for [],返回整个数组。*不支持转义。
  • 返回值说明
    • 如果json为空或非法的json格式,返回NULL。
    • 如果json合法,path也存在,则返回对应字符串。
    • 您可以通过在Session级别设置flagodps.sql.udf.getjsonobj.new属性来控制函数的返回方式:
      • 当设置set odps.sql.udf.getjsonobj.new=false;时,函数返回行为采用了JSON保留字符转义的方式进行输出。函数返回行为规则如下:
        • 换行符(\n)、引号(")等JSON保留字符使用字符串'\n''\"'显示。
        • 一个Object中不可以出现相同的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"}', '$');
      • 当设置set odps.sql.udf.getjsonobj.new=true;时,函数返回行为采用了保留原始字符串的方式进行输出。
        推荐您使用此配置,函数返回行为更标准,处理数据更方便,性能更好。如果MaxCompute项目有使用JSON保留字符转义行为的存量作业,建议保留原有行为方式,避免因未验证而直接使用该行为产生错误或正确性问题。函数返回行为规则如下:
        • 返回值仍是一个JSON字符串,可以继续当做JSON来解析,而不再需要额外使用replaceregexp_replace等函数替换反斜线。
        • 一个Object中可以出现相同的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"}', '$');
      说明 自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"]

      您可以提工单联系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"]。
      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输入为空或非法格式。命令示例如下:
      --返回NULL。
      select get_json_object('','$.array[1][1]');
      --返回NULL。
      select get_json_object('"array":["aaaa",1111],"bbbb":["cccc",3333]','$.array[1][1]');

JSON_TUPLE

  • 命令格式
    string json_tuple(string <json>, string <key1>, string <key2>,...)
  • 命令说明

    用于一个标准的JSON字符串中,按照输入的一组键(key1,key2,...)抽取各个键指定的字符串。

  • 参数说明
    • json:必填。STRING类型,标准的JSON格式字符串。
    • key:必填。STRING类型,用于描述在JSON中的path,一次可输入多个,不能以美元符号($)开头。
  • 返回值

    返回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的值如下。
    Table:school
    +------------+------------+
    | Id         | json       |
    +------------+------------+
    | 1          | {
                    "校名": "湖畔大学",
                    "地址":"杭州",
                    "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,"校名","地址") as (item0,item1) from school;
      --返回结果如下。
      +-------+-------+
      | item0 | item1 |
      +-------+-------+
      | 湖畔大学 | 杭州 |
      +-------+-------+
    • 示例3:支持解析多层嵌套的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]] |
      +------------+-------+-------+
    • 示例4:支持解析包含多重嵌套数组的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     |
      +------------+-------+-------+

TO_JSON

  • 命令格式
    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字符串。
  • 示例
    • 示例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"));