POSEXPLODE

Description

The posexplode function is used to expand an input array or map type expression expr into multiple rows of data, adding a position column to each row to indicate the relative position of the element in the original array or map. This function can be used directly or in conjunction with LATERAL VIEW to achieve data expansion in more complex queries.

Functionality

1. For array type input, posexplode expands each element in the array into a row, adding columns named pos and col to represent the position and value of the element, respectively.
2. For map type input, posexplode expands each key-value pair in the map into a row, adding columns named pos, key, and value to represent the position, key, and value of the element, respectively.

Parameters

• expr: Input array array<T> or map map<K, V> type.

Return Results

• For array type input, the return type is int, T.
• For map type input, the return type is int, K, V.

Usage Example

1. Expand array type input:

   SELECT posexplode(array(1, 2, 3));
   +-----+-----+
   | pos | col |
   +-----+-----+
   | 0   | 1   |
   | 1   | 2   |
   | 2   | 3   |
   +-----+-----+
   

2. Expand the input of the mapping type:

   SELECT posexplode(map("a", 1, "b", 2, "c", 3));
   +-----+-----+-------+
   | pos | key | value |
   +-----+-----+-------+
   | 0   | a   | 1     |
   | 1   | b   | 2     |
   | 2   | c   | 3     |
   +-----+-----+-------+
   

3. Using in combination with LATERAL VIEW:

   SELECT word, pos, ex
   FROM VALUES ('hello') AS vt(word)
   LATERAL VIEW posexplode(array(5, 6)) lv AS pos, ex;
   +-------+-----+----+
   | word  | pos | ex |
   +-------+-----+----+
   | hello | 0   | 5  |
   | hello | 1   | 6  |
   +-------+-----+----+
   

4. Expand the array and filter specific elements:

   SELECT pos, col
   FROM  posexplode(array(1, 2, 3, 4)) AS t(pos, col) 
   WHERE col % 2 = 0;
   +-----+-----+
   | pos | col |
   +-----+-----+
   | 1   | 2   |
   | 3   | 4   |
   +-----+-----+
   

5. Expand the mapping and calculate the sum of values:

   SELECT SUM(value) AS total_sum
   FROM posexplode(map("a", 1, "b", 2, "c", 3)) AS t(pos, key, value);
   +-----------+
   | total_sum |
   +-----------+
   | 6         |
   +-----------+
   

By the above example, you can better understand the usage and functionality of the posexplode function. In practical applications, you can expand and process array or map type data as needed.