MaxCompute:JSON_EXPLODE

Command format

JSON_EXPLODE(JSON <var>)

Parameter description

var: Required. Supports JSON_ARRAY or JSON_OBJECT types. Does not support JSON constants, JSON STRING, JSON NUMBER, JSON BOOLEAN, and NULL types.

Return value description

The function returns transformed rows in the following format:

+-------+-------+
|  KEY  | VALUE |
|-------+-------|

• For a JSON ARRAY type var, the outermost of the JSON ARRAY is expanded into multiple rows of JSON data, with KEY as NULL and VALUE as the array element.

• For a JSON OBJECT type var, each KEY-VALUE pair of the outermost of the JSON OBJECT is expanded into rows with two columns: A STRING type KEY column is the KEY of the JSON OBJECT, and a JSON type VALUE column is the VALUE of the JSON OBJECT.

Example

Create a table named table_json and insert data into it, where the first row is a JSON OBJECT type and the second row is a JSON ARRAY type.

-- Create table
CREATE TABLE table_json(c1 json);
-- Insert data
INSERT INTO table_json(c1) SELECT JSON_OBJECT('a', 123,'b','hello');
INSERT INTO table_json(c1) SELECT JSON_ARRAY(1, true, 2, json'{"a":456}');
-- View table data
SELECT * FROM table_json;

The query returns the following result:

+-----------------------------+
| c1                            |
+-------------------------------+
| {"a":123,"b":"hello"}         |
| [1,true,2,{"a":456}]          |
+-------------------------------+

The JSON_EXPLODE function expands each element in a JSON array or JSON object into multiple output rows. Below is a sample code:

SELECT JSON_EXPLODE(table_json.c1) FROM table_json;

The query returns the following result:

+-----+------------+
| key | value      |
+-----+------------+
| \N  | 1          |
| \N  | true       |
| \N  | 2          |
| \N  | {"a":456}  |
| a   | 123        |
| b   | hello      |
+-----+------------+