Expands each element of a JSON array or JSON object into a separate row, converting semi-structured JSON data into a relational format for SQL queries.
Syntax
JSON_EXPLODE(JSON <var>)Parameters
| Parameter | Required | Type | Description |
|---|---|---|---|
var | Yes | JSON_ARRAY or JSON_OBJECT | The JSON value to expand. JSON_STRING, JSON_NUMBER, JSON_BOOLEAN, JSON constants, and NULL are not supported. |
Return value
Returns a two-column table. Each row represents one element from the input JSON value.
| Column | Type | Description |
|---|---|---|
key | STRING | For a JSON object: the key of the key-value pair. For a JSON array: NULL. |
value | JSON | For a JSON object: the value of the key-value pair. For a JSON array: the array element. |
Behavior by input type:
| Input type | key column | value column |
|---|---|---|
| JSON array | NULL (\N) for every row | Each top-level array element, in order |
| JSON object | String key of each key-value pair | Corresponding JSON value |
Usage notes
Elements within the same JSON value are returned in their original order. The order of rows across different JSON values in the same query is not guaranteed.
JSON_EXPLODEdoes not support JSON_STRING, JSON_NUMBER, JSON_BOOLEAN, JSON constants, or NULL as input.
Example
Create a table with one JSON object row and one JSON array row:
-- Create table
CREATE TABLE table_json(c1 json);
-- Insert a JSON object
INSERT INTO table_json(c1) SELECT JSON_OBJECT('a', 123, 'b', 'hello');
-- Insert a JSON array
INSERT INTO table_json(c1) SELECT JSON_ARRAY(1, true, 2, json'{"a":456}');The table contains:
+-------------------------------+
| c1 |
+-------------------------------+
| {"a":123,"b":"hello"} |
| [1,true,2,{"a":456}] |
+-------------------------------+Run JSON_EXPLODE against the table:
SELECT JSON_EXPLODE(table_json.c1) FROM table_json;Result:
+-----+------------+
| key | value |
+-----+------------+
| \N | 1 |
| \N | true |
| \N | 2 |
| \N | {"a":456} |
| a | 123 |
| b | hello |
+-----+------------+The first four rows come from the JSON array: key is \N (NULL) because array elements have no named key. The last two rows come from the JSON object: key holds the string keys a and b, and value holds the corresponding values.