# **UNNEST()**

## **Function Description**

The `UNNEST` function is used to expand a column or parameter of an array type within JSON type data into a table. It splits elements in arrays in JSON type data into separate rows, allowing the array elements to be processed separately or joined with other tables.

When an empty array is entered, the `UNNEST` function returns an empty table because there are no elements to expand.

When a `NULL` value is entered, the `UNNEST` function returns an empty table because `NULL` is not a valid array.

## **Function Syntax**

```text
> UNNEST(array_expression)
```

## **Parameter definition**

| Parameters | Description |
| ---- | ---- |
| array_expression | Required parameters. is an array within JSON data, which can be an array column, array constant, or return value of an array function. |

## **Example**

- Example 1:

```text
-- Expand an array of strings containing JSON objects, '{"a":1}' is a string array containing a single element. This element is a string representing a JSON object.
> select * from unnest('{"a":1}') u;
+-------------------+------+--------+----------+-----------+------------+
| col | seq | key | path | index | value | this |
+-------------------+------+--------+----------+-----------+------------+
| UNNEST_DEFAULT | 0 | a | $.a | NULL | 1 | {"a": 1} |
+-------------------+------+--------+----------+-----------+------------+
1 row in set (0.00 sec)

-- Expand an array of strings containing integers '[1,2,3]' and use the alias u to represent the expanded column.
> select * from unnest('[1,2,3]') u;
+-------------------+------+--------+------------+---------------------+
| col | seq | key | path | index | value | this |
+-------------------+------+--------+------------+---------------------+
| UNNEST_DEFAULT | 0 | NULL | $[0] | 0 | 1 | [1, 2, 3] |
| UNNEST_DEFAULT | 0 | NULL | $[1] | 1 | 2 | [1, 2, 3] |
| UNNEST_DEFAULT | 0 | NULL | $[2] | 2 | 3 | [1, 2, 3] |
+-------------------+------+--------+------------+---------------------+
3 rows in set (0.00 sec)

-- Expand an array of strings containing integers '[1,2,3]' and select the first element of the array to return as part of the result set. '$[0]' is a path expression that specifies the array element to be selected, true is a boolean indicating whether the path is returned, and uses the alias u to represent the expanded column.
> select * from unnest('[1,2,3]','$[0]',true) u;
+-------------------+------+-------+-------+-------+------+
| col | seq | key | path | index | value | this |
+-------------------+------+-------+-------+-------+------+
| UNNEST_DEFAULT | 0 | NULL | $[0] | NULL | NULL | 1 |
+-------------------+------+-------+-------+-------+------+
1 row in set (0.00 sec)
```

- Example 2:

```text
create table t1 (a json,b int);
insert into t1 values ('{"a":1,"b":[{"c":2,"d":3},false,4],"e":{"f":true,"g":[null,true,1.1]}}',1);
insert into t1 values ('[1,true,false,null,"aaa",1.1,{"t":false}]',2);
> select * from t1;
+----------------------------------------------------------------------------------------------------------------------------------+
| a | b |
+----------------------------------------------------------------------------------------------------------------------------------+
| {"a": 1, "b": [{"c": 2, "d": 3}, false, 4], "e": {"f": true, "g": [null, true, 1.1]}} | 1 |
| [1, true, false, null, "aaa", 1.1, {"t": false}] | 2 |
+----------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

-- Expand the element of array t1.a from table t1 and select the expanded element to return as part of the result set. "$a" is a path expression that specifies the array element to be selected; true is a boolean indicating whether the path is returned; f.* is used to select all columns after expansion; f is an alias for the UNNEST function, representing the expanded result
mysql> select f.* from t1,unnest(t1.a, "$.a", true) as f;
+------+-----+-----+-----+------+-----+-----+
| col | seq | key | path | index | value | this |
+------+-----+-----+-----+------+-----+-----+
| t1.a | 0 | NULL | $.a | NULL | NULL | 1 |
| t1.a | 0 | NULL | $.a | NULL | NULL | 1 |
+------+-----+-----+-----+------+-----+-----+
2 rows in set (0.00 sec)
```