---

{ “title”: “POSEXPLODE_OUTER”, “language”: “en”, “description”: “The posexplodeouter table function expands the column into multiple rows and adds a column indicating the position, returning a STRUCT type.” }

Description

The posexplode_outer table function expands the <array> column into multiple rows and adds a column indicating the position, returning a STRUCT type. It should be used together with Lateral View and supports multiple Lateral Views. The main difference between posexplode_outer and posexplode is how they handle null values.

Syntax

POSEXPLODE_OUTER(<array>)

Parameters

• <array> Array type, NULL is not supported.

Return Value

• Returns a single-column, multi-row STRUCT data. STRUCT consists of two columns:
  1. A column of integers starting from 0, incrementing by 1, until n – 1, where n represents the number of result rows.
  2. A column containing all elements of <array>.
• If <array> is NULL or an empty array (number of elements is 0), 1 row with NULL are returned.

Usage Notes

1. <array> cannot be NULL or other types, otherwise an error will be reported.

Examples

0. Prepare data

   create table example(
       k1 int
   ) properties(
       "replication_num" = "1"
   );
   
   insert into example values(1);
   

1. Regular parameters

   select  * from (select 1 as k1) t1 lateral view posexplode_outer([1, 2, null, 4, 5]) t2 as c;
   

   +------+-----------------------+
   | k1   | c                     |
   +------+-----------------------+
   |    1 | {"pos":0, "col":1}    |
   |    1 | {"pos":1, "col":2}    |
   |    1 | {"pos":2, "col":null} |
   |    1 | {"pos":3, "col":4}    |
   |    1 | {"pos":4, "col":5}    |
   +------+-----------------------+
   

   select  * from (select 1 as k1) t1 lateral view posexplode_outer([1, 2, null, 4, 5]) t2 as pos, value;
   

   +------+------+-------+
   | k1   | pos  | value |
   +------+------+-------+
   |    1 |    0 |     1 |
   |    1 |    1 |     2 |
   |    1 |    2 |  NULL |
   |    1 |    3 |     4 |
   |    1 |    4 |     5 |
   +------+------+-------+
   

2. Empty array

   select  * from (select 1 as k1) t1 lateral view posexplode_outer([]) t2 as c;
   

   +------+------+
   | k1   | c    |
   +------+------+
   |    1 | NULL |
   +------+------+
   

3. NULL parameter

   select  * from (select 1 as k1) t1 lateral view posexplode_outer(NULL) t2 as c;
   

   ERROR 1105 (HY000): errCode = 2, detailMessage = only support array type for posexplode_outer function but got NULL
   

4. Non-array parameter

   select  * from (select 1 as k1) t1 lateral view posexplode_outer('abc') t2 as c;
   

   ERROR 1105 (HY000): errCode = 2, detailMessage = only support array type for posexplode_outer function but got VARCHAR(3)