MaxCompute:EXPLODE

Usage notes

• If the parameter value is of the ARRAY<T> type, each element in the array is returned as a separate row. The output column is named col.

• If the parameter value is of the MAP<K, V> type, each key-value pair in the map is returned as a separate row with two columns: key and value.

• If the input value is NULL, no rows are returned.

Limits

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

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

Syntax

explode(<expr>)

Parameters

Return value

Rows generated from the input array or map 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: Sample statement:

      +------------+-------------------+
      | 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;
      -- 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 where the entire array is NULL. Individual NULL elements within non-null arrays are still included in the output.

      -- 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 the row where the entire array is NULL.
      +------------+
      | 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.