MaxCompute:EXPLODE

Usage notes

• If the parameter value is of the array<T> type, the array stored in the column is transposed into multiple rows.

• If the parameter value is of the map<K, V> type, each key-value pair of the map stored in the column is transposed into one row with two columns. One column is used to store keys, and the other column is used to store values.

• If a row contains only the null data, this function does not return a row that contains only the null data.

Limits

• A SELECT statement can contain only one EXPLODE function, and no other columns of a table are allowed.

• This function cannot be used with the GROUP BY, CLUSTER BY, DISTRIBUTE BY, or SORT BY clause.

Syntax

explode (<var>)

Parameters

var: required. The value must be of the array<T> or map<K, V> type.

Return value

Rows after transposition are returned.

Examples

• Example 1: The t_table_map table contains the c1 (BIGINT) and t_map (MAP<STRING,BIGINT>) columns. Data in the table:

    +------------+-------+
  | c1         | t_map |
  +------------+-------+
  | 1000       | {k11:86, k21:15} |
  | 1001       | {k12:97, k22:2} |
  | 1002       | {k13:99, k23:1} |
  +------------+-------+

  Sample statement:

  select explode(t_map) from t_table_map;
  -- The following result is returned: 
  +-----+------------+
  | key | value      |
  +-----+------------+
  | k11 | 86         |
  | k21 | 15         |
  | k12 | 97         |
  | k22 | 2          |
  | k13 | 99         |
  | k23 | 1          |
  +-----+------------+

• Example 2: The returned result does not include a row that contains only the null data.

  -- Create a table.
  create table explod_array (arr array<string>);
  -- Insert data into the table.
  insert into table explod_array values (array('1','2','3',null,'agb')),
                                        (array('1','2','3',null,'ag')),
                                        (null);
  -- Query data from the table.
  select * from explod_array;
  -- The following result is returned:
  +------------+
  | arr        |
  +------------+
  | ["1","2","3",null,"agb"] |
  | ["1","2","3",null,"ag"] |
  | NULL       |
  +------------+
  -- Display data.
  select explode(arr) from explod_array;
  -- The returned result does not include a row that contains only the null data.
  +------------+
  | col        |
  +------------+
  | 1          |
  | 2          |
  | 3          |
  | NULL       |
  | agb        |
  | 1          |
  | 2          |
  | 3          |
  | NULL       |
  | ag         |
  +------------+
                      

Related functions

EXPLODE is a complex type function. For more information about the functions that are used to process data of complex data types, such as ARRAY, MAP, STRUCT, and JSON, see Complex type functions.