---

{ “title”: “EXPLODE_JSON_ARRAY_JSON_OUTER”, “language”: “en” }

Description

The explode_json_array_json_outer table function accepts a JSON array. Its implementation logic is to convert the JSON array to an array type and then call the explode_outer function for processing. The behavior is equivalent to: explode_outer(cast(<json_array> as Array<JSON>)). It should be used together with LATERAL VIEW.

Syntax

EXPLODE_JSON_ARRAY_JSON_OUTER(<json>)

Parameters

• <json> JSON type, the content should be an array.

Return Value

• Returns a single-column, multi-row result composed of all elements in <json>. The column type is Nullable<JSON>.
• If <json> is NULL or an empty array (number of elements is 0), 1 row with NULL is returned.

Examples

0. Prepare data

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

1. Regular parameters

   select * from example lateral view explode_json_array_json_outer('[4, "abc", {"key": "value"}, 5.23, null]') t2 as c;
   

   +------+-----------------+
   | k1   | c               |
   +------+-----------------+
   |    1 | 4               |
   |    1 | "abc"           |
   |    1 | {"key":"value"} |
   |    1 | 5.23            |
   |    1 | NULL            |
   +------+-----------------+
   

2. Empty array

   select * from example lateral view explode_json_array_json_outer('[]') t2 as c;
   

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

3. NULL parameter

   select * from example lateral view explode_json_array_json_outer(NULL) t2 as c;
   

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

4. Non-array parameter

   select * from example lateral view explode_json_array_json_outer('{}') t2 as c;
   

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